Reducing Formulas in FSM Searches
This topic is intended for administrators and developers who reduce search formulas and preserve the intended results.
Measuring actual search performance in the account is the best indicator of whether a search needs optimization and whether a replacement improves it. Formula use alone doesn't establish a performance problem, and configuration or source review cannot reliably identify every search that may be slow. Compare performance using representative data and user actions, and confirm that the required results are preserved.
Search formulas, including formulatext, formulanumeric, formuladate, and formuladatetime, can slow search execution. Prefer standard NetSuite field or joined-field filters, using template tags to supply values from the current context. Add JavaScript inside a template tag only when additional logic is required and a search formula would otherwise be needed. Keep that JavaScript as simple as possible.
For example, standard filters can compare a quote's project and case IDs with values supplied by ${ event.job } and ${ event.supportcase }. Simple fallback logic can handle missing IDs before the search runs. NetSuite performs the record filtering. For template-tag syntax and evaluation, see FSM Expressions and Template Tags.
This approach applies when the required inputs are already available in the template context. A template tag doesn't automatically have the field values of every record being searched. Retain a formula where its calculation cannot be replaced without changing the returned records or values. For result mappings, use direct or joined field mappings that provide the required value.
This topic includes the following sections:
Selecting Quotes for the Current Job
The standard Quote resource searches NetSuite estimate records. Its filters include quotes linked directly to the current CRM Task or Project Task, and quotes whose project and case both match the current event. The project-and-case condition uses this formula filter:
[
"formulatext:case when ({job.internalid} = '${ event.job }') and {custbody_nx_case.internalid} = '${ event.supportcase }' then 'T' else 'F' end",
"is",
"T"
]
The formula also avoids passing missing project or case IDs to standard filters. A replacement must preserve that behavior and the direct task links.
The following custom configuration illustrates an alternative for routes/mobile.quote.article.filters. It uses ordinary search conditions and short template tags to supply the IDs:
{
"routes/mobile": {
"quote": {
"article": {
"record": "estimate",
"filters": [
["custbody_nx_task", "is", "${ event.internalid }"],
"or",
["custbody_nx_projecttask", "is", "${ event.internalid }"],
"or",
[
["job.internalid", "anyof", "${ event.job || 0 }"],
"and",
["custbody_nx_case", "anyof", "${ event.supportcase || 0 }"]
]
]
}
}
}
}
The example preserves or changes the following aspects of the search:
-
Quotes linked directly to the current CRM Task or Project Task remain included.
-
When both project and case IDs are available, the additional branch requires both to match. It doesn't select every quote for either one separately.
-
The case condition reads the stored
custbody_nx_casefield directly, rather than joining to the case record to read its internal ID. The project condition retainsjob.internalid, the transaction search's project join. -
When a project or case ID is missing or blank,
|| 0supplies a nonmatching ID. That project-and-case branch cannot match. The direct task conditions remain available. Don't substitute@NONE@: it can match records with an empty link. -
The
record: "estimate"setting restricts the search to estimate records, so a secondrecordtypecondition is unnecessary. -
The example replaces the filter formula. Formula mappings elsewhere in the Quote resource remain in use.
This example shows an alternative configuration. It doesn't describe a change to the standard Quote search. Custom filter arrays replace the inherited array. Before using the example, review the combined configuration and preserve any additional restrictions required by the account.
Checking a Formula Replacement
A replacement must preserve the required result set. Use selective standard NetSuite filters and template tags. Retrieving broader results and filtering the results afterward in JavaScript can increase retrieval time, payload size, and the risk of search limits.
Measured search performance is the best indicator of improvement. Replacing a formula doesn't guarantee better performance. For measurement and collection methods, see FSM Performance and Data Collection.