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

Rookie Asked on November 16, 2022 in

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

Intermediate Answered on November 17, 2022.

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.

Hi, this was my rookie error.  A criteria was causing the issue.  Thanks for the replies.

Rookie Answered on November 23, 2022.