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:


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 {}=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 {}=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 {}=115 THEN {pricing.unitprice} ELSE NULL END

Criteria > Formula (Numeric) = 0 |Formula D = CASE WHEN {}=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 {}=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 on August 2, 2022 in Items.
Add Comment
1 Answer(s)

UPDATE: Figured this out. Since the 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


Criteria > Formula (Numeric) = 1 | 



Criteria > Formula (Numeric) IS EMPTY | 

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



=> No Results


Instead, we can:


limit to only Shopify Items: Criteria > 

Shopify Product = T




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

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





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


Rookie Answered on August 10, 2022.
Add Comment

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   Become a Sponsor   Become a Sponsor