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.

s.create({
    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

January 7, 2021

Anything I wrote today about business would both feel and be inadequate. I simply can’t arrive at the words to express how I feel in this moment. So instead, here are a few helpful ...

Read More

December 20, 2020

2020 was a hundred swift kicks to the shorts and one giant kick in the ass. If you’ve lost people you love, if you’ve gotten sick or hurt, if you’ve lost your job, if ...

Read More

December 16, 2020

“I’ve been meaning to learn SuiteScript, but it’s hard to find the time …”“We’re slammed, and I just don’t have the time …”“I haven’t made the time …” All are variations of a theme ...

Read More

December 14, 2020

Last week we looked at mimicking the functionality of a REST endpoint with its own module (e.g. retrieving a User, or a list of Users, refreshing a token, etc). For complex integrations that rely on a large ...

Read More