15 replies [Last post]
minneapolisdan's picture
Offline
Joined: 05/21/2009
Juice: 67
Was this information Helpful?

I am trying to use Views to create a page showing products on sale, and need some advice. I thought this would be simple, maybe not.

- Can I create a filter, using Views, that says, show products where the LIST PRICE is greater than the SELL PRICE? I tried, but maybe I'm not entering the right value in the field. Can Views compare the value of 2 fields?

I'll attach a screenshot from my Views.

PreviewAttachmentSize
filters setupfilters setup10.59 KB
minneapolisdan's picture
Offline
Joined: 05/21/2009
Juice: 67
Re: Create a View for products on sale?

I can get this working by using Taxonomy, creating a custom set of tags including one for "Sale". I tag a product as "sale" and then create a page in Views, using the filters of "products" and taxonomy tag of "sale".

So that works. It's just an extra step to explain to the client, if you want to put something on sale, you have to:

1. Enter a sale price lower than the list price
2. Tag it as "sale"

Has anyone else created a page of products on sale? Anyone?

RSTaylor's picture
Offline
Joined: 04/02/2008
Juice: 99
Re: Create a View for products on sale?

Another option is to use nodequeue to create a queue of 'featured items' or 'items on sale'. Once you've done that, making a view from the queue is simple.

fehin's picture
Offline
Joined: 12/17/2008
Juice: 154
Re: Create a View for products on sale?

Did you ever figure this out. I was able to get the items on sale by making List price greater then zero. It's not perfect but it got me all the items on sale and those with a list price that is the same as sell price. My list price field will generally be used ony if the item is on sale so I hope that will prevent getting items not on sale in the future.

minneapolisdan's picture
Offline
Joined: 05/21/2009
Juice: 67
Re: Re: Create a View for products on sale?

I got a sale page working by relying on Taxonomy. It works, as I explained above, but I wanted a way to create a "SALE" page that automatically included items where the List price was higher than the Sell price. In other words, automate it and not require any user 'tagging' to include it on the "sale' page. I do understand using List and Sell price as a way to indicate something is on sale, I just wanted a more automated process for showing all products on sale.

fehin's picture
Offline
Joined: 12/17/2008
Juice: 154
Re: Re: Re: Create a View for products on sale?

I installed this module http://drupal.org/project/viewsphpfilter but unfortunately I couldn't figure out how to write the php script to make it work. If you ever figure it out, please let me know.

Fonant's picture
Offline
Joined: 10/27/2011
Juice: 18
View filter PHP to show only items where Sell Price < List Price

Here's the snippet I used with Views PHP Filter, which simply compares the "Sell price" with the "List price", allowing for rounding errors in VAT calculations:

<?php
$node_ids
= array();

$result = db_query("SELECT nid FROM {uc_products} WHERE sell_price < list_price - 0.005");

while (

$row = db_fetch_array($result)) {
 
$node_ids[] = $row['nid'];
}

return

$node_ids;
?>
minneapolisdan's picture
Offline
Joined: 05/21/2009
Juice: 67
Re: View filter PHP to show only items where Sell Price < List P

Thanks, that seems to be a smart solution.

Fonant's picture
Offline
Joined: 10/27/2011
Juice: 18
Re: View filter PHP to show only items where Sell Price < List P

And here's a better version that only looks at the current product node revisions, and allows node access rules to work:

<?php
$node_ids
= array();

// Find current node revisions that are on sale.
$sql = "SELECT n.nid FROM {node} n
JOIN {uc_products} p ON n.vid = p.vid
WHERE p.sell_price < p.list_price - 0.005"
;

// Rewrite query to apply node access rules
$sql = db_rewrite_sql($sql);

// Run query.
$result = db_query($sql);

// Get list of NIDs.
while ($row = db_fetch_array($result)) {
 
$node_ids[] = $row['nid'];
}

return

$node_ids;
?>
justclint's picture
Offline
Joined: 12/20/2009
Juice: 71
Created CCK Field...

To do this I just created a cck field for the product content type. I called the field field_product_onsale and made it a required radio button with a Yes or No value and a No default value.

