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.

Rookie Asked 6 days ago in Items.
Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor   Become a Sponsor