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 How To's.
Add Comment
3 Answer(s)

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

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

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

Rookie Answered on November 23, 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   Limebox   Become a Sponsor   Become a Sponsor