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”