Sign up to receive SuiteScript examples and advice directly in your email inbox.

Formula Columns

Created: July 11, 2017

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 chooseformulanumericbecause your result is a number of days. The fact that your inputs are date values does not mean you want aformuladate.

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: