Search Formula Examples and Tips

Review the following items for hints about how other users have defined search formulas.

For other search examples, see Saved Search Examples.

For details about supported SQL functions, see SQL Expressions. For tables of NetSuite field IDs you can refer to SuiteScript Supported Records.

Important:

If you have examples or tips that you think other users may find helpful, please submit them to the NetSuite User Group's 100 Ways to Use Formula Fields thread.

Important:

Field values referenced in formula definitions may be translated based on a user’s language preference. For example, if a formula is created for a saved search using an English field value and the search is run by a user with a different language setting, the results of the search may vary. Consequently, you should be using internal field IDs when constructing formula definitions. This will ensure the intended results are displayed, regardless of a user’s language settings.

Including Line Numbers in Transaction Searches

To improve the readability of transaction line items, you can include line numbers in transaction search results. You can add the Line Sequence Number field on the Results subtab of the search definition page. This field represents the internal sequence number stored for each transaction line.

Note that line numbers are not guaranteed to be in order, even if results include all lines of a transaction. To make line numbers contiguous in search results, add a Formula(Numeric) field as a results field on the search, with the following formula expression:
```
              RANK() OVER (PARTITION by {internalid} ORDER BY {linesequencenumber}) 

            
```
Note that you can create a line number custom formula field and apply it to transaction forms to display line numbers on viewed and printed transactions. For instructions, see Creating a Formula Field to Display Transaction Line Numbers . If you are using this type of field and you also want to include the line number in search results, set up the search as follows to ensure that search results match viewed and printed transaction items:
- Set a criteria of Main Line =No (false).
- Filter out transaction line items related to taxes.
- Add the Item field as a results field.
- Add the Amount (Gross) field as a results field; do not use the Amount field.
- Add a Formula(Numeric) field as a results field, with the following formula expression: RANK() OVER (PARTITION by {internalid} ORDER BY {linesequencenumber})

Referencing Field IDs

You can reference the ID values for List/Record type fields in search formulas. To include a field's ID in a formula, use the format {field_name.ID}.

Avoiding Divide By Zero Errors

If you use division in a formula, please use Y/nullif(X,0) in place of Y/X.

Using Joined Search Field Values

You can use values from related records in your search criteria and results by defining joined search fields in your formula definitions. Joined search fields are specified using dot notation {record.field} where the related record is specified followed by the field on that record. For example, to specify the credit limit field on a vendor record associated with a contact, enter {vendor.creditlimit}.
Also, note that you can select a field to be included in a formula expression from the Formula popup window's Field dropdown list. Join fields appear at the end of the dropdown list. When you select from the popup window's dropdown lists, the system puts the correct ID in the formula.

Retrieving the Day of the Week for a Date on a Custom Record

Select Formula (Text) and enter the following formula:

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

Displaying the Percentage Variance between Custom Record Values

On the Results subtab, select Formula (Percent), a summary type of Group, a Function of Round to Hundredths, and (if you want) a custom label such as Average. Enter the following formula:

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

Calculating Days Remaining Until Task Complete

You can calculate the number of days remaining for a task to be completed based on the value entered in the Due Date field. Select Formula (Numeric) and enter the following formula:

ROUND({enddate}-{today})

Displaying Multiplier from Cost to Base Price for Items

When you review a list of the same model items, you can ensure your markup is similar for all of them. Select Formula (Numeric) and enter the following formula:

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

Comparing Summed Amounts Across Two Fiscal Years for Transactions

On the Criteria subtab, check the Use Expressions box and enter the following on two separate lines:

(Date is within this fiscal year to date OR Date is within last fiscal year to date).

On the Results subtab, enter the following formulas:

If the fiscal year starts in July:

For current year to date, Formula (Numeric), SUMMARY TYPE =Sum:

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

For previous year to date, Formula (Numeric), SUMMARY TYPE =Sum:

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

If the fiscal year is the calendar year:
- For current year to date, Formula (Numeric), SUMMARY TYPE =Sum:
```
                      DECODE(TO_CHAR({trandate},'YYYY'),TO_CHAR({today},'YYYY'),{amount},0) 

                    
```
- For previous year to date, Formula (Numeric), SUMMARY TYPE =Sum:

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

NOTES:
- ADD_MONTHS compensates calendar year for current fiscal year (6) and previous fiscal year (-6) if fiscal year begins in July, and compensates for previous year (-12) if fiscal year is the same as calendar year.
- TO_CHAR extracts the (fiscal) years.
- DECODE(value1, value2, {amount}, 0) compares the transaction date's fiscal year value (value1) to the current or previous fiscal year (value2). If equal, use amount; otherwise zero it out.

Finding the Most Recent Record Updater

You can use the dense_rank function in a formula, to order records by values in a specified field and assign each record a consecutive numerical ranking from 1 to n, where n is the number of records returned. You can use the keep (dense_rank) function to return only the first or last ranking record as ordered by values in a specified field.

For example, to return the name of the user who most recently updated a document, do a document search, and create a Formula(Text) field with a Summary Type of Minimum and a formula like the following: min({systemnotes.name}) keep (dense_rank last order by {systemnotes.date})

Calculating Days a Sale Is In Effect

You can use a formula to calculate the days a sale is in effect up to the date it is canceled, like a contract age. If there is no cancellation date, substitute today's date. Select Formula (Numeric) and enter the following formula:

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

You can use a similar formula to calculate the days remaining on a contract based on the end date field.

Referencing System Notes Records

You can reference System Notes fields in numeric and text formulas using the format: {systemnotes.field_name}. Also, note that you can select a field to be included in a formula expression from the Formula popup window's Field dropdown list. Join fields appear at the end of the dropdown list. When you select from the dropdown lists, the system puts the correct ID in the formula.

Calculating Time Taken to Approve Orders

Do a Transaction search, with filters of Type is Sales Order, Main Line is true, System Notes : Field is Document Status, and System Notes : New Value is Pending Fulfillment.
Add a Formula(Numeric) results column with the formula {systemnotes.date}-{datecreated}. You can add a Custom Label that indicates this calculation is the time taken to approve each order.