I missed this NetSuite API detail, and It cost me hours! (Complete problem with solutions)
Updated: April 11, 2024
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: Netsuite API
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 Netsuite SuiteQL query
Our initial SuiteQL query was as follows:
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 Netsuite API
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:
Which returned me all the existing units of measurement data as shown in screenshot below.
but when i run same query in postman then it give me following error.
“invalid search query. Detailed unprocessed description follows. Search error occurred: record ‘UnitsTypeUom’ was not found”
the simple query which works in Netsuite UI but it doesn’t work in postman via suiteQL api.
Solution: Netsuite API User Permission
After several hours of troubleshooting, I found that the Netsuite 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 rewrite my original query to include UoM calculations as below.
When I ran the same query in the Postman SuiteQL endpoint, it worked !!
Second success !!
Lessons Learned
Resolving this Netsuite API issue took about 6-8 hours, but with the right configuration in Netsuite, it could have been accomplished in just 10 minutes. This experience highlights the importance of understanding the detailed configuration within Netsuite.
Conclusion
I hope this article proves helpful to anyone working with the Netsuite API and facing it, especially when dealing with similar challenges.
Need help with Netsuite API integration? Don’t hesitate to contact us for expert support.