Why doesn’t this formula work to show multiple transaction types in saved search
Good day. I am not understanding why the following formula works in my transaction saved search criteria:
(Formula Numeric) equals 1
CASE WHEN ({type} = ‘Item Receipt’ AND SUBSTR({appliedtotransaction},1,8) = ‘Transfer’) THEN 1 ELSE 0 END
But, when I do this, it only shows Cash sales and Invocies:
CASE
WHEN ({type} = ‘Item Receipt’ AND SUBSTR({appliedtotransaction},1,8) = ‘Transfer’) THEN 1
WHEN ({type} = ‘Invoice’ )THEN 1
WHEN ({type} = ‘Cash Sale’ )THEN 1
ELSE 0 END
This returns 900K rows. When I sort by the type, it only shows Cash Sales and Invoices. It doesn’t make sense that the Item receipt would no longer appear. I don’t see any other filter that would prevent.
Any ideas? Thanks!
M.
Hi,
What’s the purpose of the filter? Are you only looking for Item Receipts for Transfer Orders?
Could you also remove the criteria and add Applied To Transaction to the columns. What does this show?
Thanks,
Chris
It’s a little tricky without knowing exactly what you’re trying to achieve or seeing to search to see if there is anything else not formula related causing the problem. I see two options to try. You could set 1x criteria as TYPE is any of Item Receipt, Invoice, Cash Sale. Then do a formula criteria such as below.
(Formula Numeric) equals 1
CASE WHEN {type} = ‘Item Receipt’ THEN
CASE WHEN SUBSTR({appliedtotransaction},1,8) = ‘Transfer’ THEN 1
ELSE 0 END
ELSE 1 END
It’s also possible that it’s somehow the order or the parenthesis that are causing issues. I don’t ever use parenthesis in mine. You could also try:
CASE
WHEN {type} IN(‘Invoice’,‘Cash Sale’) THEN 1
WHEN {type} = ‘Item Receipt’ AND SUBSTR({appliedtotransaction},1,8) = ‘Transfer’ THEN 1
ELSE 0 END
If none of those work I would likely try to get away from using the SUBSTR function. Maybe use: {appliedtotransaction.type} = ‘Transfer Order’.
Hopefully one of those options works.
Hi, this was my rookie error. A criteria was causing the issue. Thanks for the replies.
