### RE: 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.

memariani Rookie Asked on November 16, 2022 in

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.

Beginner Answered on November 22, 2022.