Page tree
Skip to end of metadata
Go to start of metadata

This page describes formula functionality in the Grid Custom Field.


Formula is a separate column type based on JavaScript. 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.

Formula column type

A Formula is a separate column type. It 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 expressions

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

Following operations are available: 

  • string concatenation;

  • mathematical operations with numbers: additionsubtractionmultiplicationdivision; 

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

  • conditions

  • userlist operation

 

Formula syntax 

You can create a formula using JavaScript. 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
if($(check) || $(role) === 'Team lead'){ return $(numb1) * $(numb2) } else { return $(numb1) / $(numb2) / 0 }
Using variables with date and moment.js library
firstDate = moment($(firsdt)); 
var secondDate = moment($(seconddt)); 
var difference = firstDate.diff(secondDate); 
return Math.abs(moment.duration(difference).asDays());




Examples

Below you can find formula examples with most common expressions.

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
var correctPercent = $(correct) * 100 / $(total)
var result = ''
if($(level) === 'low') {
 result = correctPercent > 60 ? 'Passed' : 'Fail'
} else if ($(level) === 'medium') {
 result = correctPercent > 70 ? 'Passed' : 'Fail'
} else {
 result = correctPercent > 80 ? 'Passed' : 'Fail'
}
return result
Date subtraction

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

Date subtraction using Moments.js formula expression
var firstDate = moment($(startDate));
var secondDate = moment($(endDate));
var difference = firstDate.diff(secondDate);
return Math.abs(moment.duration(difference).asDays());
Checkbox

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

Checkbox formula expression
if($(calculate)){
    return $(salary) + $(bonus)
} else {
    return $(salary)
}
Numbers

This example shows how to use JavaScript Math

Numbers using JS Math operations formula expression
var sum = $(a) + $(b)
if(sum > 1000) {
    return sum * $(coefficient)
} else {
    return sum / $(coefficient)
}
String concatenation

This example shows how to concatenate string column type. 

String Concatenation expression
return $(user) + ' ' + $(position)
  • No labels