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
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.
