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}}

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)