Start a conversation

Calculate with grid fields

You can also calculate with grid field values. For example, you can calculate the sum of a row or column in your grid. This of course makes sense if you are using numerical fields, but can sometimes apply to dropdown fields as well.

Grid fields are stored as so-called JSON objects. If you look into your audit trail after entering values for a grid field, you will see how the grid field is stored. It looks something like this:

{"0":{"0":"8","1":"8"},"1":{"0":"8","1":"8"}}


In this case, the grid field consists of 2 rows, numbered 0 and 1 (outside the inner brackets), and two columns, also numbered 0 and 1 (inside the inner brackets). In the case above, the values 8 have been added for each cell.

Accessing values from the grid field

You will first need to 'parse' the grid field - this will always be your first step and you can use this piece of code for that:

var obj = JSON.parse('{grid_variable_name}');

Afterwards you can do calculations with the individual items.

To get the value entered in the first row and first column, use:

Object.values(obj[0])[0];


To get the value entered in the second row and first column, use:

Object.values(obj[1])[0];


To get the value entered in the second row and second column, use:

Object.values(obj[1])[1];


and so on. You can perform further calculations with these values, depending on what you are trying to achieve. 

Use of numbers in grid fields

Beware that this code above renders the objects in string (plain text). If you want to do mathematical operations with numbers, you will need to convert that text into numbers. For that you would use the parseInt() function. For example, in the case above if we want to get the sum of the values in the first row (in that case it is 16), we would use:

parseInt(Object.values(obj[0])[0]) + parseInt(Object.values(obj[0])[1]); 

You cannot calculate with a (half-)empty grid field - all the cells that are used in the calculation have to be filled in the grid to be able to execute the calculation

Validate grid field values

Calculation fields can be used to validate values in the grid field.

For example, this template validates the values entered in the first and second columns of the first row.

var obj = JSON.parse('{grid_variable_name}');

if (Object.values(obj[0])[0] == 1 && Object.values(obj[0])[1] == 1) {
    'The values are correct!';
} else {
    'The values are not correct!';
}

Use if/else logic to render various data validation messages.

Use if/else logic to output a number and add data validation to a calculation to display 'Warning' messages (like the one below) and other types of data validation messages. 

Choose files or drag and drop files
Was this article helpful?
Yes
No
  1. Tonya

  2. Posted
  3. Updated