Improve Saved Searches with TO_CHAR Formatting

NetSuite is great when it comes to retrieving data from the system. But now that I have this data in my search, how can I make this more effective at conveying the idea that I am going for?

Short answer: Formatting.

Whether you are trying to isolate negative numbers and change the color of only those lines, or adding a currency symbol or percentage sign, we have you covered.

Using a Formula field in the results of your saved search will allow you to utilize the power of the “TO_CHAR” function which NetSuite supports.

Here are a couple examples of the formula and the purposes behind those formulas:

• • Use case: You have imported customers with zip codes and the East Coast customers (with zip codes starting with “0”) are not showing that leading zero.
  • Explanation: By adding the “0” in front of the “09999” the function tells the system to display leading zeros for that field.
  • Solution: To find these 4-digit zip codes in the first place, and then apply this you could use something like this –
    • CASE WHEN (LENGTH({zipcode}) = ‘4’) then TO_CHAR({zipcode}, ‘09999’) END
    • This formula would find only the 4-digit zip codes and apply the Leading Zero.

TO_CHAR zip code formula example (click to enlarge):

TO_CHAR zip code formula displayed results (click to enlarge):

Formula (Text) | TO_CHAR({amount}, ‘$999,999.00’) or TO_CHAR({amount}, “L999,999.00)

• • Use case: You need to include the “$” sign at the beginning of a numeric value.
  • Explanation: By adding the “$” to the beginning of “$999,999.00” you are inserting the “$” character into the value – Simple!
    • By adding the “L” to the beginning of the number, you will be inserting the “Local Currency” symbol into the value. This is great when your international branches will be using the same report!
  • Solution: Your CEO wants to be sure when looking at a report that it is impossible to miss which values are Currency, and which are not. This formula inserts the value making it very easy to find and validate. We are dealing with only US currency so we should use something like this –
    • Formula (Text) | TO_CHAR({amount}, ‘$999,999.00’)

TO_CHAR amount formula example (click to enlarge):

TO_CHAR amount formula example (click to enlarge):

Using TO_CHAR in combination with CASE to conditionally show values in a different color in a Formula (HTML)

• • Use Case: Your CFO wants the report to show negative values in a column in “red” font but keep the positive values black.
  • Explanation: By using Formula (HTML) you can modify the font or color used by the browser. Using the <span> tag allows you to enter the criteria for the condition, select the color when it meets that condition and when it doesn’t, and using the double pipes (“||”) allows you to concatenate values within the search. The formula is identifying if the value is negative or positive, changing the color of the negative values to “red” and keeping the positive values “black” and then adds a “%” sign to make it easily visible that this column is representing a percentage.
  • Solution:
    • Formula (HTML) | Summary type = “Maximum” | CASE WHEN (({budget}/{estimatedbudget) > 0) THEN '<span style="color:' ||   CASE WHEN ROUND((({budget}/{ estimatedbudget}) - (1 - {percentcomplete})) * 100, 0) < 0       THEN 'red' ELSE 'black' END || ';">' || TO_CHAR(ROUND((({budget}/{ estimatedbudget}) - (1 - {percentcomplete})) * 100, 0)) || '.0%' || '</span>' ELSE NULL END

HTML example (click to enlarge):

HTML display (click to enlarge):

Formatting your NetSuite searches effectively can transform raw data into meaningful insights, making reports not only more readable but also more impactful for decision-makers.

By utilizing the TO_CHAR function and combining it with formulas like CASE, you can create visually distinct and informative data displays tailored to your business needs. Whether it’s adding leading zeros to zip codes, incorporating currency symbols, or highlighting negative values in red, these formatting techniques ensure that the information you present is clear, consistent, and easily interpreted by stakeholders.

The power of NetSuite’s search functionality lies not just in retrieving data but in presenting it in a way that drives understanding and action.