Custom columns: JavaScript computing


#1

This functionality requires you to have basic JavaScript knowledge.

Import formula to JavaScript

This button will take your pre-existing formula (from the Formula tab) and translate it to functional JavaScript, as long as the formula does not contain any errors.

Compute

This function lets you create the values for the column grid. It is called once for each cell and has three arguments:

  • index: The current row index.

  • row: An array of the values for the current row. Each column has an unique identifier you must use to get the desired value. For example, you can get the request number of the current row by using row['requestNumber']. You can also use the tool located on the upper-right corner of the textarea.

  • records: A two-dimensional array of all the values contained in the grid. To use it, you must first get a specific row, then a specific column. For example, records[0]['requestNumber'] would get the request number of the first line in the grid.

Computing a column based on another computed column

You can base your computed function on the values returned by another computed column you previously declared. This is useful if you need to show multiple steps in a calculation. However, it’s important to remember that the new column you declare must always be located after (to the right of) the column from which it gets its data. If you reorder your column and place the new one to the left of the old one, the new column will be able to access the values it needs when the grid loads.

Debugging

Because you develop your functions directly in the browser, you must remain careful as it might crash if there is an error in your function. The vast majority of possible mistakes will be caught by the browser and you’ll be given a message describing the error or problem. Some errors might not be caught by the browser, such as an infinite loop that might force you to close your browser. For this reason, we highly recommend saving your view prior to editing a function. It’s also a good idea to copy your function in a text editor before running it.

Examples of compute functions

Basic operations

Basic arithmetic operations can be performed with JavaScript using symbols such as +, -, *, /, (, and ). For example, if you want to multiply a quantity by a price to get the total, you would write:

var total = row['quantity'] * row['price']; return total;

In the above example, we first declared a variable (with the keyword var) called total. We then set the value of this variable to the result of the quantity multiplied by the price. Finally, we return the variable value to the grid (with the keyword return).

If you need more advanced mathematical functions, you can use the Math library which provides you with functions like floor(), round(), ceil(), random(), sqrt(), etc. In the above example, if we wanted to round the results, here is what we would have done:

var total = Math.round(row['quantity'] * row['price']); return total;

Date operation

The following is an example of a function that returns the number of days between the open and closed dates. In that example, we first get the difference between the two dates. However, the result will be in milliseconds, we we need to divide it by 1000 * 60 * 60 * 24 to get the result in days.

var diffInMilliseconds = row['closed'].getTime() - row['created'].getTime(); return Math.floor(diffInMilliseconds / 86400000);

String manipulation

In this example, we add text to the returned value:

return "Request #" + row['requestNumber'];

In this other example, we transform this value from a string to a numerical value (to improve sorting, the display and to allow for the addition of a computer footer):

return Number("99.9");

Resources

For information on custom display functions in JavaScript, see the Custom columns: JavaScript custom display functions topic.

For information on formulas and custom dates and times, see the Custom columns: Formulas and custom dates/times topic.