Cadbury

Filter a NetSuite saved search by a calculated text category

Use one Formula Text criterion and available filter to switch between calculated HIGH and LOW categories. Check prefix matching and blank-input behavior.

Published 4 min read Watch the walkthrough

The quick answer

Use one Formula (Text) criterion that returns named categories, then add Formula (Text) under Available Filters with Show in Filter Region enabled. The available filter supplies the comparison value for that formula.

For example:

CASE WHEN {amount} >= 1000 THEN 'HIGH' ELSE 'LOW' END

Start with the criterion is HIGH. Set Default Text Field Filter to starts with and enter complete HIGH or LOW labels on the results page. This runtime filter uses prefix matching. Categories with overlapping prefixes can match together.

Before you start

You need permission to edit a Transaction saved search and view the relevant transactions. This walkthrough edits an illustrative existing search, Sales Order Amount Bands, using five fictional sales-order headers. Its Formula (Text) result column already displays Amount Band. Main Line Yes establishes this example’s header grain; other formula-category searches may use a different grain.

The search keeps Type = Sales Order and Main Line = Yes. The header amounts are 600, 1,600, 1,100, 400 and zero. HIGH means at least 1,000; LOW covers the other three. Your counts depend on your data and criteria.

This example uses one formula criterion and one available formula filter. It demonstrates a calculated category, not a numeric range or a way to count item lines.

01. Inspect the numeric filter

Open the existing results and click Edit this Search. The starting Formula (Numeric) criterion is:

CASE WHEN {amount} >= 1000 THEN 1 ELSE 0 END

It selects equal to 1, so the example initially returns the two HIGH headers. Open Available Filters and select Formula (Numeric). In this walkthrough, its Show in Filter Region cell is unavailable. Cancel that uncommitted row.

Formula (Numeric) is offered, but Show in Filter Region is unavailable in this walkthrough. Enlarge to inspect the selected row.

02. Use a text criterion

Return to Criteria. Select the old Formula (Numeric) criterion and click Remove. Add Formula (Text), then enter:

CASE WHEN {amount} >= 1000 THEN 'HIGH' ELSE 'LOW' END

Choose is, enter HIGH, and click Set. Keep Type Sales Order and Main Line Yes. Remove the old numeric criterion if it remains as a separate row; otherwise it would continue to exclude the LOW population.

The CASE returns quoted text. The initial criterion uses is HIGH.

03. Expose the text filter

Open Available Filters. Set Default Text Field Filter to starts with. Select Formula (Text), enable Show in Filter Region, enter Amount Band as the label, and click Add to commit the row. Confirm the added Formula (Text) row shows Yes under Show in Filter Region before running the search.

You do not enter a second formula expression on this tab. The filter uses the Formula (Text) criterion already defined.

Add one Formula (Text) filter, Show in Filter Region Yes and label Amount Band. Default Text Field Filter is starts with. Enlarge for all settings.

Click Save & Run to update the existing illustrative search. Its results now show Amount Band set to HIGH and the same two headers: SO8722 for 1,600 and SO8723 for 1,100.

In the illustrative search, Save & Run displays HIGH and returns SO8722 and SO8723. Enlarge to compare the input, total and header amounts.

04. Test HIGH, LOW and blank

Replace HIGH with LOW in Amount Band and press Tab to leave the input. In this walkthrough, that refreshes the results. The initial criterion used is HIGH; the runtime input uses starts with. Enter complete category labels and check for overlapping prefixes.

LOW supplies the new comparison value for this formula. It does not require each row to match HIGH and LOW together. Other criteria still restrict the search population.

The example now returns SO8721 for 600, SO8724 for 400 and SO8725 for zero.

In the illustrative search, LOW returns SO8721 for 600, SO8724 for 400 and SO8725 for zero. Enlarge to compare the full rows.

Clear Amount Band and press Tab. This configuration returns Total: 0. Blank is not an automatic all-rows selection.

In the illustrative search, blank returns no rows. It is not an all-rows selection. Enlarge to inspect the empty input and total.

Enter LOW again and press Tab. Confirm the same three headers return, including the zero amount. Check both categories and blank in your own search before sharing it.

In the illustrative search, LOW returns the same three fictional headers. Enlarge for the filter input and document numbers.

Common questions

Why use Formula Text instead of Formula Numeric?

In the verified UI used for this walkthrough, Formula (Numeric) is offered under Available Filters, but its Show in Filter Region control is unavailable. Formula (Text) has an enabled control in the verified search state. Check the controls available in your account.

Does LOW get added to the original HIGH criterion?

For the single-formula pattern shown, the runtime text supplies that formula criterion's comparison value. LOW does not require simultaneous HIGH and LOW matches. Other search criteria still apply.

Is this exact text matching?

The initial criterion uses is HIGH. The results-page input uses starts with because that is the default text matching selected in this example. Use complete labels and avoid overlapping prefixes when you need distinct categories.

Can I leave it blank to show all rows?

Blank returns no rows in this configuration. Do not assume it means all categories. Test the blank case with your own formula and search settings.

Does this establish how multiple formula filters map?

No. The walkthrough uses one Formula (Text) criterion and one Formula (Text) available filter. It establishes no positional mapping for several formulas of the same type.