NetSuite Saved Search Formula Library

Business question: How do I classify a record into one of several text labels based on multiple conditions?

Record type: SuiteAnalytics Workbook dataset (pattern applies to any date/value classification)   Formula type: Formula, output type String

CASE 
  WHEN EXTRACT(Month FROM {trandate})= 12 THEN 'winter'  
  WHEN EXTRACT(Month FROM {trandate})= 6 THEN 'summer' 
  ELSE 'it was fall or spring' 
END

Set it up with: Use directly as a results field; add further WHEN branches for additional categories as needed.

Expect: A text label ('winter', 'summer', or 'it was fall or spring') per record.

Adapt for your account: Replace the EXTRACT(Month FROM {trandate}) condition and label strings with your own classification logic.

Watch for: This specific example only distinguishes two named months. A real seasonal classification needs a WHEN branch per month or a range check; treat it as the general CASE-branching pattern, not a ready-made season calendar.

Depends on: None.

Source: Conditional Evaluations Using CASE WHEN, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #case-when-season.

Business question: A field is sometimes blank. How do I substitute a default value, branch on whether it’s blank, or deliberately force a value to NULL so it’s excluded from a sum?

Record type: Any search   Formula type: Formula (Numeric) or (Text), depending on the field

NVL({quantity},'0')     : substitute 0 when quantity is null
NVL2({location},1,2)     : return 1 if location is set, else 2
COALESCE({quantitycommitted}, 0)     : first non-null value in the list
NULLIF({price}, 0)     : return NULL if price equals 0 (useful to exclude zero-price rows from an average)

Set it up with: Use whichever function matches the need directly in a results or criteria formula field.

Expect: Varies by function; see each line above.

Adapt for your account: Replace the example field IDs ({quantity}, {location}, {quantitycommitted}, {price}) with your own.

Watch for: NVL2 and COALESCE both handle nulls but are not interchangeable. NVL2 is a fixed two-branch conditional; COALESCE returns the first non-null value across any number of arguments.

Depends on: None.

Source: SQL Expressions, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #null-handling-reference.

Business question: How many days has this case/task/transaction been open, and how do I filter for records open longer than N days?

Record type: Case, Task, or any record with a start date field   Formula type: Formula (Numeric)

{today}-{startdate}

Set it up with: To filter instead of display: use the same expression as a Formula (Numeric) criteria field, e.g. ({today}-{startdate}) > 3, to return only records open more than 3 days.

Expect: Whole number of days between the start date and today.

Adapt for your account: Replace {startdate} with the actual start-date field ID for the record type you are searching (e.g. {createddate}, or a custom field ID).

Watch for: Returns a raw day count, not a business-day count; does not account for holidays or weekends.

Depends on: None beyond the field existing on the record type searched.

Source: Formulas in Searches, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #days-open-overdue-filter.

Business question: What day of the week does a date field on a custom record fall on?

Record type: Custom record with a date field   Formula type: Formula (Text)

TO_CHAR ({custrecordname_date}, 'DAY - DD Mon')

Set it up with: No special criteria configuration required; add as a results column.

Expect: Text such as 'MONDAY – 15 Jan'.

Adapt for your account: Replace {custrecordname_date} with your actual custom date field ID.

Watch for: Output capitalization/format follows the TO_CHAR format mask exactly as written; adjust the mask string for a different display format.

Depends on: None.

Source: Retrieving the Day of the Week for a Date on a Custom Record, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #day-of-week-custom-record.

Business question: How many days remain before a task’s due date?

Record type: Task   Formula type: Formula (Numeric)

ROUND({enddate}-{today})

Set it up with: Add as a results column on a Task search; no special criteria required.

Expect: Whole number of days remaining (negative if the due date has passed).

Adapt for your account: {enddate} is the Task record’s Due Date field ID as documented; no substitution needed for a standard Task search.

Watch for: Does not distinguish business days from calendar days.

Depends on: None.

Source: Calculating Days Remaining Until Task Complete, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #days-remaining-task.

