1

I am using Postman and Netsuite's SuiteQL to query some tables. I would like to write two queries. One is to return all items (fulfillment items) for a given sales order. Two is to return all sales orders that contain a given item. I am not sure what tables to use.

The sales order I can return from something like this.

"q": "SELECT * FROM transaction WHERE Type = 'SalesOrd' and id = '12345'"

The item I can get from this.

"q": "SELECT * FROM item WHERE id = 1122"

I can join transactions and transactionline for the sale order, but no items.

"q": "SELECT * from transactionline tl join transaction t on tl.transaction = t.id where t.id in ('12345')"

The best reference I have found is the Analytics Browser, https://system.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2021_1/analytics/record/transaction.html, but it does not show relationships like an ERD diagram.

What tables do I need to join to say, given this item id 1122, return me all sales orders (transactions) that have this item?

Mule
  • 748
  • 1
  • 9
  • 24

2 Answers2

1

You can review all the available records, fields and joins in the Record Catalog page (Customization > Record Catalog).

Nat
  • 21
  • 3
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 03 '22 at 11:00
  • You may not have permissions to view the [Record Catalog](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/article_159367781370.html). If you have access it is under [Setup > Records Catalog](https://system.netsuite.com/app/recordscatalog/rcbrowser.nl). Otherwise you'll need permissions. "To assign the Records Catalog permission, first select the role you want to allow access to the Records Catalog in Setup > Users/Roles > Manage Roles. On the role page, you can then add the permission to view the Records Catalog under Permission > Setup > Add (Records Catalog)." – Minimistro Oct 14 '22 at 23:10
0

You are looking for TransactionLine.item. That will allow you to query transaction lines whose item is whatever internal id you specify.

{
    "q": "SELECT Transaction.ID FROM Transaction INNER JOIN TransactionLine ON TransactionLine.Transaction = Transaction.ID WHERE type = 'SalesOrd' AND TransactionLine.item = 1122"
}

If you are serious about getting all available tables to query take a look at the metadata catalog. It's not technically meant to be used for learning SuiteQL (supposed to make the normal API Calls easier to navigate), but I've found the catalog endpoints are the same as the SuiteQL tables for the most part.

https://{{YOUR_ACCOUNT_ID}}.suitetalk.api.netsuite.com/services/rest/record/v1/metadata-catalog/

Headers:
Accept    application/schema+json
Minimistro
  • 73
  • 7