Page tree
Skip to end of metadata
Go to start of metadata

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 

Error rendering macro 'contentbylabel'

parameters should not be empty