Business question: How many days has a promotion or contract been active, including ones already canceled?

Record type: Custom record tracking a sale or contract with start/cancellation dates   Formula type: Formula (Numeric)

ABS({custom_field_startdate}-nvl({custom_field_cxldate},{today}))

Set it up with: Add as a results column; no special criteria required.

Expect: Whole number of days the record has been (or was) in effect.

Adapt for your account: Replace {custom_field_startdate} and {custom_field_cxldate} with your actual start-date and cancellation-date custom field IDs.

Watch for: If the cancellation-date field does not exist on your record, this formula errors. Confirm the field ID first.

Depends on: Requires the two named custom date fields to exist on the record.

Source: Calculating Days a Sale Is In Effect, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #days-sale-in-effect.

Business question: What is the first calendar day of the current month, for use as a period-start filter?

Record type: SuiteAnalytics Workbook dataset/search formula field (any record with dates)   Formula type: Formula, output type Date

TRUNC(LAST_DAY(CURRENT_DATE)-1, 'MONTH')

Set it up with: Use as a computed date value in criteria or results; typically compared against a transaction or record date field.

Expect: A date value equal to the 1st of the current month.

Adapt for your account: None. The formula is self-contained.

Watch for: Documented for SuiteAnalytics Workbook formula fields; verify the same output type option is available in your specific search/dataset builder before relying on it.

Depends on: None.

Source: Calculating Specific Dates, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #first-day-this-month.

Business question: What is the last calendar day of the current month, for a period-end filter?

Record type: SuiteAnalytics Workbook dataset/search formula field   Formula type: Formula, output type Date

LAST_DAY(CURRENT_DATE)

Set it up with: Use as a computed date value in criteria or results.

Expect: A date value equal to the last day of the current month.

Adapt for your account: None.

Watch for: Same Workbook-context note as First Day of This Month.

Depends on: None.

Source: Calculating Specific Dates, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #last-day-this-month.

Business question: What is the first calendar day of the prior month, for month-over-month comparisons?

Record type: SuiteAnalytics Workbook dataset/search formula field   Formula type: Formula, output type Date

TRUNC(LAST_DAY(ADD_MONTHS(CURRENT_DATE,-1)), 'MONTH')

Set it up with: Use as a computed date value in criteria or results.

Expect: A date value equal to the 1st of the previous month.

Adapt for your account: None.

Watch for: Same Workbook-context note as First Day of This Month.

Depends on: None.

Source: Calculating Specific Dates, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #first-day-last-month.

Business question: What is the last calendar day of the prior month?

Record type: SuiteAnalytics Workbook dataset/search formula field   Formula type: Formula, output type Date

LAST_DAY(ADD_MONTHS(CURRENT_DATE,-1))

Set it up with: Use as a computed date value in criteria or results.

Expect: A date value equal to the last day of the previous month.

Adapt for your account: None.

Watch for: Same Workbook-context note as First Day of This Month.

Depends on: None.

Source: Calculating Specific Dates, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #last-day-last-month.

Business question: What is the transaction total for the current fiscal year to date, when the fiscal year starts in July?

Record type: Transaction search   Formula type: Formula (Numeric), Summary Type: Sum

DECODE(TO_CHAR(ADD_MONTHS({trandate},6),'YYYY'), TO_CHAR(ADD_MONTHS({today},6),'YYYY'),{amount},0)

Set it up with: Check "Use Expressions" and set: Date is within this fiscal year to date OR Date is within last fiscal year to date.

Expect: Summed transaction amount for the current fiscal year to date; 0 for transactions outside that range.

Adapt for your account: None if your fiscal year genuinely starts in July; otherwise use the calendar-year variant or adjust the ADD_MONTHS offset to your fiscal start month.

Watch for: The 6-month offset is specific to a July fiscal-year start. Do not reuse it as-is for a different fiscal calendar.

Depends on: Company fiscal calendar must actually start in July for this exact offset to be correct.

Source: Comparing Summed Amounts Across Two Fiscal Years for Transactions, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #fiscal-ytd-july-current.

