Using NetSuite’s saved searches feature is one of the easiest and most effective ways to segment data and create custom reporting without requiring a technical background. Not only can you harness both standard and custom field values as search criteria and in search results, but you can also use formulas and SQL statements to include calculated values within your searches. In this post, I’ll share five of my favorite ways to use formulas to make your searches even more powerful – including tricks for generating dynamic links within searches, adding custom highlighting on individual text values, consolidating values into a single column, and more.
Embed links in search results.
By virtue of its single-data-source design, it is easy to navigate between related records within NetSuite. This is also true when looking at reports, which offer the ability to quickly drill into customers, employees, transactions, and other record types with one click. This same functionality can be built into saved searches, by using formulas to dynamically embed HTML links to the desired records. For example, if you want to embed a link to the item record on a search displaying open Sales Orders, use the following string in a Formula (Text) field:
'<a href="https://system.na2.netsuite.com/app/common/item/item.nl?id='||{item.internalid}||'" target="_blank">'||{item}||'</a>'
The double pipes (“||”) are used to concatenate the HTML for embedding a link with the unique item ID that NetSuite uses in order to generate the URL for the item record. The value in the Item field is used as the display text for the link. This same logic can be applied to other record types within NetSuite by navigating to the record type that will be linked to (i.e. a phone call or customer), copying the link, and then replacing the numerical internal ID with the field value for the internal ID of that record.
Display quantity awaiting fulfillment on partially-fulfilled orders.
For retailers who deal in physical goods, staying on top of back-orders is vital for maintaining customer satisfaction. Many companies will ship in-stock items ahead while awaiting replenishment of any back-ordered products, however getting visibility into these partially-shipped orders can be somewhat challenging with out-of-the-box reporting. By using a formula to calculate the number of units awaiting shipment, you can quickly generate a custom saved search that displays this value along with any other pertinent order information. To calculate the quantity remaining, use the following string in a Formula (Numeric) field:
{quantity}-nvl({quantityshiprecv},0)
The “nvl({quantityshiprecv},0)” value prevents errors that could occur when the field that is being referenced contains no value by replacing it with the specified value – in this case, “0”. This formula subtracts the number of units that have been fulfilled from the total quantity ordered to determine the number of units that are awaiting fulfillment.
By using this formula in the search criteria (with a value of “greater than 0”) and the search results, you can limit your data set to only those sales orders that contain a back-ordered product to create a time-saving report that can be added to the dashboards of your sales representatives.
Use CASE statements to add conditional text formatting.
One of the most effective ways to draw attention to important information is to use color-coding. NetSuite’s saved searches feature already has high-level highlighting capabilities built in, but certain use cases require more granularity and control over the way that search results are displayed. Using an SQL CASE statement allows you to specify how you would like your data to be displayed on a column-by-column basis. For example, if you would like to display the inventory quantity on an item search in green text if more than 20 units are available, in bold, yellow text if 11 – 19 units are available, larger, bold, red text if less than 10 units are available, and ‘Out of Stock’ if none are available, use the following string in a Formula (Text) field:
CASE WHEN {quantityavailable} > 19 THEN '<font color="#006400">'||{quantityavailable}||'</font>' WHEN {quantityavailable} > 10 THEN '<font color="#EEB422"><b>'||{quantityavailable}||'</b></font>' WHEN {quantityavailable} > 0 THEN '<font color="#BE2625" size="+1"><b>'||{quantityavailable}||'</b></font>' ELSE 'Out of Stock' END
This formula looks somewhat complex, but it is fairly simple once you break it down. Each “when” statement identifies one of the data sets (i.e. 20 or more units is entered as “> 19”), and the “then” statement that follows indicates how data that falls within that set should be displayed using basic HTML formatting. The double pipes (“||”) are used to concatenate the HTML code and NetSuite field values. This type of formatting makes spotting critical data easy, since values that need attention are called out with specialized formatting.
Embed links to create related records within search results.
Saved searches allow representatives to manage customer accounts more effectively – especially when they are included on their dashboards. Staying on top of back-ordered products, open support cases, and other critical issues related to customer management is simplified through the use of targeted saved searches that isolate the particular sets of criteria that you are looking to address. To make customer management even more efficient, you can use formulas to embed the links for creating estimates, sales orders, and other common records within the results of a customer search. Use a Formula (Text) field with each of the formulas below:
Email:
'<a href="https://system.na2.netsuite.com/app/crm/common/crmmessage.nl?entitytype=custjob&entity='||{internalid}||'&l=T&templatetype=EMAIL" target="_blank">Send Email</a>'
Estimate:
'<a href="https://system.na2.netsuite.com/app/accounting/transactions/estimate.nl?entity='||{internalid}||'&whence=" target="_blank">Create Estimate</a>'
Sales Order:
'<a href="https://system.na2.netsuite.com/app/accounting/transactions/salesord.nl?entity='||{internalid}||'&whence=" target="_blank">Create SO</a>'
Because these formulas are referencing the internal ID of the customer, all records are pre-loaded with the customer’s information when they open in a new window.
Include multiple field values in a single results column.
When creating complex searches with many columns in the results, optimizing the way that the data is displayed is an important consideration. By using a simple formula, you can quickly combine multiple field values into a single column. Use double pipes (“||”) to concatenate the desired field values into a text string. For example, to combine the phone, fax, and email fields from a customer record into one column, use the following string in a Formula (Text) field:
'P: '||{phone}||' F: '||{fax}||' E: '||{email}
Not only does this formula display the desired fields, but it also includes headers to break up the text and make it easier to read. This same logic can be applied to any other set of fields, and can even be combined with the techniques used to embed links or add conditional formatting.
Need additional help? Feel free to contact us today and we can help you create formulas to make your saved searches more effective!
By including formulas to calculate values within your saved searches, you enable users to view real-time data within NetSuite, instead of having to export the data into a spreadsheet for manual calculations. Adding text formulas to perform functions like text formatting and embedding links further improves user efficiency by giving them at-a-glance visibility into key data points and one-click access to related records. To make your searches even more robust, consider combining multiple techniques into a single formula – try displaying specific links based on CASE criteria, color-coding individual values when grouping multiple fields into a single column, or embedding more than one link into a column. When you become comfortable adding formulas into your saved searches, the possibilities are practically endless!
Need additional help? Feel free to contact us and we can walk you through how you can make your saved searches more effective with formulas.
