Is anyone evaluating the transaction status as a query criteria? I am connecting to netsuite2 and trying to query approved sales orders that are flagged for a custom process. If I return the status of a specific test sales order in sandbox with

select id, status from transaction where id = 1234

the result comes back with a status of

B

. But for some reason

select id, status from transaction where id = 1234 and status = 'B'

I get no results. I have also used the query module in a test script and see the same behavior. Is there something different with how the status field is handled? This is driving me crazy!

In the Where clause of your query, you will need to use

'SalesOrd:B'

for the status filter. You need to use the value form the SearchFilter column in this SA article: https://suiteanswers.custhelp.com/app/answers/detail/a_id/50165 As for why the status column as displayed does not match the value you are searching on, that drives me crazy too.

It is confusing. In general, you'll want to use something like this:

SELECT TOP 10 TranID, Status, <http://BUILTIN.CF|BUILTIN.CF>( Status ), BUILTIN.DF( Status ) FROM transaction WHERE Status = 'SalesOrd:B'

This might help explain how it really works: https://timdietrich.me/blog/netsuite-suiteql-sales-report/ Scroll down to "Understanding the Transaction Statuses, BUILTIN.CF, and the BUILTIN.DF Functions."

Nice, I was not aware of the BUILTIN.CF, but that is going to become very useful to me now

@Ben Tanner There's a #C01FBBZ8PQC channel that might also prove to be helpful.

SuiteAnswer’s description for that CF function does not really seem helpful:

Sets the field usage context to CRITERIA

I would never have guessed how it worked from that alone. https://suiteanswers.custhelp.com/app/answers/detail/a_id/99191/loc/en_US

Thanks for the links. I was aware of those status strings and have used them with the old schema. I talked myself out of it once I looked at the values being returned. The functions are new to me so I'm reading up on those. Big thanks for the resources.