Business question: What was the same-period total for the prior fiscal year, for a year-over-year comparison?

Record type: Transaction search   Formula type: Formula (Numeric), Summary Type: Sum

DECODE(TO_CHAR(ADD_MONTHS({trandate},6),'YYYY'), TO_CHAR(ADD_MONTHS({today},-6),'YYYY'),{amount},0)

Set it up with: Same criteria as the current-year variant (Use Expressions: this fiscal year to date OR last fiscal year to date).

Expect: Summed transaction amount for the prior fiscal year, same period.

Adapt for your account: Adjust the offsets if your fiscal year does not start in July.

Watch for: Same fiscal-start assumption as the current-year variant.

Depends on: Company fiscal calendar must start in July.

Source: Comparing Summed Amounts Across Two Fiscal Years for Transactions, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #fiscal-ytd-july-prior.

Business question: What is the transaction total for the current year to date, when the fiscal year equals the calendar year?

Record type: Transaction search   Formula type: Formula (Numeric), Summary Type: Sum

DECODE(TO_CHAR({trandate},'YYYY'),TO_CHAR({today},'YYYY'),{amount},0)

Set it up with: Check "Use Expressions" and set: Date is within this fiscal year to date OR Date is within last fiscal year to date.

Expect: Summed transaction amount for the current calendar year to date.

Adapt for your account: None, for a true calendar-year fiscal calendar.

Watch for: Do not use if the company fiscal year does not equal the calendar year.

Depends on: Company fiscal calendar must equal the calendar year.

Source: Comparing Summed Amounts Across Two Fiscal Years for Transactions, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #fiscal-ytd-calendar-current.

Business question: What was the same-period total last calendar year?

Record type: Transaction search   Formula type: Formula (Numeric), Summary Type: Sum

DECODE(TO_CHAR({trandate},'YYYY'), TO_CHAR(ADD_MONTHS({today} ,-12),'YYYY'),{amount},0)

Set it up with: Same criteria as the current-year calendar variant.

Expect: Summed transaction amount for the same period last calendar year.

Adapt for your account: None, for a true calendar-year fiscal calendar.

Watch for: Do not use if the fiscal year does not equal the calendar year.

Depends on: Company fiscal calendar must equal the calendar year.

Source: Comparing Summed Amounts Across Two Fiscal Years for Transactions, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #fiscal-ytd-calendar-prior.

Business question: What was the transaction total from the start of last year through the equivalent day this year?

Record type: SuiteAnalytics Workbook dataset (transaction-based)   Formula type: Formula, output type Float

CASE WHEN (ADD_MONTHS({trandate}, 12) <= CURRENT_DATE) AND (EXTRACT(YEAR FROM {trandate}) + 1 = EXTRACT(YEAR FROM CURRENT_DATE)) THEN TO_NUMBER({foreigntotal}) END

Set it up with: Use directly as a results/summary field; no additional criteria required beyond the transaction filters you already have.

Expect: The transaction amount when it falls in last-year-to-date, otherwise NULL (excluded from sums).

Adapt for your account: Replace {foreigntotal} with {amountnet} for Sales (Ordered) or Sales (Invoiced) analytical record types, per Oracle’s own guidance.

Watch for: Documented for SuiteAnalytics Workbook, not confirmed identical in classic Saved Search builder.

Depends on: None beyond the transaction date and amount fields existing on the dataset.

Source: Calculating Amounts for Relative Date Ranges, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #amount-last-year-to-date.

Business question: What is the transaction total from the start of this year through today?

Record type: SuiteAnalytics Workbook dataset   Formula type: Formula, output type Float

CASE WHEN {trandate} <= CURRENT_DATE AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE) THEN TO_NUMBER({foreigntotal}) END

Set it up with: Use directly as a results/summary field.

Expect: The transaction amount when it falls in this-year-to-date, otherwise NULL.

Adapt for your account: Replace {foreigntotal} with {amountnet} for Sales analytical record types as needed.

