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)