July 11, 2017

Formula Columns

Let’s look at how to create Formula columns to add to this week’s ESS on Search Column names, summaries, and sorting.

Formula Columns let us add more dynamic expressions to our Search Results instead of just returning the raw value of a single field.

First, you need to decide which type of formula you need; this will become the name of your Column. The available types of formula fields are:

  • formulacurrency
  • formuladate
  • formuladatetime
  • formulanumeric
  • formulapercent
  • formulatext

Choose the type that best matches the *output* format of your formula, not the inputs. For example, if you want a formula that calculates the number of days between two dates, you would choose formulanumeric because your result is a number of days. The fact that your inputs are date values does not mean you want a formuladate.

Next, you simply need to write your formula expression as a String into the Column’s formula property. Here is an example for a Customer Search that concatenates the Customer’s First, Middle, and Last Name into a single Column. It utilizes the NVL function to ensure that an empty middle name doesn’t get inserted into the name as null.

    type: s.Type.CUSTOMER,
    filters: [ /* filters here */ ],
    columns: [{
        name: "formulatext",
        formula: "{firstname} || NVL({middlename}, '') || {lastname}"

For more on the syntax and available functions you have in Search Formulas, see the NetSuite Help articles:

  • “Using a Formula in Search Results”
  • “Search Formula Examples and Tips”
  • “SQL Expressions”
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Related posts

March 3, 2021

Does the physical location of a competent employee change the amount of value they can deliver for your organization? If it does, I’d be keen to hear your story. If it does not, why ...

Read More

March 2, 2021

Today a friend and former colleague of mine – who happens to now be in a development leadership role for a NetSuite partner – was lamenting the extreme difficulty of finding and hiring NetSuite ...

Read More

March 1, 2021

Software is never finished; clients are always changing and re-prioritizing. NetSuite is ever a moving target – always updating and evolving. Market forces are always shifting and shoving, forcing new demands on your business. ...

Read More