While standard saved search columns are perfect for displaying stored data, their true power is unlocked when you add formula fields to create calculated columns. These on-the-fly computations allow you to display derived metrics like profit margins, date differences, conditional labels, and concatenated text—information that doesn't exist in a standard field. This guide provides a fact-checked and expanded overview of using formula fields in your saved search results.

Understanding Formula Field Types

When adding a calculated column, you must first choose the correct formula type based on the kind of data you want to produce. Each type has a specific purpose and supports different functions.

Common Formula Patterns and Verified Examples

The following patterns represent common business requirements and have been verified for accuracy against official NetSuite SQL function documentation.

Financial Calculations

Gross Margin Amount

Calculates the difference between the transaction amount and the estimated cost.

{amount} - NVL({costestimate}, 0)

Gross Margin Percentage

Calculates the gross margin as a percentage of the total amount. It includes `NULLIF` to prevent division-by-zero errors and `ROUND` to format the output.

ROUND(({amount} - NVL({costestimate}, 0)) / NULLIF({amount}, 0) * 100, 2)

Date Calculations

Days Since Transaction

Calculates the number of days that have elapsed since the transaction date.

TRUNC(SYSDATE) - {trandate}

Due Date Plus 30 Days

Calculates a new date that is 30 days after the original due date.

{duedate} + 30

Conditional Logic with `CASE`

Aging Bucket Labels

Assigns a text label based on the value of the `daysoverdue` field.

CASE WHEN {daysoverdue} <= 0 THEN 'Current' WHEN {daysoverdue} <= 30 THEN '1-30 Days' WHEN {daysoverdue} <= 60 THEN '31-60 Days' ELSE 'Over 60 Days' END

High-Value Transaction Flag

Creates a checkbox that is checked if the transaction amount exceeds $10,000.

-- Use this in a Formula (Checkbox) fieldCASE WHEN {amount} > 10000 THEN 'T' ELSE 'F' END

Text Manipulation

Full Name Concatenation

Combines the first and last name fields into a single column.

{firstname} || ' ' || {lastname}

Displaying a Default Value for Empty Fields

Shows the text 'Not Specified' if a custom field is empty, improving report readability.

NVL({custbody_custom_field}, 'Not Specified')

Implementation and Best Practices

1. Navigate to the Results Tab: Edit your saved search and go to the Results subtab.

2. Add a New Column: In an empty column, select the appropriate formula type from the Field dropdown (e.g., Formula (Numeric)).

3. Enter the Formula: In the Formula popup window, enter your SQL expression.

4. Set a Custom Label: This is critical for usability. Enter a clear, descriptive name for your column in the Custom Label field.

5. Save and Preview: Save the search and run it to verify that your formula is working as expected.

Critical Best Practice: Handling Nulls

A NULL (empty) value in a calculation will almost always result in a NULL output. This can lead to unexpected blanks in your search results. Always wrap fields that might be empty in the NVL() function to provide a default value.

• For numeric fields: NVL({costestimate}, 0)

• For text fields: NVL({memo}, 'N/A')

Debugging Tips

• Check Field IDs: Ensure your field IDs are spelled correctly and enclosed in curly braces {}. Field IDs are case-sensitive.

• Build Incrementally: Don't write a complex formula all at once. Start with a simple piece, run the search to confirm it works, and then gradually add more logic.

Verify Data Types: A common source of errors is trying to perform a mathematical calculation on a text string. Ensure your functions and operators are appropriate for the data types of the fields you are using.