I'm facing weird Issue with the below SQL query. Basically, the query fetches the Invoices and cash sale's. When I try to execute the below query in the Tim SQL editor, I'm getting Invoices and cash sale's which is expected but the same query is excluding the Cash sale records and returning only Invoices when I run it in the suitescript code using runSuiteQL function.

SELECT

Transaction.ID,

Transaction.trandate as InvoiceDate,

Transaction.tranid,

Transaction.foreigntotal as total,

BUILTIN.DF(Transaction.entity) as Customer,

BUILTIN.DF(Transaction.bulkprocsubmission) as bulkprocsubmission,

BUILTIN.DF(Transaction.custbody_creator) as createdBy

FROM

Transaction,

transactionLine

WHERE

TRANSACTION.ID = transactionLine.TRANSACTION

AND transactionLine.mainline = 'T'

AND (

(

TRANSACTION.TYPE = 'CustInvc'

AND TRANSACTION.approvalstatus = '1'

)

OR (

TRANSACTION.TYPE = 'CashSale'

AND TRANSACTION.status = 'CashSale:B'

)

)

how many results are you getting?

@jen I’m getting 7 results In SuiteQL editor and 6 results in suitescript logs. There is one cash sale record in the system. It is missing.

Are they being executed under different permissions?

Nope Matt. I’m running suitelet which has Admin role.

Our accounts maybe different but

AND TRANSACTION.status = 'CashSale:B'

returns nothing for me but if i switch to

AND TRANSACTION.status = 'B'

I get the expected results. What is the

'CashSale:B'

?

Hey @Matt Bernstein, It work's perfectly with the status = 'B'. Thanks for the help!

Hey, you should use BUILTIN.CF(transaction.status) = 'CashSale:B'