Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


Excerpt
hiddentrue

Example of the functions, supported in the Table Grid Editor formulas.

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

Panel

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

Panel
titlePlain arithmetic


col.linetotal.type=number
col.linetotal.formula={weight} * {shipprice} * 1.10


Panel
titleconditional

col.linetotal.type=number
col.linetotal.formula=if( {shipaddress} =~ ["London", "Birmingham"]) { {weight} * {shipprice} * 1.10 } else {{weight} * {shipprice} * 1.50 }

String functions

Panel
titleConcatenation
# strings can be concatenated
#
 
col.label.type = string
col.label.formula = {company} + " " + {shipaddress} + "  " + {contactperson} + " " + {weight} + "lbs"


Panel
titleLength of a string


col.label.type=string
col.label.formula=size({shipaddress})


Panel
title 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"}


Panel
title Not-In or Not-Match (!~)


col.label.type=string
col.label.formula= if( {shipaddress} !~ ["London", "Birmingham"]) {"taxi"} else {"courier"}


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


Panel
title Substring


col.label.type=string
col.label.formula= {shipaddress}.substring(1, 3)

 

Date functions

Panel

Use formulas to calculate differences between dates

Code Block
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

Code Block
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.

Code Block
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

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

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

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

Grid configuration with formula date function and custom date formatting

Code Block
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

 

Panel
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)

Panel

Use query functions to perform SELECT SQL queries to your datasources

Get status of the issue by its number

Code Block
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

Code Block
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 

Content by Label
showLabelsfalse
spacesTGPD
showSpacefalse
cqllabel = "formula" and space = "TGPD"
labelsformula

Table of Contents
maxLevel2