thanks for the help with this:
https://community.oracle.com/netsuite/discussion/4473899/how-can-i-search-for-balance-due/p1?new=1
related:
V1: this returns 1 unique record with this added: and tal.transactionline = tl.id
--> but needed "amountunpaid" field is missing
{ "q": "SELECT * FROM transaction t LEFT JOIN transactionline tl ON t.id = tl.transaction LEFT JOIN transactionaccountingline tal ON t.id = tal.transaction LEFT JOIN account ac ON ac.id = tal.account where tl.custcol1 IS NOT NULL and t.posting = 'T' and tl.uniquekey = xxxxx and tal.transactionline = tl.id "
}
---
V2: this returns 2 records (dupe uniqueid unwanted) without this added: and tal.transactionline = tl.id
{ "q": "SELECT * FROM transaction t LEFT JOIN transactionline tl ON t.id = tl.transaction LEFT JOIN transactionaccountingline tal ON t.id = tal.transaction LEFT JOIN account ac ON ac.id = tal.account where tl.custcol1 IS NOT NULL and t.posting = 'T' and tl.uniquekey = xxxxx "
}
Q1: Is there a way to use version 1 and also get the needed "amountunpaid" field in results?
Also I see these fields:
"amountpaid": "0",
"amountunpaid": "0",
and
"foreignamountpaid": "0",
"foreignamountunpaid": "0",
Q2: so are the amountpaid and foreignamountpaid always the same?
Q3: also are the amountunpaid and foreignamountunpaid always the same?
Thanks - Dave