Watch for: Workbook-documented context.

Depends on: None.

Source: Calculating Amounts for Relative Date Ranges, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #amount-this-year-to-date.

Business question: What is the transaction total for the entirety of this year?

Record type: SuiteAnalytics Workbook dataset   Formula type: Formula, output type Float

CASE WHEN EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE) THEN {foreigntotal} END

Set it up with: Use directly as a results/summary field.

Expect: The transaction amount when created this year, otherwise NULL.

Adapt for your account: Replace {foreigntotal} with {amountnet} for Sales analytical record types as needed.

Watch for: Workbook-documented context.

Depends on: None.

Source: Calculating Amounts for Relative Date Ranges, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #amount-this-year.

Business question: What is the transaction total for the entirety of last year?

Record type: SuiteAnalytics Workbook dataset   Formula type: Formula, output type Float

CASE WHEN EXTRACT(YEAR FROM {trandate}) + 1 = EXTRACT(YEAR FROM CURRENT_DATE) THEN TO_NUMBER({foreigntotal}) END

Set it up with: Use directly as a results/summary field.

Expect: The transaction amount when created last year, otherwise NULL.

Adapt for your account: Replace {foreigntotal} with {amountnet} for Sales analytical record types as needed.

Watch for: Workbook-documented context.

Depends on: None.

Source: Calculating Amounts for Relative Date Ranges, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #amount-last-year.

Business question: What is the transaction total for the current quarter so far?

Record type: SuiteAnalytics Workbook dataset   Formula type: Formula, output type Float

CASE WHEN TO_CHAR({trandate},'Q') = TO_CHAR(CURRENT_DATE,'Q') AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE) THEN TO_NUMBER({foreigntotal}) END

Set it up with: Use directly as a results/summary field.

Expect: The transaction amount when it falls in the current quarter, otherwise NULL.

Adapt for your account: Replace {foreigntotal} with {amountnet} for Sales analytical record types as needed.

Watch for: Uses calendar quarters (Q1=Jan-Mar); adjust if your fiscal quarters differ.

Depends on: None.

Source: Calculating Amounts for Relative Date Ranges, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #amount-this-quarter-to-date.

Business question: What was the transaction total for the previous full quarter, including the year-boundary case (Q4 to Q1)?

Record type: SuiteAnalytics Workbook dataset   Formula type: Formula, output type Float

CASE WHEN TO_NUMBER(TO_CHAR(CURRENT_DATE,'Q')) > 1 AND TO_NUMBER(TO_CHAR({trandate},'Q')) = TO_NUMBER(TO_CHAR(CURRENT_DATE,'Q'))-1 AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE) THEN TO_NUMBER({foreigntotal}) WHEN TO_NUMBER(TO_CHAR(CURRENT_DATE,'Q')) = 1 AND TO_NUMBER(TO_CHAR({trandate},'Q')) = 4 AND EXTRACT(YEAR FROM {trandate})+1 = EXTRACT(YEAR FROM CURRENT_DATE) THEN TO_NUMBER({foreigntotal}) END

Set it up with: Use directly as a results/summary field.

Expect: The transaction amount when it falls in the previous quarter (correctly handling the Q4-to-Q1 year rollover), otherwise NULL.

Adapt for your account: Replace {foreigntotal} with {amountnet} for Sales analytical record types as needed.

Watch for: Uses calendar quarters.

Depends on: None.

Source: Calculating Amounts for Relative Date Ranges, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #amount-last-quarter.

Business question: What is the transaction total for the current month so far?

Record type: SuiteAnalytics Workbook dataset   Formula type: Formula, output type Float

CASE WHEN {trandate} <= CURRENT_DATE AND EXTRACT(Month FROM {trandate}) = EXTRACT(Month FROM CURRENT_DATE) AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE) THEN TO_NUMBER({foreigntotal}) END

Set it up with: Use directly as a results/summary field.

Expect: The transaction amount when it falls in month-to-date, otherwise NULL.

Adapt for your account: Replace {foreigntotal} with {amountnet} for Sales analytical record types as needed.

