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
col.linetotal.type=number
col.linetotal.formula={weight} * {shipprice} * 1.10
col.linetotal.type=number
col.linetotal.formula=if( {shipaddress} =~ ["London", "Birmingham"]) { {weight} * {shipprice} * 1.10 } else {{weight} * {shipprice} * 1.50 }
String functions
# strings can be concatenated
#
col.label.type = string
col.label.formula = {company} + " " + {shipaddress} + " " + {contactperson} + " " + {weight} + "lbs"
col.label.type=string
col.label.formula=size({shipaddress})
# 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"}
col.label.type=string
col.label.formula= if( {shipaddress} !~ ["London", "Birmingham"]) {"taxi"} else {"courier"}
# 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}}
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