This article is relevant if you need to understand the NetSuite SuiteScript 2.0 syntax for producing a search with either a join or summary operator.
Background
NetSuite is making investments to grow its SuiteScript 2.0 API. Thus, to keep up, we old-timers need all the help we can get to learn the new syntax. The NetSuite Help document is still under development and thus code examples are forthcoming. At the time of this writing, I had a hard time finding some code examples for searching using the Join and Summary syntax. So I thought I would offer up an example.
SuiteScript 2.0 Search Example with Join and Summary Operators
//search on employees based on specific roles by joining to role table
var filters = [
search.createFilter({
name: 'isinactive',
operator: search.Operator.IS,
values: ['F']
}),
search.createFilter({
name: 'internalid',
join: 'role',
operator: search.Operator.ANYOF,
values: JSON.parse(JSON.stringify(roleList))
})
];
//get the values of the roles in the role table via SuiteScript 2.0
var s = search.create({
'type':'employee',
'filters':filters,
'columns':[
search.createColumn({'name':'firstname'}),
search.createColumn({'name':'lastname'}),
search.createColumn({'name':'internalid', join: 'role'}),
search.createColumn({'name':'name', join: 'role'})
// if we wanted to run a summary search, here is the syntax
//,search.createColumn({'name':'created','summary':search.Summary.MAX})
]
}).run();
s = s.getRange(0,1000);
Join A Team of NetSuite Innovators
If you have achieved your NetSuite Certification and wish to be part of the leading team producing NetSuite innovations for the community, let’s have a conversation.
Doesn’t SuiteScript 2.0 use boolean values for its filters, rather than ‘T’ and ‘F’?
Hello Aaron,
Yes, you are correct, you can use JavaScript true or false. However, we have found it is inconsistent likely because NetSuite is working to stabilize the new 2.0 API. Apparently the syntax offered, which mimics 1.0, is also available.
Marty
The query rendered seems to implicitly execute an INNER JOIN. Is there a way to do a LEFT JOIN as well? For example, if I wanted to get a list of customers along with the name of their parent customer, I would join through the parentcustomer relationship. However, an inner join would omit customers that do not have a parent, so a left join would be preferable.
Hi Dan,
Yes, this example is an inner join. The trick to getting a left join is to produce criteria with ORs so that you reach out to the joined customers even if they don’t have a parent. Then, you may need to use formulas in your result set that remove information that does not meet criteria.
Marty
JSON.parse(JSON.stringify(roleList)) — Does this works ? For me it gives error.
Assume that your rolelist as
roleList = {“internalid”:”1234″}.
org.mozilla.javascript.EcmaError: TypeError: Cannot find function _marshal in object [object Object]
This works for me in a browser.
var roleList = {internalid:1234}
var x = JSON.parse(JSON.stringify(roleList))
But I am confused by your question relative to this article.
When I use this code I get a error.
var roleList = {internalid:14627};
var x = JSON.parse(JSON.stringify(roleList))
TypeError: Cannot find function _marshal in object [object Object]
I asked you this question here becos I was trying to use IN like query in the filter. I have multiple ids to be choose.
Now the question is how do you find the “join: ‘role'” from “employee” record. I am referring to the current 2017 record browser and not able to find any join in name of role.
TIA
I don’t get that error when I run your code. If you have multiple IDs, you may need to use a filter expression.
Marty
Hi Marty,
Do you have any recent information on SuiteQL and Multi Join capabilities?
Kind Regards,
Viktor
Hi Viktor, not yet. But I understand something new is coming for 2018.1…
Hi Marty,
I have a SuiteScript 2.0 that load and search transaction saved search with posting period filter. In my filter I am using ‘anyof’ operator which is not working for ‘postingperiod’ field
below is sample of my code:
function getTransactionData(datain)
{
try
{
var objSearch = search.load(
{
id: datain.savedsearchid
});
objSearch.filters.push(search.createFilter({ name: “postingperiod”, operator: “ANYOF”, values: [“42”, “43”]}));
//above filter filters only record with internalid 42
result = readAllData(objSearch);
return result;
}
catch (ex)
{
log.error(“getTransactionData”, ex);
throw ex;
}
}
Please note above issue is occurring only for saved search and even in saved search only for postingperiod field, if I search other object for example ‘account’ object with internalid filter using ‘anyof’ operator, works fine.
can you please suggest me some solution for the same.
Thank you very much.
Umeth,
Did you try not quoting the values? Does it work with one value, not multiple array values? Finally, are you positive these are the internalid values you must retrieve?
Marty
Hi Marty,
Thank you so much for looking into this.
Yes, I have tried with quotes also.
Yes it works for one value, but when I pass multiple value is in an array, anyof filters only for first value of array.
Yes these are internalids. In my Netsuite account 42 is an internalid of ‘May 2003’ and 43 is an internalid of ‘June 2003’
Thank you
I have posted a query on Stack Overflow for the same:
https://stackoverflow.com/questions/52260501/suitescript-2-0-filter-operator-anyof-not-working
but till now no solution…
Umesh,
Have a look at this article which shows anyof operators. https://blog.prolecto.com/2018/03/17/learn-the-netsuite-script-pattern-to-automatically-set-lot-serial-and-bin-numbers/
Sorry for my above reply, I wanted to say:
Yes, I have tried without quotes also.
Hi Marty,
Thanks for article link. I looked into link but in this script ‘anyof’ not been used with ‘postingperiod’ field anywhere. As I mentioned in my query, this issue is only for ‘postingperiod’ field. If I use ‘anyof’ operator with other fields of transaction saved search (for example I create filter for account field using ‘anyof’ operator) it works fine.
Thanks
Hello Umesh,
You need to use the Join operator to get to multiple account periods. See this code example:
var transactionSearchObj = search.create({
type: "transaction",
filters:
[
["accountingperiod.internalid","anyof","10","100","119"]
],
columns:
[
search.createColumn({name: "mainline", label: "*"}),
search.createColumn({name: "recordtype", label: "Record Type"}),
search.createColumn({name: "source", label: "Source"}),
search.createColumn({name: "transactionnumber", label: "Transaction Number"}),
search.createColumn({
name: "trandate",
sort: search.Sort.ASC,
label: "Date"
}),
search.createColumn({name: "postingperiod", label: "Period"}),
search.createColumn({name: "type", label: "Type"}),
search.createColumn({name: "tranid", label: "Document Number"}),
]
});
var searchResultCount = transactionSearchObj.runPaged().count;
log.debug("transactionSearchObj result count",searchResultCount);
transactionSearchObj.run().each(function(result){
// .run().each has a limit of 4,000 results
return true;
});
Hi Marty,
This works like a charm.
Thank you so much…
Thank you so much…
Thank you so much…
Regards:
Umesh
Hi Marty. Your articles are probably the most informative NetSuite articles I’m aware of, so I’m hoping maybe you can answer a question I haven’t been able to find a definitive answer to. If I set the sort on a single column in a search, and then run that search as a paged search, should it sort the entire dataset by that column? Or should it just sort each page by the specified column?
For example, I’m running a transaction search that returns a couple thousand records. One of the columns is “balance”, which joins on customer. I want to sort the entire dataset by that column, and then give me the results in pages of 100. But NetSuite seems to only sort each individual page by the customer’s balance, and not the entire dataset. Is what I’m trying to accomplish even possible? Any info would be appreciated.
Thanks,
Hello Heath,
I believe that the search that you define will be the default that you start with and it should work across the page navigator. I have seen situations where NetSuite remembers your previous clicked resultset sort and this can cause some confusion. I have produced summary rankings before such as the highest sales or highest balance and have had them sort right. I suspect I may be confused by your question.
Marty