Watch for: Workbook-documented context.

Depends on: None.

Source: Calculating Amounts for Relative Date Ranges, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #amount-this-month-to-date.

Business question: What was the transaction total for the equivalent period last month?

Record type: SuiteAnalytics Workbook dataset   Formula type: Formula, output type Float

CASE WHEN (ADD_MONTHS({trandate}, 1) <= CURRENT_DATE) AND EXTRACT(Month FROM ADD_MONTHS({trandate}, 1)) = EXTRACT(Month FROM CURRENT_DATE) AND EXTRACT(YEAR FROM ADD_MONTHS({trandate}, 1)) = EXTRACT(YEAR FROM CURRENT_DATE) THEN TO_NUMBER({foreigntotal}) END

Set it up with: Use directly as a results/summary field.

Expect: The transaction amount when it falls in last-month-to-date, otherwise NULL.

Adapt for your account: Replace {foreigntotal} with {amountnet} for Sales analytical record types as needed.

Watch for: Workbook-documented context.

Depends on: None.

Source: Calculating Amounts for Relative Date Ranges, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #amount-last-month-to-date.

Business question: Which standard aging bucket (Current, 1-30, 31-60, 61-90, 90+) does an open invoice or bill fall into?

Record type: Transaction search (Invoice or Vendor Bill)   Formula type: Formula (Text), using CASE

CASE WHEN ({today}-{duedate}) <= 0 THEN 'Current' WHEN ({today}-{duedate}) BETWEEN 1 AND 30 THEN '1-30' WHEN ({today}-{duedate}) BETWEEN 31 AND 60 THEN '31-60' WHEN ({today}-{duedate}) BETWEEN 61 AND 90 THEN '61-90' ELSE '90+' END

Set it up with: Filter to open (unpaid) transactions first (e.g. Status is not Paid In Full); add as a results column, optionally with Summary Type: Group to count/sum by bucket.

Expect: Text label of the bucket the transaction currently falls into.

Adapt for your account: Replace {duedate} with {duedate} (standard) or your own due-date field if customized; adjust the day boundaries if your company uses different aging intervals.

Watch for: This exact composite is not a named Oracle example. It combines the documented days-difference pattern with CASE syntax and the standard 1-30/31-60/61-90/90+ bucket boundaries used in NetSuite’s own A/R Aging Summary and A/P Aging Detail reports. Verify against your own Aging Reports preference (Setup > Accounting > Preferences > Accounting Preferences, Aging Reports Use: Due Date or Transaction Date) before relying on it, since that preference changes which date field is correct.

Depends on: Assumes standard {duedate} field; multi-book or custom aging preferences may require a different field.

Source: A/R Aging Summary Report, Oracle NetSuite Applications Suite (bucket definitions); composed with the documented days-difference and CASE patterns from Formulas in Searches, last checked against documentation 2026-08-28

Link directly to this formula with #ar-ap-aging-bucket.

Business question: How long did it take a sales order to move from creation to Pending Fulfillment (i.e., approval)?

Record type: Transaction search (Sales Order)   Formula type: Formula (Numeric)

{systemnotes.date}-{datecreated}

Set it up with: Type is Sales Order; Main Line is true; System Notes: Field is Document Status; System Notes: New Value is Pending Fulfillment.

Expect: Number of days (fractional if using time) between creation and the approval status change.

Adapt for your account: None for a standard Sales Order approval workflow; adjust the System Notes "New Value" filter if your approval flow uses a different status.

Watch for: Relies on System Notes being enabled/retained for status-field changes; very old records may have purged system notes.

Depends on: Sales Order approval workflow must actually transition through a Document Status change NetSuite logs in System Notes.

Source: Calculating Time Taken to Approve Orders, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #time-to-approve-order.

Business question: How do I show a clean, continuous line-item number (1, 2, 3…) in a transaction search, since NetSuite’s own line sequence numbers can have gaps?

Record type: Transaction search (line-level)   Formula type: Formula (Numeric)

