CODEWITHSUNDEEP
netsuite
John
John

Reputation: 67

Netsuite Saved Search - Highlight row based on two column comparison in result

We have a saved search in Netsuite that looks at all open quotes with a greater than 70% chance of converting in the next 30 days. In the results, we're looking at the items on these quotes and how many of these said items we have on hand to help us forecast inventory needs. The search is working fine, but we would like to highlight the rows in the result where quantity quoted is greater than on hand. I've been able to highlight result rows based on the value of one column, but cannot figure out how to compare two columns. Any help would be greatly appreciated.

Upvotes: 1

Views: 3123

Answers (1)

Mike Robbins
Mike Robbins

Reputation: 3287

Without seeing your saved search or knowing what your environment looks like (multiple locations?), this is the pattern for a formula that I use when I need to filter records in a saved search by comparing two fields.

On the criteria tab, add the following field

Filter: Formula (Numeric)

Value: equal to 1

Formula:

case 
    when {quantity} > {item.quantityonhand} then 1 
    else 0 
end

EDIT: If you're aggregating your results, then the formula should go on the Summary sub tab of the Criteria tab. Summary type should be 'Sum'. Field should be 'Formula (Numeric)'. Value should be 'Equal to 1' and the formula in your case would be:

case 
    when sum({quantity}) > max({item.quantityonhand}) then 1 
    else 0 
end

Upvotes: 2

Related Questions