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?
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.
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!
Yeah, you would need to couple them if you have multiple transaction types.
