RE: Need an Item Search where Pricing level ID = X is NULL

Trying to to return a list of items where Shopify Product (custom field) = T and Shopify Price (Price Level) is empty. Was attempting to combine formulas from:

https://netsuite.custhelp.com/app/answers/detail/a_id/75847/kw/show%20price%20level%20when%20null

and

https://netsuite.custhelp.com/app/answers/detail/a_id/76859/kw/show%20price%20level%20when%20null/related/1

I was able to build a search with all my items returning with either their Shopify Price or NULL via Item search with:

Criteria > Shopify Product = T

Results > Formula (Currency) > Summary = Maximum | Formula A = CASE WHEN {pricing.pricelevel.id}=115 THEN NVL({pricing.unitprice}, 0) END

Other info fields like SKU Summary = Group

The only items that are null in this results column are Item Groups (N/A to price level) and items where the Price is Empty. So I should be able to turn this into criteria, no?

Tried adding to it:

Criteria > Formula (Numeric) = IS EMPTY | Formula B = CASE WHEN {pricing.pricelevel.id}=115 THEN NVL({pricing.unitprice}, 0) END

This returns a bunch of entries for items where the Formula (A) field Max of SFY Price in the results is empty, but I know they have a Shopify Price level value set. On the prior version of the search above, these items all returned 1 result line, with a value (not null), so I’m not sure why they’re returning as empty when I move the formula to the criteria.

I also tried

Criteria > Formula (Numeric) = IS EMPTY | Formula C = CASE WHEN {pricing.pricelevel.id}=115 THEN {pricing.unitprice} ELSE NULL END

Criteria > Formula (Numeric) = 0 |Formula D = CASE WHEN {pricing.pricelevel.id}=115 THEN 1 ELSE 0 END

With the same results.

There seems to be some interaction in the criteria formulas for items that have another Price Level set, and not just ID = 115, and returning those as ‘True’ for the criteria formulas, even though {pricing.pricelevel.id}=115 is not empty for the item; which isn’t happening when using a similar formula in the results. If someone has a suggestion to fix, or even a simpler way, I’d really appreciate it.

roboriccio Rookie Asked on August 2, 2022 in Items.
Add Comment
1 Answers

UPDATE: Figured this out. Since the pricelevel.id treats records where the chosen price level is empty as FALSE (i.e. not valid result for the price level), the solution is to include a double-negative instead of a positive ID.

 

–The positive filter of ID is the chosen level doesn’t work because it evaluates any items where the chosen price level is Null as FALSE (e.g. doesn’t have that level) rather than limiting results to the contents of the chosen price level, whatever they may be. So the NULL results we’re seeking are canceled out.

 

Criteria > 

Shopify Product = T
 AND

 

Criteria > Formula (Numeric) = 1 | 

CASE WHEN {pricing.pricelevel.id}=115 THEN 1 ELSE 0 END AND

 

Criteria > Formula (Numeric) IS EMPTY | 

CASE WHEN {pricing.pricelevel.id}=115 THEN NVL({pricing.unitprice}, 0) END

 

 

=> No Results

 

Instead, we can:

 

limit to only Shopify Items: Criteria > 

Shopify Product = T

 

AND

 

Return results where the Shopify Price is Null: Criteria > Formula (Numeric) = IS EMPTY | Formula B = 

CASE WHEN {pricing.pricelevel.id}=115 THEN NVL({pricing.unitprice}, 0) END

 

 

AND

 

Return only results that are not from some other price level: Criteria > Formula E (Numeric) = 0 | 

CASE WHEN NOT({pricing.pricelevel.id}=115) THEN 1 ELSE 0 END

Rookie Answered on August 10, 2022.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.