Suitescript Saved Search Filter using other saved search results

Question

I am trying to use the results of a specific saved search to try and filter another saved search in suitescript.

Basically, there is a button created on a project. Once the button is clicked, I need to go get all the tasks for that specific project and use each task to filter on a transaction saved search using a custom field and get whatever information is on that saved search.

This is what I have so far:

function runScript(context) {
    var record = currentRecord.get();
    var id = record.id;
    var type = record.type;
    var i = 0;
    console.log(id);
    var projectSearch = search.load({id: 'customsearch1532'})
    var billableExpenseSearch = search.load({id: 'customsearch1533'})

    var projectFilter = search.createFilter({
        name:'internalId',
        operator: search.Operator.IS,
        values: id
    });
    projectSearch.filters.push(projectFilter);

    var projectResults = projectSearch.run().getRange(0,1000);

    while(i < projectResults.length){
        var task = projectResults[i].getValue(projectSearch.columns[1]);
        console.log(task);
        var billableExpenseFilter = search.createFilter({
            name:'custcol4',
            operator: search.Operator.ANYOF,
            values: task
        });

        billableExpenseSearch.filters.push(billableExpenseFilter);
        var billableExpenseResults = billableExpenseSearch.run().getRange(0,1000);
        console.log(billableExpenseResults.length);

        for(var j = 0; j< billableExpenseResults.length; j++){
            var testAmount = billableExpenseResults[j].getValue(billableExpenseSearch.columns[3]);
            console.log(testAmount);
        }
        i++;

    }
}

The log for the Task is correct. I have 2 tasks on the project I am trying this on but once we get to the second iteration, the billableExpenseSearch length is showing as 0, when it's supposed to be 1.

I am guessing that my logic is incorrect of the createFilter function doesn't accept changes once the filter is created. Any help is appreciated!

EDIT:

var billableExpenseSearch = search.load({id: 'customsearch1533'});
        var billableExpenseFilter = search.createFilter({       
            name:'custcol4',
            operator: search.Operator.ANYOF,
            values: task
        });
        billableExpenseSearch.filters.push(billableExpenseFilter);
        var billableExpenseResults = billableExpenseSearch.run().getRange(0,1000);
        console.log(billableExpenseResults.length);
        for(var j = 0; j< billableExpenseResults.length; j++){
            var taskid = billableExpenseResults[j].getValue(billableExpenseSearch.columns[0]);
            console.log(taskid);

Thank you

Mohammad Mattour Mohammad Mattour · Accepted Answer · 2021-03-15T23:47:06

I think your guess is correct your are keep pushing filters

    billableExpenseSearch.filters.push(billableExpenseFilter);

After pushing the filter and extract the value you need to remove it before adding a new one, you can do this by pop() the last one:

    billableExpenseSearch.filters.pop();

Note: You can fix this by re-loading the search every time before pushing the filter. this will reset your filters, but I do NOT recommend that since loading a search will consume more USAGE and might receive USAGE_LIMIT_EXCEEDED ERROR.

I also recommend the following:

1- Get all task ids before doing the second search, once you do that you only need to search once. Because if you have many records you might encounter USAGE_LIMIT_EXCEEDED ERROR. Since you work with a client or Suitelet script you only have 1000 USAGE.

Edit: Sample might help you.

var ids = [];
         var pagedData = projectSearch.runPaged({pageSize : 1000});
         // iterate the pages
         for( var i=0; i < pagedData.pageRanges.length; i++ ) {
         // fetch the current page data
                 var currentPage = pagedData.fetch(i);
                 // and forEach() thru all results
                 currentPage.data.forEach( function(result) {
                 // you have the result row. use it like this....
                    var id = result.getValue(projectSearch.columns[1]);
                     Ids.push(id);
                 });
         }

Note: This search will extract all records not only first 1000.

After that add the array to the Filter

var billableExpenseFilter = search.createFilter({       
                name:'custcol4',
                operator: search.Operator.ANYOF,
                values: [ids]
            });

2- Don't Use search.load use search.create it will make your script more readable and easier to maintain in the future.

Suitescript Saved Search Filter using other saved search results

1 Answers