What Type Of Support Is Available For NetSuite?

This article assumes you've already setup a connection to NetSuite. 

Next, click on Transaction > Add Transactions and select NetSuite.

Here is a basic SuiteQL query:

SELECT T.*, TO_CHAR(T.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SSxFF') AS lastmodifieddate FROM Transaction T WHERE lastmodifieddate >= @Threshold ORDER BY lastmodifieddate ASC

Your SQL query must allow for incremental data retrieval based on an update field:

1. Specify the update field field name for your table.
   
1. Typically, this will be "lastmodifieddate", as shown in the example above
   
2. However, for Transaction Line Items, it must be "linelastmodifieddate", as per NetSuite documentation
2. Your SELECT statement should include the update field.
1. "SELECT T.*, TO_CHAR(T.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SSxFF') AS lastmodifieddate" works because it includes the update field.
2. "SELECT Amount" does NOT work because it does NOT include the update field.
3. Your WHERE statement should include an AND condition with update field >= @Threshold - examples:
   
1. "WHERE lastmodifieddate >= @Threshold" works because it has a condition on the update field.
2. "WHERE Amount > 2000 AND lastmodifieddate >= @Threshold" works because it has an AND condition on the update field.
   
3. "WHERE Amount > 2000" does NOT work because it does NOT have a condition on the update field.
4. "WHERE Amount > 2000 OR lastmodifieddate >= @Threshold" does NOT work because it has an OR condition on the update field.
   
4. Your ORDER BY statement should include a primary ordering by update field ascending - examples:
   
1. "ORDER BY lastmodifieddate ASC, " works because it has primary ordering by update field ascending.
   
2. "ORDER BY lastmodifieddate ASC, Id DESC" works because it has primary ordering by update field ascending.
3. "ORDER BY ID ASC" does NOT work because it does NOT have primary ordering by update field ascending.
   
4. "ORDER BY ID ASC, lastmodifieddate ASC" does NOT work because it does NOT have primary ordering by update field ascending.

Retrieving Transaction Line Items

To retrieve transaction line items (and also pull values from parent transactions), use the following as a starting template:

SELECT TO_CHAR(TL.linelastmodifieddate, 'YYYY-MM-DD HH24:MI:SSxFF') AS linelastmodifieddate, TL.uniquekey AS "Unique ID (Line Item)", BUILTIN.DF(TL.itemtype) AS "Item Type (Line Item)", BUILTIN.DF(TL.department) AS "Department (Line Item)", BUILTIN.DF(TL.class) AS "Class (Line Item)", BUILTIN.DF(TL.location) AS "Location (Line Item)", TL.linesequencenumber AS "Line Sequence Number (Line Item)", TL.transaction AS "Transaction Unique ID", TL.quantity AS "Quantity (Line Item)", TL.rate AS "Rate (Line Item)", TL.netamount AS "Net Amount (Line Item)", TL.memo AS "Memo (Line Item)", Tl.item AS "Item Unique ID (Line Item)", T.closedate AS "Closed Date", T.createddate AS "Create Date", BUILTIN.DF(T.Currency) AS Currency, T.duedate AS "Due Date", T.exchangerate AS "Exchange Rate", T.id AS "Transaction ID", T.number AS "Transaction Number", BUILTIN.DF(T.status) AS Status, T.billingstatus AS "Billing Status", T.trandate AS "Transaction Date", T.trandisplayname AS "Display Name", T.tranid AS "Transaction ID", T.transactionnumber AS "Transaction Number", BUILTIN.DF(T.type) AS "Transaction Type", T.voided AS Voided, BUILTIN.DF(T.employee) AS "Employee Name", BUILTIN.DF(T.entity) AS Customer, T.abbrevtype AS "Abbrev Type", BUILTIN.DF(T.createdby) AS "Created By", T.isreversal AS "Is Reversal", BUILTIN.DF(T.recordtype) AS "Record Type", BUILTIN.DF(T.terms) AS Terms, T.title AS Title, BUILTIN.DF(T.lastmodifiedby) AS "Last Modified By", BUILTIN.DF(T.ordertype) AS "Order Type", T.email AS Email, T.trandisplayname AS "Transaction Display Name", T.basetaxtotal AS "Tax Total", T.taxtotal AS "Tax Total (Foreign Currency)", T.basetotalaftertaxes AS "Total After Taxes", T.totalaftertaxes AS "Total After Taxes (Foreign Currency)", T.foreigntotal AS "Total Amount (Transaction Currency)" FROM TransactionLine AS TL JOIN Transaction AS T ON T.id = TL.transaction WHERE linelastmodifieddate >= @Threshold ORDER BY linelastmodifieddate ASC

This query retrieves transaction line items, joining with transactions, giving friendly names to fields.

Note the following in the query above:

1. "AS" is used to provide friendly names to fields
   
1. "AS" is also used to provide table name aliases, such as "TL" for TransactionLine
   
2. "LEFT JOIN" is used to join from TransactionLine records to parent Transaction records
   
1. The LEFT join indicates that the join is optional (i.e. we will retrieve line items without a Transaction)
3. "BUILTIN.DF()" is a NetSuite function to retrieve the display name of a related record without requiring a join
   
1. NetSuite Issue - some calls to BUILTIN.DF() can cause unexpected INNER joins
   
2. For example, this may happen if you join with the Item table, and apply BUILTIN.DF() to Item fields
3. Please refer to this blog post for details
   
4. 
   
4. TO_CHAR(<update field>, 'YYYY-MM-DD HH24:MI:SSxFF') is used to retrieve timestamps
   
1. NetSuite Issue - without TO_CHAR(), simple dates are returned instead of timestamps
   
2. Simple dates do not have sufficient time precision to deliver performant incremental data syncs
5. When querying LineItem records, use linelastmodifieddate (instead of lastmodifieddate normally)
   
1. NetSuite Issue - the lastmodifieddate is NOT reliable for line item
2. This is a documented NetSuite issue
   
3. 
   

Our support team can help you craft correct SuiteQL queries.

Here are NetSuite schema references with standard field names:

1. Transaction - https://www.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2020_1/analytics/record/transaction.html
   
2. TransactionLine - https://www.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2020_1/analytics/record/transactionLine.html
   
3. Item - https://www.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2020_1/analytics/record/item.html
   
4. Customer - https://www.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2020_1/analytics/record/customer.html

Here are additional examples.

Retrieving Items

To retrieve items from product lines with friendly field names:

SELECT TO_CHAR(I.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SSxFF') AS lastmodifieddate, I.id AS "Unique ID (Item)", I.description AS "Description (Item)", I.fullname AS "Full Name (Item)", BUILTIN.DF(I.itemtype) AS "Item Type (Item)", BUILTIN.DF(I.department) AS "Department (Item)", BUILTIN.DF(I.class) AS "Class (Item)", BUILTIN.DF(I.location) AS "Location (Item)" FROM Item AS I WHERE lastmodifieddate >= @Threshold ORDER BY lastmodifieddate ASC

Retrieving Transactions

To retrieve transactions with friendly field names:

SELECT TO_CHAR(T.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SSxFF') AS lastmodifieddate, T.id AS "Unique ID (Transaction)", T.closedate AS "Closed Date", T.createddate AS "Create Date", BUILTIN.DF(T.Currency) AS Currency, T.duedate AS "Due Date", T.exchangerate AS "Exchange Rate", T.number AS "Transaction Number", BUILTIN.DF(T.status) AS Status, T.billingstatus AS "Billing Status", T.trandate AS "Transaction Date", T.trandisplayname AS "Display Name", T.tranid AS "Transaction ID", T.transactionnumber AS "Transaction Number", BUILTIN.DF(T.type) AS "Transaction Type", T.voided AS Voided, BUILTIN.DF(T.employee) AS "Employee Name", BUILTIN.DF(T.entity) AS Customer, T.abbrevtype AS "Abbrev Type", BUILTIN.DF(T.createdby) AS "Created By", T.isreversal AS "Is Reversal", BUILTIN.DF(T.recordtype) AS "Record Type", BUILTIN.DF(T.terms) AS Terms, T.title AS Title, BUILTIN.DF(T.lastmodifiedby) AS "Last Modified By", BUILTIN.DF(T.ordertype) AS "Order Type", T.email AS Email, T.trandisplayname AS "Transaction Display Name", T.basetaxtotal AS "Tax Total", T.taxtotal AS "Tax Total (Foreign Currency)", T.basetotalaftertaxes AS "Total After Taxes", T.totalaftertaxes AS "Total After Taxes (Foreign Currency)", T.foreigntotal AS "Total Amount (Transaction Currency)" FROM Transaction AS T WHERE lastmodifieddate >= @Threshold ORDER BY lastmodifieddate ASC

Retrieving Customers

To retrieve customers with friendly field names:

SELECT TO_CHAR(C.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SSxFF') AS lastmodifieddate, C.datecreated AS "Date Created (Customer)", C.id AS "Unique ID (Customer)", C.entityid as "Entity ID (Customer)", C.companyname AS "Company (Customer)" FROM Customer AS C WHERE lastmodifieddate >= @Threshold ORDER BY lastmodifieddate ASC

To learn more about connections, click here.

To learn more about managing synchronizations, click here.
To learn more about importing transactions, click here.

• Related Articles

• What Type Of Support Is Available For Stripe?

  By adding a Stripe connection to your workspace, you can automatically import and sync sales transactions to your workspace We support charges, invoices, refunds You will need to map fields the first time you import transactions Newly created Stripe ...

• What Type Of Support Is Available For SalesForce?

  By adding a SalesForce connection to your workspace, you can: Automatically import users into your workspace We will detect users who are already present within your workspace and only suggest adding missing ones You will be able to review and edit ...

• What Type Of Support Is Available For QuickBooks Desktop?

  All editions of QuickBooks Desktop are supported: Pro, Enterprise, etc. By adding a QuickBooks connection to your workspace, you can: Automatically import users into your workspace We will detect users who are already present within your workspace ...

• What Type Of Support Is Available For QuickBooks Online?

  By adding a QuickBooks connection to your workspace, you can: Automatically import users into your workspace We will detect users who are already present within your workspace and only suggest adding missing ones You will be able to review and edit ...

• What Type Of Support Is Available For Microsoft Dynamics CRM?

  By adding a Microsoft Dynamics CRM connection to your workspace, you can: Automatically import users into your workspace We will detect users who are already present within your workspace and only suggest adding missing ones You will be able to ...