Javascript NetSuite Expense Report Array Efficient

Question

Alright so what I need to do is create a script for netsuite that cycles through each expense in an expense report, and updates the tax code based on a search of a custom record. What I need help with is efficiency - usually there are the same category of expense being searched multiple times and because of the allotted governance I want to make it so I call the search the minimum amount of times, not searching the same category multiple times. Here is what I have :

/*
 * Pseudocode:
 * search minimum amount of times - do not search same category multiple times
 * for loop cycle through each expense and save category to array
 * if category is first occurrence complete search for tax code
 * else if category is a repeat set tax code based on previous search
 * 
 */



function userEventBeforeSubmit(type){

    /*if(type!='create')
    {
        return;
    }
    */

    //amount of expenses per expense report
    var expenseNum = nlapiGetLineItemCount('expense');
    var intSubsidiary = nlapiGetFieldValue('subsidiary');

    //this for loop is attempting to push the unique categories into an array leaving out duplicates 
    for(var i = 0; i < expenseNum; i++){
        var intCategory = nlapiGetLineItemValue('expense', 'category', i);
        var arr = [];
        //if category not found push into array
        if(arr.indexOf(intCategory) === -1){
            arr.push(intCategory);
        }

        //search expense tax codes record
        var arrSearchFilters = new Array();
        arrSearchFilters[0] = new nlobjSearchFilter('custrecord_cv_expensesubsidiary', null, 'anyof', intSubsidiary);
        arrSearchFilters[1] = new nlobjSearchFilter('custrecord_cv_expensecategory', null, 'anyof', arr[i]);

        //field from expense tax codes record
        var arrSearchColumns = new Array();
        arrSearchColumns[0] = new nlobjSearchColumn('custrecord_cv_taxcode');

        var arrSearchResults = nlapiSearchRecord('customrecord_cv_expensetaxcodes', null, arrSearchFilters, arrSearchColumns);

    }






        //for loop to iterate through expenses in expense report
        for(var i =1; i < expenseNum+1; i++){

            //get value category for each line
            //var intCategory = nlapiGetLineItemValue('expense', 'category', i);
            var taxAmtTemp = nlapiGetLineItemValue('expense', 'taxamount', i);

            var searchResult = arrSearchResults[0];
            //set tax code 
            var taxCode = searchResult.getValue(arrSearchColumns[0]);
            nlapiSetLineItemValue('expense', 'taxcode', i, taxCode);

            //if tax amount changes set back to original amount
            if(taxAmtTemp != nlapiGetLineItemValue('expense', 'taxamount', i)){

                nlapiSetLineItemValue('expense', 'taxamount', i, taxAmtTemp);

            }  


        }




}

Answer 1

The system you have written seems fairly reasonable, but there is one easy optimization you can make. The 'anyof' filter can accept an array of values. So build your array of categories in the first loop.

var arrExpenseCategory = [];
for(var i = 1; i < expenseNum+1; i++){
    var intCategory = nlapiGetLineItemValue('expense', 'category', i);
    if(arrExpenseCategory.indexOf(intCategory) === -1){
        arrExpenseCategory.push(intCategory);
    }
}

Then search for those records.

//search expense tax codes record
var arrSearchFilters = [
   new nlobjSearchFilter('custrecord_cv_expensesubsidiary', null, 'anyof', intSubsidiary), 
   new nlobjSearchFilter('custrecord_cv_expensecategory', null, 'anyof', expenseCategoryArr)
]

//field from expense tax codes record
var arrSearchColumns = new Array();
arrSearchColumns[0] = new nlobjSearchColumn('custrecord_cv_taxcode');
arrSearchColumns[1] = new nlobjSearchColumn('custrecord_cv_expensecategory');

Then place those records in an array.

var arrSearchResults = nlapiSearchRecord('customrecord_cv_expensetaxcodes', null, arrSearchFilters, arrSearchColumns);
arrExpenseCategory = [];
for(var i = 0; i < arrSearchResults.length; i++){
    arrExpenseCategory[arrSearchResults.getValue(arrSearchColumns[1])] = arrSearchResults.getValue(arrSearchColumns[0]);
}

Then use the array to fill your fields.

for(var i =1; i < expenseNum+1; i++){
    var taxAmtTemp = nlapiGetLineItemValue('expense', 'taxamount', i);
    nlapiSetLineItemValue('expense', 'taxcode', i, arrExpenseCategory[nlapiGetLineItemValue('expense', 'category', i)]);
    //if tax amount changes set back to original amount
    if(taxAmtTemp != nlapiGetLineItemValue('expense', 'taxamount', i)){
        nlapiSetLineItemValue('expense', 'taxamount', i, taxAmtTemp);
        }  
    }

As it's not stated in the original question, I'm assuming that this will run on the beforeSubmit event. It would also run fine on the client beforeSave event. The only other caution I have is that nlapiSearchRecord only returns up to 1000 results. If you have more than 1000 customrecord_cv_expensetaxcodes record with that subsidiary this script will not work for you. You are essentially stuck running this in a scheduled script as mentioned by eliseobeltran.

Answer 2

Do the user needs to see the proper tax code (based on the custom record search return value) after the record was created?

If you are worried about the governance you can make this a scheduled script - that way the script usage allotted would suffice.

You can then pass the internal id of the expense record to the scheduled script and make it execute as soon as the record is save. So your user submit event would serve as a trigger and would pass the record id as parameter and execute the scheduled script. Depending on the queue, the record should be updated in a matter of minutes. Although, changes in the record won't occur in real time.