RANK() OVER (PARTITION by {internalid} ORDER BY {linesequencenumber})

Set it up with: Set Main Line = No; filter out tax lines; add Item and Amount (Gross), not Amount, as results columns alongside this formula.

Expect: A continuous rank (1, 2, 3…) per transaction, ordered by the line’s actual sequence.

Adapt for your account: None. Uses standard internalid and linesequencenumber fields.

Watch for: Must use Amount (Gross) rather than Amount per Oracle’s own guidance for correct line-level results in this configuration.

Depends on: None beyond a line-level (Main Line = No) transaction search.

Source: Including Line Numbers in Transaction Searches, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #transaction-line-numbering.

Business question: How do I reference a field from a related record (e.g. a vendor’s credit limit) inside a search on a different record type (e.g. a contact)?

Record type: Any record with a defined join to another record type   Formula type: Any formula type (used as a field reference)

{vendor.creditlimit}

Set it up with: No special configuration is required. Joined fields appear at the end of the Field dropdown in the Formula popup, and NetSuite fills in the correct ID once you select one.

Expect: The related vendor record's credit limit value.

Adapt for your account: Replace vendor and creditlimit with the actual join name and field ID for your specific related-record relationship. The join name is not universal across record type pairs.

Watch for: Only works where NetSuite defines an actual join between the two record types; not every pair of record types has one.

Depends on: A defined join path between the searched record and the related record.

Source: Using Joined Search Field Values, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #joined-vendor-credit-limit.

Business question: What is the markup multiplier between an item’s cost and its base price, so I can spot inconsistent markup across similar items?

Record type: Item search   Formula type: Formula (Numeric)

ROUND({price}/nvl({cost},1),2)

Set it up with: No special criteria required; typically filtered to a specific item type or category to compare like-for-like items.

Expect: A rounded multiplier (e.g. 2.15 means price is 2.15x cost).

Adapt for your account: None. Uses standard price and cost fields.

Watch for: Defaults cost to 1 when null (via nvl), which prevents a divide-by-zero error but produces a misleadingly large multiplier for items with a genuinely blank cost. Review any item priced with no cost separately rather than trusting this multiplier at face value.

Depends on: None.

Source: Displaying Multiplier from Cost to Base Price for Items, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #cost-to-price-multiplier.

Business question: How do I show an elapsed-time duration in a readable "Days: X Hours: Y" format instead of a raw number?

Record type: Case or any record with a Duration-type field   Formula type: Formula (Text), output type String

CONCAT(CONCAT('Days: ',TO_NCHAR(FLOOR(TO_NUMBER(TO_NCHAR({timeelapsed}/24))))), CONCAT(' Hours: ',TO_NCHAR(MOD(TO_NUMBER(TO_NCHAR({timeelapsed})),24))))

Set it up with: Use directly as a results field.

Expect: A string such as "Days: 2 Hours: 5".

Adapt for your account: Replace {timeelapsed} with your actual Duration-type field ID.

Watch for: Assumes the underlying duration value represents hours before the /24 division. Confirm your field’s base unit before reusing this exact formula.

Depends on: Field must genuinely be a Duration type.

Source: Combining CONCAT and other Functions to Calculate String Values, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #duration-days-hours-format.

Business question: How do I zero-pad a line number, join two text fields together, or strip a prefix out of a text field?

Record type: Any search   Formula type: Formula (Text)

LPAD({line},3,'0')     : zero-pad a line number to 3 digits
CONCAT({number},CONCAT('_',{line}))     : join two fields with an underscore
REGEXP_REPLACE({name}, '^.*:', '')     : strip everything up to and including the last colon

Set it up with: Use directly as results fields.

Expect: Varies by function; see each line above.

Adapt for your account: Replace {line}, {number}, {name} with your own field IDs.

Watch for: REGEXP_REPLACE patterns are regular expressions. Test the exact pattern against your real data before relying on it broadly.

Depends on: None.

Source: SQL Expressions, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #string-utilities.

