How To Use FORMULA In Filter Expression – SuiteScript 2.0


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:

// 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:

// 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.

Ryan

Author of scripteverything.com, Ryan has been dabbling in code since the late '90s when he cut his teeth by exploring VBA in Excel when trying to do something more. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP. When he is not behind a screen, Ryan enjoys a good bush walk with the family during the cooler months, and going with them to the beach during the warmer months.

Recent Posts