Formulas

Owned by svitlana (Deactivated)
Last updated: Jan 04, 2021 by Oleksandr Borozenets (Deactivated)
5 min read



You can use formulas in Table Grid Next Generation as a separate column or as an aggregation option for other column types. You can input formula inside embedded JavaScript Editor and get results on the grid view. The grid can process formula logic and display formula results.





In this section

Formula column type

You can add a formula as a separate column in a grid by selecting the Formula column type. The column uses JavaScript to create formulas. 

Formula expressions - editor, where you can input JavaScript formula code. It uses Ace Editor, which highlights the syntax.

Error types in the formula column type

  • #error - syntax error

  • #type - operation error (operation could not be executed using different column types)

Formula as an aggregation function

You can use formula as an aggregation operation in other columns.

It provides you the flexibility to operate grid data in different ways.

This feature is available for all column types, supported by the Table Grid Next Generation.



Formula expressions

You can create an expression with JavaScript code in this block.

Following operations are available: 

  • string concatenation;

  • mathematical operations with numbers: addition, subtraction, multiplication, division; 

  • date subtraction - it's possible to use Moments.js library;

  • conditions

  • userlist operation



Formula syntax 

You can create a formula using JavaScript. The formula can calculate both columns with values inside and other columns that have empty values.

To run the formula when columns have empty values check the checkbox in the column configuration as below.

If you want to make the column values searchable - enable the Index data checkbox.

The column ID is a variable, use it in the following format: $(yourColumnId).

Use return as a keyword to get the formula result.



Userlist in formula

Use the following example to display Userlist column type in the formula

Userlist in formula
return $(user).value





String concatenation
return $(firstStringColumnId) + ' ' + $(secondStringColumnId);



Numbers operations
//Numbers and Integer column type return $(numb1) * $(numb2) return $(numb1) / $(numb2) return $(numb1) + $(numb2) return $(numb1) - $(numb2) return Math.abs()



Checkbox condition



Using variables with date and moment.js library


Examples

Below you can find formula examples with most common expressions.

Retrieve option attribute values from Single Select List or Multi Select List columns

Retrieve a specific option attribute value

With this formula, you can get the value of an option attribute from a Single Select List or a Multi Select List column.

Request parameters:

id_1 — The identifier of the Single Select List or Multi Select List column.

name — Key of the option attribute.

Check out how to use this formula here:

Update grid data with Single Select List column

Retrieve all option attributes from a Single Select List column

For example, we have a Single Select List column with the following option attributes:

Enter the following formula to retrieve all option attributes from a Single Select column:

id_1 — The identifier of the Single Select List column.

This formula will retrieve the following values for the Single Select List column above:

If there is a single option attribute value, the formula will retrieve the value of that attribute. For example, for the label attribute, it will retrieve the admin value.

Retrieve all option attributes from a Multi Select List column

For example, we have a Multi Select List column with two list items: Admin and User.

Enter the following formula to retrieve all option attributes from a Multi Select column:

id_1 is the identifier of the Multi Select List column.

This formula will retrieve different values based on the number of list items you select.

If you select only Admin you will get the following results:

If you select both Admin and User you will get the following results:

Retrieve option attributes from a Multi Select List column with an index value

Options in Multi Select List columns are stored as array values. Each option has a unique index value assigned to it, starting from 0.

For example, we have a Multi Select List column with two static options: Admin and User. The Admin static option has a 0 index value, and the User static option has a 1 index value.

Use the following formula to retrieve all option attributes from the Admin static option:

In this example, you will see the following result in the Formula column:

If you remove all option attributes from this column, you will see the label value displayed. In this example, it will be displayed as:

Use this formula to retrieve the email option attribute with an index value, and it's Key.

In this example, you will see the following result in the Formula column:

Condition with numbers

This example shows how to provide exam results, based on the conditions specified in the formula. You can use conditions in the formula with all available column types.

Condition formula expression

Date subtraction

This example shows how to subtract Date. You can use Date, Time, DateTime column types.

Date subtraction using Moments.js formula expression

Checkbox

This example shows how to use checkbox column type in condition formula. 

Checkbox formula expression

Numbers

This example shows how to use JavaScript Math. 

Numbers using JS Math operations formula expression

String concatenation

This example shows how to concatenate a string column type. 

String Concatenation expression

See also

How to configure a Formula column