How can I pull the related transaction records on a support case in suiteql?

AI generated answer (using internal Slack threads):

To pull the related transaction records on a support case in SuiteQL, you would need to join the

transaction

table with the

supportCase

table. However, there is no direct relationship between these two tables in NetSuite. The

supportCase

table does not have a direct link to the

transaction

table. The

transaction

table does not have a

supportCase

field. Therefore, you cannot directly join these two tables in a SuiteQL query. However, you can create a custom field on the

transaction

record that references the

supportCase

record. This custom field can then be used to join the

transaction

table with the

supportCase

table in a SuiteQL query. Here is an example of how you might do this: 1. Create a custom field on the

transaction

record that references the

supportCase

record. This field could be a

List/Record

field with a record type of

Support Case

. 2. When a transaction is created or updated, populate this custom field with the

internalId

of the related support case. 3. You can then join the

transaction

table with the

supportCase

table in a SuiteQL query using this custom field. Here is an example query:

sql
SELECT T.id, SC.caseNumber
FROM transaction AS T
JOIN supportCase AS SC ON T.custbody_related_support_case = SC.id
WHERE SC.id = xxx

In this query,

custbody_related_support_case

is the internal id of the custom field you created on the

transaction

record. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******