netsuite suitescript filter expression not producing the search results needed

Question

    var filterExp = [ 
                [ ['custrecord_rebate_customer.isinactive', 'is', 'F'],
                   'and',
                  ['custrecord_rebate_customer.custrecord_start_date', 'before', date],
                  'and',
                  ['custrecord_rebate_customer.custrecord_end_date', 'after', date],
                  'and',
                  ['custrecord_rebate_customer.custrecord_upaya_warehouse', 'anyof', warehouse],
                  'and',
                  ['isinactive', 'is', 'F' ],
                  'and',
                  ['custrecord_customer_name', 'anyof', customer]],

                  'or',

                 ['custrecord_rebate_customer.custrecord_global_clearance_item','is','T']


                  ];

I have a search that I am running to get results. I need to use an expression because I need to have an "or" in the filter. So in the UI I have the search built and it's producing the correct results. I get 7 results. Now in my code when I alert the search results I get 6 which means it's failing to do the "or" part of the code. I can't seem to figure out how to use the "or". First time with expressions in code so it might be a simple thing to fix. I have tried the brackets in every way possible. Does anyone have any solutions? Thanks in advance!

which part of the or is it not running? The section containing the date or the section containing the clearance test? If it's the date part do you actually have dates in the expected fields? — bknights, Dec 14 '15 at 19:49
The clearance part is not running. Everything before the or is running perfectly. I am getting 6 results which is correct. Should be 7 results total including the clearance. With the code above in place I get 6 results. So it is not seeing the final clearance rebate item. But in the UI I get 7. — Mike A., Dec 14 '15 at 21:28
Syntactically it looks correct. I'd be curious to see the search filters you've set up in the UI as well as the results you are expecting in the UI. — erictgrubaugh, Dec 14 '15 at 22:17

score 0 · Accepted Answer · answered Dec 15 '15 at 03:23

If your search is working correct in UI, you can try to obtain the filter expression in code by loading search and then using it in your code.

x = nlapiLoadSearch(null, YOUR_UI_SEARCH_ID);
y = nlapiSearchRecord(YOUR_CUSTOM_RECORD_ID, null, x.getFilterExpression());

y should produce the same result as that of x in UI. You can also see the contents of x.getFilterExpression() to match with your filter expression.

Also, if you are not interested in doing/matching filter expression yourself in code you can use below code to fetch results

nlapiLoadSearch(null, YOUR_UI_SEARCH_ID).runSearch().getResults(0, 1000);

This pointed me in the right direction. Thanks for reminding me that I can grab those values from the search. So the UI search I was doing was on a different custom record than the one in code. I changed the UI to reflect code and got the same results. So the code was correct. Thanks! — Mike A., Dec 15 '15 at 21:03

netsuite suitescript filter expression not producing the search results needed

1 Answers1