I had recently had to use a FORMULA type in a SuiteScript 2.0 search and found it as a very useful filter for list values.

To use FORMULA types in a filter expression, such as, FORMULATEXT, FORMULAPERCENT, FORMULANUMERIC, FORMULACURRENCY or FORMULADATETIME just prefix the type of FORMULA at the beginning of your filter expression and then write your formula. Use the operator and value as you normally would with any other filter expression.

Let’s assume I have a field on my record which is a List field, and for ease of this example, let’s further assume the List field has the values YES and NO. If I wanted to search using the filter expression in my SuiteScript 2.0 code based on the value of the List field then here’s how I created the filter:

1
2
3
4
5
6
7
8

// check the custom record is active
let f = [
    ['isinactive', 'IS', 'F']
];
// here's the FORMULA expression based on list field's value:
const fx = "FORMULANUMERIC: CASE WHEN {custrecord_list_field_name} = 'YES' THEN 1 ELSE 0 END";
// here's how we create the filter:
f.push('and', [fx, 'EQUALTO', 1]);

You can further refine the FORMULA expression in the example above by making the value more dynamic, just make sure you wrap the variable in quotes otherwise it will not evaluate as a string. For example, if the value of YES in the expression above was changed to a variable we could use Javascript’s new template strings as so:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

// sometimes the list value may be dynamic
const someVariable = 'YES';
// check the custom record is active
let f = [
    ['isinactive', 'IS', 'F']
];
// here's the FORMULA expression based on list field's value and dynamic variable:
const fx = `FORMULANUMERIC: CASE WHEN {custrecord_list_field_name} = '${someVariable}' THEN 1 ELSE 0 END`;
// here's how we create the filter:
f.push('and', [fx, 'EQUALTO', 1]);

As you can see the filter expression in SuiteScript 2.0 can still be used with any formulas you may be using, you just need to prefix your formula with the appropriate FORMULA type, along with a colon :, and then you can write your formula expression. All other operators and values remain the same.

Using this can certainly help when filtering on List field types where the value of the List field is known and needs to be filtered.