Then just went into views and in the filters you can select content -> field_product_onsale and is one of YES and then just go through your products and set YES to those on sale.

I did this instead of calculating if list price is greater than sell price since in many instances we use list as an MSRP and will almost always be greater than our sale price.

I also prefer this method over the taxonomy due to the ctl+select for selecting multiple terms. Using the radio buttons makes it easier for the content editors to just click on a button.

Hope that helps!

justclint's picture
Offline
Joined: 12/20/2009
Juice: 71
Created CCK Field...

To do this I just created a cck field for the product content type. I called the field field_product_onsale and made it a required radio button with a Yes or No value and a No default value.

Then just went into views and in the filters you can select content -> field_product_onsale and is one of YES and then just go through your products and set YES to those on sale.

I did this instead of calculating if list price is greater than sell price since in many instances we use list as an MSRP and will almost always be greater than our sale price.

I also prefer this method over the taxonomy due to the ctl+select for selecting multiple terms. Using the radio buttons makes it easier for the content editors to just click on a button.

Hope that helps!

minneapolisdan's picture
Offline
Joined: 05/21/2009
Juice: 67
Re: Create a View for products on sale?

Thanks, that is another method to consider. My goal is still to have a Sale page that is automatically generated, based on price, rather than taxonomy or custom CCK fields. But that doesn't seem possible yet. Then again, a real 'sale price' tool would probably solve it better than anything.

GiTo's picture
Offline
Joined: 08/29/2009
Juice: 6
A possibilty to get a

A possibilty to get a automatically updated view that shows only those items that have a lower sell price than list price is with compted fields:

1. Install and activate the computed fields mobule http://drupal.org/project/computed_field
2. Create a new field for node type 'product' as a computed field, for computed code use:

if ($node-&gt;list_price &gt; $node-&gt;sell_price) {$node_field[0]['value'] = 1" ;}
else {$node_field[0]['value'] = 0;}

Leave the 'Display Format' as it is.
Data type should work with 'int' and for data length use '8'.
IMPORTANT: 'Store using the database settings below' must be activated, otherwise this field is not available for views!
3. Create a filter in the view that only shows nodes with the value '1'.
4. To get the 'computed field' value computed, you may need to 'resave' each product node which is affected.

This should work, I didn't use it in that specific way but in a similar variation.
The above code sets the 'computed field' value to '1' if the list price is greater than the sell price and to '0' if same or lower. In that way you get a field value you can use for filtering the node within the view.

fehin's picture
Offline
Joined: 12/17/2008
Juice: 154
Thanks GiTo.The instructions

Thanks GiTo.
The instructions help.

Here is my working code.

<?php
if ($node->list_price > $node->sell_price && $node->list_price > '0.00')
{
$node_field[0]['value']=1;}
else{
$node_field[0]['value']=0;}
?>

To display all products but hide the list-price if it's zero or less than sell-price:

I created a cck field called sale_list_price. This field will check if list-price is greater than sell-price and if it's greater than $0.00. If yes, it returns the list-price field, otherwise it doesn't return anything. Then I used this field instead of the list-price field for my product catalog view.

Computed Code:

<?php
if ($node->list_price > $node->sell_price && $node->list_price > '0.00'){
$node_field[0]['value']= $node->list_price;
}
else{
$node_field[0]['value']='';
}
?>

Display Format:

<?php
if ($node_field_item['value']) {
$display = t('$') . $node_field_item['value'];
}
?>

css for views field:

.views-field-field-sale-list-price-value{
text-decoration: line-through;
}
minneapolisdan's picture
Offline
Joined: 05/21/2009
Juice: 67
Re: Thanks GiTo.The instructions

Thanks for the help, this new solution makes sense. I'll give it a try.

fehin's picture
Offline
Joined: 12/17/2008
Juice: 154
Re: Create a View for products on sale?

I could not get my computed field to return 2 decimal places so the price didn't look right. I found this patch (http://drupal.org/node/581516) for computed field and it solved the problem. Hope this helps someone else.