Referencing Transaction Status Field in SQL

Hi, when I am creating saved searches, I have a very difficult time referencing the transaction status field in a SQL case statement.

For example, I’m trying to use a formula in my results to only show a value when a sales order is “billed”. I’ve tried {status} = ‘Sales Order:Billed’ and {status} = ‘SalesOrd:G’ (SuiteScript naming). None produce the result I want.

Anyone have guidance on this?

Beginner Asked on February 26, 2020 in Saved Searches.
Add Comment
1 Answer(s)

You need it to match letter for letter and space for space as to what the results display as in the UI.  In your example, CASE WHEN {status} = ‘Billed’ THEN ‘passed’ ELSE ‘failed’ END should work.

Intermediate Answered on February 26, 2020.

I see. So it needs to match the output in the search results. I guess that means you have to couple status with transaction type criterion, because, for example, a status like ‘Pending Fulfillment’ could apply to both a sales order and transfer order. So if you want only to see sales orders that are pending fulfillment, you would need {type} = ‘Sales Order’ AND {status} = ‘Pending Fulfillment’.

Thanks for your help!

on February 26, 2020.

Yeah,  you would need to couple them if you have multiple transaction types.

on February 26, 2020.
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   Become a Sponsor   Become a Sponsor