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.
