Formula - Examples

Introduction

This page provides some example functions which are supported by the table grid editor.

Assume you have setup a grid to track routes of deliveries - the grid contains

gd.columns = company, shipaddress, invoiceaddress, contactperson, weight, label, shipprice, distance, linetotal

Column names can be reused in formula's by using a column placeholder such as

{shipaddress}
{contactperson}
{shipprice}

Calculations

Plain arithmetic

col.linetotal.type=number
col.linetotal.formula={weight} * {shipprice} * 1.10

conditional

col.linetotal.type=number
col.linetotal.formula=if( {shipaddress} =~ ["London", "Birmingham"]) { {weight} * {shipprice} * 1.10 } else {{weight} * {shipprice} * 1.50 }

String functions

Concatenation

^{# strings can be concatenated}

^#

^{col.label.type = string}

^{col.label.formula = {company} + " " + {shipaddress} + " " + {contactperson} + " " + {weight} + "lbs"}

Length of a string

col.label.type=string
col.label.formula=size({shipaddress})

In or Match (=~)

# Column should contain 'taxi' when the address is London or Birmingham, else 'Courier'

col.label.type=string
col.label.formula= if( {shipaddress} =~ ["London", "Birmingham"]) {"taxi"} else {"courier"}

Not-In or Not-Match (!~)

col.label.type=string
col.label.formula= if( {shipaddress} !~ ["London", "Birmingham"]) {"taxi"} else {"courier"}

Empty String

# Column names can be reused in formulas (if no shipment address given, use the invoice address)

col.label.type=string
col.label.formula= if(empty({shipaddress})) {{invoiceaddress}} else {{shipaddress}}

Substring

col.label.type=string
col.label.formula= {shipaddress}.substring(1, 3)

Date functions

Use formulas to calculate differences between dates

Some more examples

You can also use dates:diff formula with different date formats.

For example, if the grid configuration of the Date column uses custom date format dd/mm/yy, you can add that custom date format into the dates:diff formula as an additional parameter.

For dd.M.yy (20.Oct.09) use the code as below

For dd-M-yy (10-Oct-2009) use the code as below

Grid configuration with formula date function and custom date formatting

Math functions

col.foo = Foo

col.foo.type = number

...

col.xyz = Math example

col.xyz.formula="signum: ``" + math:signum({foo}) + "``; round: ``" + math:round({foo}) + "`` ; ceil:" + math:ceil({foo})

...

Query functions (since v1.19.0)

Use query functions to perform SELECT SQL queries to your datasources

Get status of the issue by its number

Decide if you should work on specific issue depending if it has a specific component selected