I Missed This NetSuite API Detail, and It Cost Me Hours! (Complete problem with solutions)

Hello everyone, I wanted to share a solution to a problem I encountered while working with the NetSuite API, specifically related to downloading sales orders with items using NetSuite API.

I noticed discrepancies in the quantity of some items, particularly affecting assembly-type items, where the quantity and price were not accurately reflected in the SuiteQL query results compared to the REST API.

Background 

The NetSuite API offers various ways to access data, with SuiteQL and the REST API being two primary methods. Despite their robustness, challenges can arise, as in our case with sales order quantities.

The Problem Identified in the NetSuite SuiteQL query

Our initial SuiteQL query was as follows:

SELECT 
TransactionLine.Quantity,
TransactionLine.Item
FROM 
TransactionLine
JOIN 
Item ON Item.id = TransactionLine.Item
WHERE 
TransactionLine.Transaction = '11453'
AND TransactionLine.AccountingLineType = 'INCOME'
ORDER BY 
TransactionLine.Transaction;

The results, however, were incorrect!

The sales order with ID 11453 had only 1 quantity, whereas the query result was showing -2000. This discrepancy led us to a deeper investigation.

The results, however, were incorrect, leading us to a deeper investigation.

Investigation and Analysis of API in SuiteQL

Upon further analysis, I discovered that the discrepancies primarily affected assembly-type items. The quantity and price data retrieved through SuiteQL were not accurate. I encountered an error stating that the Units of Measure table did not exist when attempting to access it for accurate data.

So, I queried for the Units of Measurement table:

select * from unitsTypeUom

Which returned to me all the existing units of measurement data as shown in the screenshot below.

but when I run the same query in Postman then it give me the following error.

the simple query that works in NetSuite UI but it doesn’t work in [Postman via suiteQL API](https://www.postman.com/jackwen2022/workspace/netsuite/documentation/10085988-be27dfba-3055-4478-9cf4-16b405d1666c).

Solution: NetSuite API User Permission

After several hours of troubleshooting, I found that the NetSuite’s API user needed the correct permission to access the UoM(Units of Measure) table. so after configuring permissions as below my NetSuite API query for UoM started giving results in Postman too !!

First small success !!

Corrected SuiteQL Query

I rewrote my original query to include UoM calculations, as shown below.

SELECT 
(TransactionLine.Quantity / U.ConversionRate * -1) AS Quantity,
TransactionLine.Item
FROM 
TransactionLine
JOIN 
Item ON Item.id = TransactionLine.Item
JOIN 
UnitsTypeUom U ON U.InternalId = TransactionLine.Units
WHERE 
TransactionLine.Transaction = '11453'
AND TransactionLine.AccountingLineType = 'INCOME'
ORDER BY 
TransactionLine.Transaction;

When I ran the same query in the Postman SuiteQL endpoint, it worked !!

Second success !!

Lessons Learned

Resolving this issue in NetSuite took about 6-8 hours, but it could have been accomplished in just 10 minutes with the right configuration in Oracle NetSuite. This experience highlights the importance of understanding the detailed configuration within NetSuite.

Also Read: 2 NetSuite API Integration Strategies You Wish You Knew Sooner

Conclusion

I hope this article proves helpful to anyone working with the NetSuite API and facing it, especially when dealing with similar challenges.