As of release 2019.1 Netsuite has released SuiteAnalytics Workbooks. SuiteScript writers could leverage the power of multilevel joins in searches. We have become accustomed to doing the impossible with saved searches but now you could use queries. Workbooks with multilevel joins could be used in place of saved searches. I couldn’t wait to try it out. Just itching for a reason to use a query in my code. Finally, the need arose, our IS team created a search that went two(or twenty-two) levels deep to find a buildable quantity. We needed to check this quantity as SO line items were entered. I will share this experience with you and hope to make your journey into SuiteAnalytics workbooks a pleasant one.
At the time of writing the code the documentation was sparse. Good solid examples were few and far. NetSuite documentation gave a basic usage of queries with code to create one and view the data. If I was to use this during line item entry it would need to be mean and lean. I couldn’t create a new workbook every time. I needed to be able to manipulate the saved workbook. Since it’s a new product my friend Google wasn’t much help either. The community I have come to rely on had nothing for me. It was time to earn my check. Yes, its true I was going to write it. What’s the world coming to. Another rogue developer inventing a wheel.
First part wasn’t very difficult. The query module has a load method:
var loadedQuery = query.load({id: myQueryId});
I loaded the complex query had a query object to work with. Now I needed to add filters. Its inconsistent how Netsuite would’nt have made it similar to adding filters to a saved search. The two search modules couldn’t be less similar, but in their defense we aren’t in Kansas anymore Toto. SuiteAnalytics workbooks are as complex as they are powerful. To add filters to queries you use conditions. I used the createCondition method to create my first filter.
var queryComponent = loadedQuery.child.bomassemblylocation;
var firstCondition = queryComponent.createCondition({
fieldId: ‘assembly’,
operator: query.Operator.EQUAL,
values: itemId
});
This established I was filtering the assembly items by the value of item Id. When creating a condition, you need be aware of what table the condition is bound to. The same flexibility is what adds to the complexity. After I established a reference to the table, I created the condition required to filter the assembly items by item.
Now I thought I was home free. Off to the races as it were! I was now ready to create my 2nd condition. The logic was somewhat similar, find the table I wanted to filter and create the condition. This part took some reverse engineering to get to. I admit I delved deep into the Netsuite Suitescript debugger on this one. I concluded that I needed to filter the location of the item from the bomrevision components sublist. Forget 3 levels deep we going 5 levels here. Rule 1 Never underestimate a partner’s ability to abuse a nice new feature in an ERP system. The new condition:
var queryComponent1 = loadedQuery.child.bomrevision.child.component.child.item.child.locations;
var secondCondition = queryComponent1.createCondition({
fieldId: ‘location’,
operator: query.Operator.EQUAL,
values: locationId
});
Now that I established my 2 conditions simply join them to the query using the and method then run.
loadedQuery.condition = loadedQuery.and(firstCondition,secondCondition);
var resultSet = loadedQuery.run();
I think the difficulty I found in this one was from the complexity of the workbook created. When implementers or end users are creating complex queries using this amazing tool the fields to filter on are sometimes buried in a table 3 (or 5 in this case) levels deep. Isolating that field and querying on it takes some practice and of course trial and error. So with this guide and some practice you will be able to add filters to a saved SuiteAnalytics workbook in Suitescript. So maybe the next time some one Googles Netsuite query object this article will guide some poor lost hack like me thru a difficult afternoon. Happy Coding All.
Need more help? MIBAR offers expert NetSuite support. Contact us today to learn more.