Business question: A duration field, such as time elapsed, won’t convert directly with TO_NUMBER. How do I get a usable number out of it?

Record type: Case or any record with a Duration-type field   Formula type: Formula (Numeric), output type Float

TO_NUMBER(TO_NCHAR({timeelapsed}))

Set it up with: Use directly as a results field; set output type to Float.

Expect: The elapsed time as a numeric value (hours), suitable for further math.

Adapt for your account: Replace {timeelapsed} with your actual Duration-type field ID.

Watch for: Duration fields must be cast through TO_NCHAR first. Passing a Duration field directly to TO_NUMBER fails.

Depends on: Field must genuinely be a Duration type.

Source: Calculating Duration Values with TO_NUMBER and TO_NCHAR, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #duration-to-number.

Business question: What is the percentage difference between two numeric values on a custom record (e.g. budget vs. actual)?

Record type: Custom record with two comparable numeric fields   Formula type: Formula (Percent), Summary Type: Group, Function: Round to Hundredths

ROUND ( ({custrecord_value1} / {custrecord_value2} - 1.00 ) * 100, 2)

Set it up with: Results subtab: Formula Type = Formula (Percent); Summary Type = Group; Function = Round to Hundredths.

Expect: A percentage value representing how much value1 differs from value2.

Adapt for your account: Replace {custrecord_value1} and {custrecord_value2} with your actual custom field IDs.

Watch for: Divides by {custrecord_value2} directly. Wrap it in nullif() (see the divide-by-zero pattern) if that field can be zero.

Depends on: Both custom fields must exist on the record.

Source: Displaying the Percentage Variance between Custom Record Values, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #percentage-variance.

Business question: Who most recently updated this record, when a record may have many system-note entries?

Record type: Any record with System Notes   Formula type: Formula (Text), Summary Type: Minimum

min({systemnotes.name}) keep (dense_rank last order by {systemnotes.date})

Set it up with: Summary Type on the formula field set to Minimum, as documented (the MIN wrapper is required for the KEEP clause to function as a summary field).

Expect: The name of the user associated with the most recent system-note entry.

Adapt for your account: None. Uses standard systemnotes join fields.

Watch for: Reflects the most recent System Notes entry, not necessarily every kind of field edit if system note logging is restricted for that field type.

Depends on: System Notes must be populated for the record type/field being tracked.

Source: Finding the Most Recent Record Updater, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #most-recent-record-updater.

Business question: Are there vendor bills that share the same vendor and vendor invoice number more than once, suggesting a possible duplicate entry?

Record type: Transaction search (Vendor Bill)   Formula type: Formula (Numeric), analytic function

COUNT(*) OVER (PARTITION BY {entity}, {tranid})

Set it up with: Type is Vendor Bill; Main Line is true; add this formula as a results column, then filter/sort results where the value is greater than 1.

Expect: A count of how many bills share the same vendor + reference number; values greater than 1 indicate a possible duplicate.

Adapt for your account: Replace {tranid} with the specific reference-number field your process actually uses for the vendor’s invoice number if it differs from the standard field.

Watch for: This exact composite is not a named Oracle example. It applies the documented `OVER (PARTITION BY …)` analytic-function syntax (used elsewhere in Oracle’s own line-numbering example) to a duplicate-flagging use case. A count greater than 1 is a candidate for manual review, not proof of a duplicate; legitimate vendors sometimes reuse similar reference formats.

Depends on: Assumes vendor + reference number is a meaningful duplicate signal for your AP process; adjust the partition fields if a different combination (e.g. + amount) is more appropriate.

Source: SQL Expressions, Oracle NetSuite Applications Suite (analytic/aggregate functions); composed with the documented PARTITION BY pattern from Including Line Numbers in Transaction Searches, last checked against documentation 2026-08-28

Link directly to this formula with #duplicate-bill-detection.

Business question: How do I get a transaction accounting line amount consolidated into a single reporting currency across subsidiaries?

Record type: Transaction Accounting Line (SuiteAnalytics Workbook dataset)   Formula type: Formula, output type Float (via TO_NUMBER)

