/
Formula - Examples

Formula - Examples

Oct 03, 2019

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

gd.columns=_date, _date2, dateToNow, dateToDate2 gd.ds=jira gd.tablename=abc col.dateToNow=DateToNow col.dateToNow.type=string col.dateToNow.formula=dates:diff({_date}, dates:now()).hours() col.dateToDate2=DateToDate2 col.dateToDate2.type=string col.dateToDate2.formula=dates:diff({_date}, {_date2}).hours() col._date=date col._date.type=datetime col._date2=date2 col._date2.type=datetime



Some more examples

dates:diff({dateColumnName_1}, {dateColumnName_2}).minutes() dates:diff({dateColumnName_1}, {dateColumnName_2}).hours() dates:diff({dateColumnName_1}, {dateColumnName_2}).days() dates:diff({dateColumnName_1}, {dateColumnName_2}).months() dates:diff({dateColumnName_1}, {dateColumnName_2}).quarters() dates:diff(dates:now(), {dateColumnName_1}).minutes()

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.

dates:diff({dateColumnName_1}, {dateColumnName_2}, "dd/mm/yy").minutes() dates:diff({dateColumnName_1}, {dateColumnName_2}, "dd/mm/yy").hours() dates:diff({dateColumnName_1}, {dateColumnName_2}, "dd/mm/yy").days() dates:diff({dateColumnName_1}, {dateColumnName_2}, "dd/mm/yy").months() dates:diff({dateColumnName_1}, {dateColumnName_2}, "dd/mm/yy").quarters() dates:diff(dates:now(), {dateColumnName_1}, ).minutes()

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

dates:diff({dateColumnName_1}, {dateColumnName_2}, "dd.MMM.y").days()

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

dates:diff({dateColumnName_1}, {dateColumnName_2}, "dd-MMM-y").days()

Grid configuration with formula date function and custom date formatting

gd.columns=date_one,date_two,dates_diff gd.tablename=actions gd.ds=jira col.date_one=Date One col.date_one.type=date col.date_one.formatDate=d.M.y col.date_two=Date Two col.date_two.type=date col.date_two.formatDate=d.M.y col.dates_diff=Dates Diff col.dates_diff.formula=dates:diff({date_one},{date_two},"d.MMM.y").days()



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

col.issuenum=issuenum col.issuenum.type=string col.status=status col.status.type=string col.status.formula=queries:value('jira', 'select ist.pname from issuestatus ist inner join jiraissue ji on ji.issuestatus = ist.id and ji.issuenum = ' + {issuenum})

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

col.issuenum=issuenum col.issuenum.type=string   col.decision=decision col.decision.type=string col.decision.formula=if ('c2' =~ queries:list('jira', "select c.cname from component c inner join nodeassociation na on c.id = na.sink_node_id and na.sink_node_entity = 'Component' inner join jiraissue ji on ji.issuenum = " + {issuenum} + " and ji.id = na.source_node_id and na.source_node_entity = 'Issue'")) {'To process'} else {'Ignore'}



Related information