Adding a filter to a transaction (work order) search for multiple transaction ids

Question

I am building a search in a RESTlet. I am stuck on creating an elegant filter based upon transactionumber. I would like to filter it with an ANYOF operator, passing in an array of numbers for values, but that does not seem possible.

The only solution I have found is to pre-process the array into a string of numbers and use a conditional SQL function to test it:

search.createFilter({
    name : 'formulanumeric',
    formula :  'case when TO_NUMBER({transactionnumber}) in (' 
        + tranids.join(',')   // e.g.   in (741,744)  
        + ') then 1 else 0 end',
    operator : search.Operator.NOTEQUALTO,
    values : 0
});

Surely there is a better way. The above has a (slight) performance hit of converting the array to a string and I'm concerned about limitations of the formulanumeric field -- i.e. length of the formula string.

Avi Avi · Accepted Answer · 2019-01-02T11:35:07

transactionnumber is a text field, so you cannot use anyof operator as you are trying to use. You will have to use is with filterExpressions and joining them with or operator.

You could try something like below

transactionNumbersList = ['abc', 'xyz'];

var searchFilters = [];

transactionNumbersList.forEach(function (transactionNumber) {
  searchFilters.push(['transactionnumber', 'is', transactionNumber], 'or')
});

// pop trailing `or`
searchFilters.pop();

Adding a filter to a transaction (work order) search for multiple transaction ids

1 Answers