Seriously, I don’t know why this was so difficult, but I could not find a full working example of an ad hoc search written in SuiteScript 2.0 that included a join. Let me give a quick shout-out to Marty Zigman. Thanks for getting me 90% of the way there!
Here’s Marty’s example: http://blog.prolecto.com/2016/10/22/netsuite-suitescript-2-0-search-join-and-summary-example/
I’m a bit stupified by the .run().getRange(0,1000). I was trying to use .run().each() and could not get it to work. I’m not a fan of the for-loop in this case. Whatever!… This works!
Here’s the example. This script lists files in a folder that begin with text of your choosing.
And here’s the code so that you can easily copy it.
/**
*@NApiVersion 2.x
*@NScriptType ScheduledScript
*/
define([‘N/search’, ‘N/log’],
function (search, log) {
function execute(context) {
var mySearch = search.create({
type: search.Type.FOLDER,
columns: [
search.createColumn({
name: ‘internalid’,
join: ‘file’
}),
search.createColumn({
name: ‘name’,
join: ‘file’
}),
],
filters: [
search.createFilter(
{
name: ‘name’,
operator: search.Operator.IS,
values: [‘Your folder name here’]
}
),
search.createFilter(
{
name: ‘name’,
join: ‘file’,
operator: search.Operator.STARTSWITH,
values: [‘Your file name here’]
}
),
]
});
var result = mySearch.run().getRange(0, 1000);
var ids = new Array();
var names = new Array();
for (var i = 0; i < result.length; i++) {
ids.push(result[i].getValue({ name: ‘internalid’, join: ‘file’ }));
names.push(result[i].getValue({ name: ‘name’, join: ‘file’ }));
}
log.debug(‘ids’, ids.join(‘,’));
log.debug(‘names’, names.join(‘,’));
return true;
}
return {
execute: execute
};
})
NetSuite Experiences 
If I need 1000000 results , how to to code my getRange() function.
LikeLike
var myPages = mySearch.runPaged({ pageSize: 1000 });
for (var i = 0; i < myPages.pageRanges.length; i++) {
var myPage = myPages.fetch({ index: i });
myPage.data.forEach(
function (result) {
var internalid = result.getValue('internalid');
customers.push(internalid);
}
)
}
LikeLike
Thanks for posting this, this helped me understand how to create a join. Still trying to figure out how to use an AND with it. I get an error that filters has to be an array, even though it is.
Regarding not getting run().each to work: Were your forgetting to return true? I do this all the time.
LikeLike
You can easily add AND and OR logic using filter expressions.
LikeLike
You can AND/OR with all array based filters but it doesn’t seem to like them when using createFilter objects and ‘AND’ string or trying to mix createFilter objects and array based filter criteria with the ‘AND’ string. I think you have to use the createFilter object when using joins? At least, I haven’t figured out the array based version of them.
LikeLike