TO_NUMBER({transactionlines.accountingimpact.netamount#currency_consolidated})

Set it up with: Use directly as a results field; applies only to amount fields from the transaction accounting line record type.

Expect: The net amount consolidated into the reporting currency.

Adapt for your account: None for this specific field; the #currency_consolidated suffix pattern generalizes to other transaction accounting line amount fields.

Watch for: TO_NUMBER() is required because the Formula Builder does not support a native Currency output type.

Depends on: Multi-subsidiary / OneWorld consolidation must be enabled.

Source: Currency Consolidation and Conversion Using Custom Formula Fields, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #currency-consolidation.

Business question: How do I convert a transaction amount field into a specified currency?

Record type: Transaction or transaction line record (SuiteAnalytics Workbook dataset)   Formula type: Formula, output type Float (via TO_NUMBER)

TO_NUMBER({foreignamountpaid#converted})

Set it up with: Use directly as a results field; applies only to amount fields from the transaction and transaction line record types.

Expect: The amount converted to the target currency.

Adapt for your account: The #converted suffix pattern generalizes to other transaction/transaction-line amount fields.

Watch for: TO_NUMBER() required; distinct from consolidation, which applies only to transaction accounting line fields.

Depends on: Multi-currency feature must be enabled.

Source: Currency Consolidation and Conversion Using Custom Formula Fields, Oracle NetSuite Applications Suite (SuiteAnalytics Workbook), last checked against documentation 2026-08-28

Link directly to this formula with #currency-conversion.

Business question: How do I stop a formula from erroring when the denominator can be zero?

Record type: Any search using division   Formula type: Any numeric formula pattern

Y/nullif(X,0)

Set it up with: Not applicable. This is a defensive pattern to wrap around any division, not a standalone search.

Expect: NULL instead of a divide-by-zero error when X is 0; the normal division result otherwise.

Adapt for your account: Replace Y and X with your actual numerator and denominator field IDs or sub-expressions.

Watch for: Produces NULL, not 0, when the denominator is 0. Decide whether that is the correct downstream behavior for your report.

Depends on: None.

Source: Avoiding Divide By Zero Errors, Oracle NetSuite Applications Suite, last checked against documentation 2026-08-28

Link directly to this formula with #divide-by-zero-avoidance.

Business question: Which customers have had no recorded activity in the last N months?

Record type: Customer search

Set it up with: Criteria: Summary Type Maximum, Field Activity Fields > Date, filter "not after" Relative "1 months ago". Results: Name (Group), Activity Fields > Date (Maximum).

Source: Creating a Search for Customers with No Recent Activity, Oracle NetSuite Applications Suite

Business question: What inventory was added today, by item and quantity?

Record type: Transaction search

Set it up with: Criteria: Date is today; Account is your inventory account; Posting is Yes; Formula (Numeric) {quantity} greater than 0. Results: Item (Group), Quantity (Sum).

Source: Creating a Daily Inventory Additions Search, Oracle NetSuite Applications Suite

Business question: Which transactions or items are tied to a specific warehouse bin, and what is on hand there?

Record type: Item search or Transaction search

Set it up with: Transaction variant. Criteria: Transaction Bin Number is not empty. Results: Date, Type, Transaction Number, Amount, Transaction Bin Number, Transaction Bin Quantity. Item variant. Criteria: Bin Number is not empty. Results: Name, Type, Base Price, Bin Number, Bin On Hand Count, Bin On Hand Available, Preferred Bin.

Source: Creating Saved Searches for Bin Numbers, Oracle NetSuite Applications Suite

Business question: How many customers does each sales rep own, or what is each rep’s total customer balance?

Record type: Customer search

Set it up with: Results/Columns: Sales Rep field, Summary Type Group; Customer Name field, Summary Type Count (customer count per rep), or Balance field, Summary Type Sum (total balance per rep) as an alternative to Count.

Source: Defining Summary Types to Roll Up Search Results, Oracle NetSuite Applications Suite