# How to use formula fields in a NetSuite saved search > Source: https://netray.co/erp-help/netsuite-saved-search-formula-fields-guide > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## Short answer In the saved search Results tab, add a column, set Field to "Formula (Text)", "Formula (Numeric)", "Formula (Date)" or "Formula (Currency)", then type an Oracle SQL expression into the Formula box using curly braces around field IDs, e.g. {trandate} or {item.custitem_weight}. Formula fields can also go on the Criteria tab so you can filter on the calculated value itself. ## Kind how-to ## System netsuite ## Erp Oracle NetSuite ## Module Saved Search / Reporting ## Query how to use formula fields in NetSuite saved search ## Alternate Queries - netsuite saved search formula (text) field examples - netsuite saved search CASE WHEN formula - netsuite saved search formula date field - how to add a calculated column to a netsuite saved search ## Applies To NetSuite all current versions (2023.x-2026.x), any edition with saved search access. Formula fields use Oracle SQL syntax, not JavaScript. ## Steps ### Build a formula field in a saved search **Items:** - Open the saved search (Lists > Search > Saved Searches, or Reports > New Saved Search) and go to the Results tab. - In a new row, set Field to the matching Formula type: Formula (Text), Formula (Numeric), Formula (Date), Formula (Currency), or Formula (Percent). - Click the Formula field that appears and enter an Oracle SQL expression, referencing NetSuite fields in curly braces, e.g. CASE WHEN {amount} > 1000 THEN 'Large' ELSE 'Small' END. - For joined record fields, prefix with the join field ID, e.g. {customer.category} or {item.custitem_lead_time}. - Use Summary Type (Group, Sum, Count, Max) on the formula row if the search has any summary/grouped column elsewhere - NetSuite forces every column into summary mode once one column is summarized. - To filter on the calculated value, add the same formula expression on the Criteria tab as a Formula (Text)/(Numeric) filter with its own operator and value. - Test with Preview before saving; formula syntax errors show as a SuiteScript-style SQL error banner at the top of the results, not per row. - For dates, wrap arithmetic in TO_DATE/TO_CHAR as needed, e.g. CASE WHEN {trandate} < TO_DATE(SYSDATE) - 30 THEN 'Overdue' ELSE 'Current' END. ## Sections ### Formula type determines what functions are allowed **Body:** NetSuite validates formula expressions against the declared type. A Formula (Numeric) field that returns a string, or a Formula (Text) field wrapped in TO_NUMBER on a null, will throw a search error rather than a blank cell. Pick the type that matches the final output of the expression, not the type of the input fields. CASE WHEN...END is the most common pattern for bucketing values (aging buckets, size tiers, status labels). DECODE is a shorter equivalent for simple equality checks: DECODE({status}, 'A', 'Open', 'B', 'Closed', 'Other'). NVL({field}, 0) is the standard null-guard before doing arithmetic, since NULL + anything in Oracle SQL returns NULL, not the other operand. **Code:** -- Aging bucket on a transaction search CASE WHEN {daysoverdue} <= 0 THEN 'Current' WHEN {daysoverdue} BETWEEN 1 AND 30 THEN '1-30' WHEN {daysoverdue} BETWEEN 31 AND 60 THEN '31-60' ELSE '60+' END ### Joined fields and multi-level joins **Body:** Any field on a related record that appears as a join in the saved search's join dropdown can be referenced with dot notation inside the formula: {customer.terms}, {item.custitem_category}, {salesrep.email}. The join has to already be reachable from the record type the search is built on - if NetSuite does not offer it in the Field list, it usually is not reachable via a single join and needs a second saved search or a SuiteQL query instead. Custom fields keep their script ID inside the braces exactly as defined, including the custitem/custbody/custrecord prefix. Checkbox custom fields return 'T'/'F' in formulas, so a CASE WHEN {custbody_field} = 'T' pattern is required rather than treating it as a boolean. ### Formula summary rules and performance **Body:** Once any column in the search has a Summary Type set (Group, Sum, Count, Average), every other displayed column must also declare a summary type, including formula columns - usually Group for text/date bucket columns and Sum for numeric ones. This is the single most common reason a working formula search suddenly errors after someone adds a subtotal. Formula fields execute per row inside the search engine, not client side, so a search with several nested CASE expressions and string functions across a large result set can slow noticeably. For recurring heavy reporting, move the logic into a SuiteQL query (Analytics > SuiteQL or via N/query in a script) which pushes the same SQL to the database more efficiently and can be scheduled or cached. ### Common formula functions worth knowing **Body:** BUILTIN.DF({field}) returns the display value/name of a list or reference field instead of the internal ID - useful when {status} returns a raw code but you want the label. TO_CHAR({trandate}, 'YYYY-MM') buckets transactions by month for a pivot-style summary search. INSTR and SUBSTR let you parse text fields such as memo or external ID. ROUND, TRUNC and standard arithmetic operators work as expected on Formula (Numeric) columns. For percent-of-total style formulas, note NetSuite has no native window function support in saved search formulas - that kind of comparison against a grand total needs either a second search or a SuiteQL query with analytic functions. **Code:** - BUILTIN.DF({status}) TO_CHAR({trandate}, 'YYYY-MM') ## Pitfalls - Mismatched formula type and returned data type causes an opaque search error banner rather than a row-level failure. - Forgetting to add a Summary Type to a formula column once any other column in the search is summarized. - Using a custom checkbox field as if it returns a real boolean instead of the string 'T'/'F'. - Referencing a joined field that is not actually available from the base record, which NetSuite silently treats as null rather than erroring. - Heavy nested formulas on large datasets causing search timeouts - move that logic to SuiteQL instead. - Forgetting curly braces around field IDs, which NetSuite treats as literal text rather than a field reference. ## Ai Angle ERPray can be asked in plain language for the same output a formula field would produce - "show open sales orders more than 30 days old grouped by sales rep" - and it drafts the underlying saved search or SuiteQL, including the CASE WHEN aging bucket, against the actual NetSuite account's custom fields rather than a generic example. It is still worth a human review before saving, since custom field IDs and join paths vary by account. ## Faq ### Can I use a formula field as a saved search filter, not just a result column? **Answer:** Yes. Add the same formula expression to the Criteria tab with type Formula (Text)/(Numeric)/(Date), set an operator and value, and NetSuite filters rows on the calculated result rather than a raw field. ### Why does my formula field show blank instead of the expected value? **Answer:** Usually a null in one of the referenced fields propagating through arithmetic or concatenation. Wrap numeric fields in NVL({field}, 0) and text fields in NVL({field}, '') before combining them. ### Can saved search formulas call custom SuiteScript functions? **Answer:** No. Formula fields only support Oracle SQL functions available in the saved search engine (CASE, DECODE, NVL, TO_CHAR, BUILTIN.DF, string and math functions). Custom logic needs a scripted process or a SuiteQL query instead. ### What is the difference between a formula field and a SuiteQL query for reporting? **Answer:** Formula fields live inside the saved search UI and are limited to what the search engine exposes per record type. SuiteQL runs closer to raw SQL against the underlying tables, supports joins and functions saved search does not, and is better for heavy or recurring reports. ## Search terms netsuite saved search formula field, netsuite formula text field, netsuite saved search case when, netsuite saved search decode, netsuite formula numeric field, netsuite saved search summary type error, netsuite builtin.df, netsuite saved search join field, netsuite saved search aging bucket, netsuite formula field vs suiteql --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json