SuperSync Files - Advanced Formulas

Advanced Formulas Overview

Advanced Formulas is a powerful feature that extends the capabilities of NetSuite's Saved Search formulas. With this enhancement, you can traverse across multiple linked record relationships—going far beyond the single-level limitation in standard NetSuite formulas.

This feature enables what we call "search stacking" — a technique for chaining multiple saved searches together, each using the result of the previous step to fetch deeper related data.

This is especially useful when building complex reporting or automation logic that depends on deeply related data across multiple record types.

Standard Formula Limitations

NetSuite's native formula fields in Saved Searches can only reference one level of joined records. For example, from an Invoice record 

you can access:

customer.parent

You cannot however access deeper relationships like:

customer.parent.salesrep.location.id

This restriction makes it difficult to build searches that rely on multiple levels of related data.

Advanced Formulas Approach

Advanced Formulas overcomes this limitation by allowing you to utilize a chain of "stacked" searches, where each step is a separate search that feeds into the next.

Here is an example of an advanced formula starting from the "invoice" record. We return an ID field and then use that to find a record using a subsequent search. Here is how it would theoretically look to get from an invoice to the location of the sales rep assigned to the parent customer.

Start on Invoice:               get Customer ID
-- Run a Customer Search   ->   get Parent Customer ID
-- Run a Customer Search   ->   get Sales Rep ID
-- Run an Employee Search  ->   get Location ID

In this case we would not be able to jump directly to the location from the invoice. There are too many "hops" for one search. If we stack four separate searches, however, we are able to do this.

Advanced Formula Syntax

The syntax for Advanced Formulas builds on native NetSuite formulas by introducing the following tools:

  • Standard fields and formula values
  • Stacked Searches
  • Inline Search Filtering
  • Variables and Composite Formulas

Standard Fields and Formula Values

Just like a standard NetSuite search formula, combinations of fields and formula results can be used in advanced searches. Note that each of these expressions are treated as formulas in NetSuite and so field names must be contained within curly braces. All of the following are capabilities of standard NetSuite formulas. They are called out here specifically for illustrative purposes.

a) Plain Field Name

Use the field name directly. For list/record fields, this will return the display text or name of the record. For standard non list/record fields it will just return the value as-is. Note that {id} can be used to return the internal ID of the record. In all instances, following NetSuite's standard syntactical convention, field names must be wrapped in curly braces {}.

{customer}
{email}
{id}

b) Dot Reference to .id

Access the internal ID of a list/record field.

{customer.id}

c) One-Level Dot References

Access fields one level deep on related list/record fields. This is limited to expressions that would work with standard NetSuite searches. If a field is more than one linked record away it will likely not return.

{customer.companyname}

d) Use of Functions in Formulas

Use full NetSuite formula expressions.

replace({tranid}, 'INV', '')

e) Special Record Label Tokens

Two special tokens are available to reference the label of the current record type dynamically:

  • {record_label} — returns the singular display name of the current record type (e.g., "Invoice")
  • {record_label_plural} — returns the plural display name of the current record type (e.g., "Invoices")

These are replaced before formula evaluation and are wrapped in single quotes automatically, making them safe to use directly within SQL expressions.

Stacked Search Syntax

Stacked searches are built by telling the system to take one field or formula result and then use it as the ID to look up a record of another record type (search on another table). The syntax follows on after a field or formula result and uses a double dash, then the record type, then an arrow leading to the next field or formula.

<field or formula returning ID> -- <search record type> -> <field or formula expression>

Stacked Search Chains

Multiple chains of value expressions and search definitions can be chained, but the full chain always starts and ends with a search formula.

<search formula>  -- <search record type> -> <search formula>  
-- <search record type> -> <search formula>

This can also be written inline without line breaks — either format is valid. The system automatically handles line breaks and HTML formatting when processing formulas.

<search formula> -- <search record type> -> <search formula> -- <search record type> -> <search formula>

Example

Simple example starting from an invoice to get a customer's company name:

{customer.id} -- customer -> {companyname}

For a more complex example, starting on the Invoice record to get the location ID of the sales rep for the parent customer:

{customer.id}
-- customer -> {parent.id}
-- customer -> {salesrep.id}
-- employee -> {location.id}

Step-by-step:

  1. From the invoice, get the customer ID
  2. Use that ID to run a search on the customer and get the parent customer ID
  3. Use that ID to find a customer record and return its sales rep ID
  4. Use that employee ID to find an employee record and return its location ID

Field and Record Type Reference

  • Make sure each <record type> matches a valid NetSuite record type
  • Field names must match their internal IDs
  • Each step in the chain must return a value used in the next step's filter
  • If any prior step returns no value, null will be returned for the whole chain

Any natively supported methods of retrieving field values can be used with stacked searches. The examples below illustrate this with a field and a formula used with a stacked search.

This formula concatenates first and last names:

{customer.id} -- customer -> {firstname} || ' ' || {lastname}

This formula uses an NVL function to check if a custom sales customer field is blank and if so fall back to the standard customer ID, then uses that result to run a customer search and return the company name:

nvl({custbody_sales_customer.id}, {customer.id}) -- customer -> {companyname}

Inline Search Filtering

Some searches may return multiple rows—especially transaction records or when accessing fields on related sublists. In these cases, filters can be applied to narrow the result set.

To add a filter, wrap it in square brackets [ ] before the field name:

[{account.id} = 119] {item.id}

This example filters the results to only rows where account.id = 119.

Filters can also be used in stacked searches:

{createdfrom.id} -- transaction -> [{quantity} > 0] {item}

This filters the transaction to only rows where quantity is greater than zero before extracting the item.

Technical Handling of Filters

When a filter has been specified, it is applied as part of the formula column expression. The filter is wrapped using a CASE WHEN expression as follows:

CASE WHEN <filter expression> THEN <field expression> ELSE NULL END

This means the filter controls whether the field value is returned or null — it does not return 1 or 0. If the condition is not met, null is returned for that row, and the system will move to the next non-null result.

Variables and Composite Formulas

Composite formulas can be used to combine multiple stacked search results or combine more than one advanced formula together. They are handled by returning the result of one formula into a variable and then reusing it in an "outer" formula.

Need for Formula Variables

The need for composite formulas becomes apparent when trying to combine values from two separate stacked formulas into one. Starting from the invoice record, we could retrieve the sales rep's email from the source sales order transaction like this:

{createdfrom.salesrep.id} -- employee -> {email}

If we also wanted to check for a fallback email on the sales rep of the customer, we could do this:

{createdfrom.customer.id} -- customer -> {salesrep.email}

These values come from totally different search chains and the formulas in each chain run separately, so special syntax is needed to combine them into a composite formula.

Composite Formula and Variable Syntax

Formula results can be put into a reusable variable using the following syntax. Double square brackets [[ ]] are used to enclose the formula and variable declaration.

[[ <Variable Name>:<Return Type> <Formula> ]]

Variable naming rules:

  • Variable names must not contain any spaces or special characters
  • Names are alphanumeric with underscores only
  • Names are case-sensitive (capitalizing them helps with readability)

Return types:

  • TEXT — wraps the result in single quotes for use in SQL expressions
  • NUMBER — leaves the result as-is for numeric expressions
  • The return type is required and must always be specified

Note: If a null value is retrieved, it will remain null regardless of the return type specified — it will not be converted to an empty string.

Variables defined earlier in the formula can then be referenced using double curly braces {{ }}:

{{ <Variable Name> }}

Usage notes:

  • The system processes all variable declarations first in sequence, then evaluates the final return formula
  • The formula that returns a result must come after all variable declarations
  • Multiple variable declarations can be made
  • References to variables can be used inside other variable declarations (as long as the referenced variable was declared earlier)
  • Formulas can break across lines at any point — line breaks are treated as spaces

Examples

In this example, the sales rep email is looked up using a stacked search, stored in a variable, and then used in the final formula with a fallback:

[[ SALES_EMAIL:TEXT {createdfrom.salesrep.id} -- employee -> {email} ]]
{createdfrom.customer.id} -- customer -> nvl( {{ SALES_EMAIL }}, {salesrep.email} )

The same result can be written with two variable declarations and a simpler return formula:

[[ SALES_EMAIL:TEXT {createdfrom.salesrep.id} -- employee -> {email} ]]
[[ CUSTOMER_REP_EMAIL:TEXT {createdfrom.customer.id} -- customer -> {salesrep.email} ]]
nvl( {{ SALES_EMAIL }} , {{ CUSTOMER_REP_EMAIL }} )

The first variable can also be used inside the second variable declaration, since it is declared first:

[[ SALES_EMAIL:TEXT {createdfrom.salesrep.id} -- employee -> {email} ]]
[[ CUSTOMER_REP_EMAIL:TEXT {createdfrom.customer.id} -- customer -> nvl( {{ SALES_EMAIL }} , {salesrep.email} ) ]]
{{ CUSTOMER_REP_EMAIL }}

Technical Handling of Composite Formulas

  • Variable definitions are parsed out of the full formula before anything else
  • Variable definitions can be differentiated from variable references because references contain only a variable name with no spaces
  • Each formula — from each variable declaration and the final return formula — is run separately starting from the source record
  • Single quotes within variable values are automatically escaped to prevent SQL errors

Error Handling

If a formula encounters an issue at any step in the chain, the system will return an error result rather than a partial value. Common error results include:

  • Error: Invalid Expression — the formula syntax is not valid in NetSuite
  • Error: Field Not Found — a referenced field does not exist on the record type
  • No results found for search criteria — the search returned no matching rows

If any step in a stacked search chain returns no value, null is returned for the entire chain.

Was this article helpful?