One common question among our customers is “How do we find the related transaction in ODBC/JDBC/SuiteQL?”. In this article, you can learn more about ODBC connectivity in NetSuite as we delve into querying 'Related Records' using the NetSuite2.com Data-source.
There are two tables in the NetSuite2.com schema that you can use: previoustransactionlink and nexttransactionlink. As of now, these two contain nearly – if not totally – identical data. They contain three fields:
- previousdoc – this is the transaction id of the origin document. In the UI, these are the transactions that you can find in the “Created From” field.
- nextdoc – contains the transaction id of the child document.
- linktype – shows the relationship between the two transactions.
In the sample query below, I used JOIN in the nexttransactionlink table to include the recordtype field of both previous and next doc from the transaction table in the result.
SELECT Link.previousdoc, TrnPrev.recordtype AS previousdoc_recordtype, Link.nextdoc, TrnNext.recordtype AS nextdoc_recordtype, Link.linktype
FROM nexttransactionlink as Link
JOIN transaction as TrnPrev ON Link.previousdoc = TrnPrev.id
JOIN transaction as TrnNext ON Link.nextdoc = TrnNext.id;
Here’s the result:
-----
Your feedback matters! If you found this article useful, we'd love to hear from you. Leave a comment or reaction to let us know how it helped you, and help others discover its value too.