{"type":"doc","content":[{"type":"layoutSection","content":[{"type":"layoutColumn","attrs":{"width":66.66},"content":[{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"bodiedExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"excerpt","parameters":{"macroParams":{"hidden":{"value":"true"},"atlassian-macro-output-type":{"value":"INLINE"}},"macroMetadata":{"macroId":{"value":"70a6c747-c62e-45a5-bae2-6c905132aaee"},"schemaVersion":{"value":"1"},"title":"Excerpt"}}},"content":[{"type":"paragraph","content":[{"text":"Example of the functions, supported in the Table Grid Editor formulas.","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Introduction","type":"text"}]},{"type":"paragraph","content":[{"text":"This page provides some example functions which are supported by the table grid editor.","type":"text"}]},{"type":"paragraph","content":[{"text":"Assume you have setup a grid to track routes of deliveries - the grid contains","type":"text"}]},{"type":"panel","attrs":{"panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"gd.columns = company, shipaddress, invoiceaddress, contactperson, weight, label, shipprice, distance, linetotal","type":"text"}]}]},{"type":"paragraph","content":[{"text":"Column names can be reused in formula's by using a column placeholder such as ","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"{shipaddress}","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"{contactperson}","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"{shipprice}","type":"text"}]}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Calculations","type":"text"}]},{"type":"bodiedExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"panel","parameters":{"macroParams":{"title":{"value":"Plain arithmetic"}},"macroMetadata":{"macroId":{"value":"3c9d0f9d-65ce-484d-90c5-3c6dc28d8a1a"},"schemaVersion":{"value":"1"},"title":"Panel"}}},"content":[{"type":"paragraph","content":[{"type":"hardBreak"},{"text":"col.","type":"text"},{"text":"linetotal","type":"text"},{"text":".type=number","type":"text"},{"type":"hardBreak"},{"text":"col.","type":"text"},{"text":"linetotal","type":"text"},{"text":".formula={weight} * {shipprice} * 1.10","type":"text"}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"bodiedExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"panel","parameters":{"macroParams":{"title":{"value":"conditional"}},"macroMetadata":{"macroId":{"value":"a47f88d6-5900-447a-bdf7-2c5b54d7dd2f"},"schemaVersion":{"value":"1"},"title":"Panel"}}},"content":[{"type":"paragraph","content":[{"text":"col.linetotal.type=number","type":"text"},{"type":"hardBreak"},{"text":"col.linetotal.formula=if( {shipaddress} =~ [\"London\", \"Birmingham\"]) { {weight} * {shipprice} * 1.10 } else {{weight} * {shipprice} * 1.50 }","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"String functions","type":"text"}]},{"type":"bodiedExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"panel","parameters":{"macroParams":{"title":{"value":"Concatenation"}},"macroMetadata":{"macroId":{"value":"a96eaa43-f3e5-4b8e-966b-b1d32a719abd"},"schemaVersion":{"value":"1"},"title":"Panel"}}},"content":[{"type":"paragraph","content":[{"text":"# strings can be concatenated","type":"text","marks":[{"type":"subsup","attrs":{"type":"sup"}}]}]},{"type":"paragraph","content":[{"text":"#","type":"text","marks":[{"type":"subsup","attrs":{"type":"sup"}}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"paragraph","content":[{"text":"col.label.type = string","type":"text","marks":[{"type":"subsup","attrs":{"type":"sup"}}]}]},{"type":"paragraph","content":[{"text":"col.label.formula = {company} + \" \" + {shipaddress} + \" \" + {contactperson} + \" \" + {weight} + \"lbs\"","type":"text","marks":[{"type":"subsup","attrs":{"type":"sup"}}]}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"bodiedExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"panel","parameters":{"macroParams":{"title":{"value":"Length of a string"}},"macroMetadata":{"macroId":{"value":"a60c365a-9859-4c49-be43-3bbb800db884"},"schemaVersion":{"value":"1"},"title":"Panel"}}},"content":[{"type":"paragraph","content":[{"type":"hardBreak"},{"text":"col.","type":"text"},{"text":"label","type":"text"},{"text":".type=string","type":"text"},{"type":"hardBreak"},{"text":"col.","type":"text"},{"text":"label","type":"text"},{"text":".formula=size({","type":"text"},{"text":"shipaddress","type":"text"},{"text":"})","type":"text"}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"bodiedExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"panel","parameters":{"macroParams":{"title":{"value":" In or Match (=~)"}},"macroMetadata":{"macroId":{"value":"4b9e72f6-be07-4f2f-8cba-d4b707554592"},"schemaVersion":{"value":"1"},"title":"Panel"}}},"content":[{"type":"paragraph","content":[{"text":"# Column should contain 'taxi' when the address is London or Birmingham, else 'Courier'","type":"text"}]},{"type":"paragraph","content":[{"text":"col.","type":"text"},{"text":"label","type":"text"},{"text":".type=string","type":"text"},{"type":"hardBreak"},{"text":"col.","type":"text"},{"text":"label","type":"text"},{"text":".formula= if( {","type":"text"},{"text":"shipaddress","type":"text"},{"text":"} =~ [\"London\", \"Birmingham\"]) {\"taxi\"} else {\"courier\"}","type":"text"}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"bodiedExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"panel","parameters":{"macroParams":{"title":{"value":" Not-In or Not-Match (!~)"}},"macroMetadata":{"macroId":{"value":"6d3f7330-23ba-4d81-b05c-414ea4bd4ace"},"schemaVersion":{"value":"1"},"title":"Panel"}}},"content":[{"type":"paragraph","content":[{"type":"hardBreak"},{"text":"col.","type":"text"},{"text":"label","type":"text"},{"text":".type=string","type":"text"},{"type":"hardBreak"},{"text":"col.","type":"text"},{"text":"label","type":"text"},{"text":".formula= ","type":"text"},{"text":" if( {","type":"text"},{"text":"shipaddress","type":"text"},{"text":"} !~ [\"London\", \"Birmingham\"]) {\"taxi\"} else {\"courier\"}","type":"text"}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"bodiedExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"panel","parameters":{"macroParams":{"title":{"value":" Empty String"}},"macroMetadata":{"macroId":{"value":"b38ce218-7eb9-4070-96fd-a9f8d4d3b02b"},"schemaVersion":{"value":"1"},"title":"Panel"}}},"content":[{"type":"paragraph","content":[{"text":"# Column names can be reused in formulas (if no shipment address given, use the invoice address)","type":"text"}]},{"type":"paragraph","content":[{"text":"col.","type":"text"},{"text":"label","type":"text"},{"text":".type=string","type":"text"},{"type":"hardBreak"},{"text":"col.","type":"text"},{"text":"label","type":"text"},{"text":".formula= if(empty({shipaddress})) {{invoiceaddress}} else {{","type":"text"},{"text":"shipaddress","type":"text"},{"text":"}}","type":"text"}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"bodiedExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"panel","parameters":{"macroParams":{"title":{"value":" Substring"}},"macroMetadata":{"macroId":{"value":"d20f9aaa-39a5-49b9-b715-672db7575c40"},"schemaVersion":{"value":"1"},"title":"Panel"}}},"content":[{"type":"paragraph","content":[{"type":"hardBreak"},{"text":"col.","type":"text"},{"text":"label","type":"text"},{"text":".type=string","type":"text"},{"type":"hardBreak"},{"text":"col.","type":"text"},{"text":"label","type":"text"},{"text":".formula= {shipaddress}.substring(1, 3)","type":"text"}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Date functions","type":"text"}]},{"type":"panel","attrs":{"panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Use formulas to calculate differences between dates","type":"text"}]},{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"__confluenceADFMigrationUnsupportedContentInternalExtension__","text":"","parameters":{"cxhtml":"

","block-inline":true,"nestedContent":{"type":"doc","content":[{"type":"codeBlock","content":[{"text":"gd.columns=_date, _date2, dateToNow, dateToDate2\n\ngd.ds=jira\ngd.tablename=abc\n\ncol.dateToNow=DateToNow\ncol.dateToNow.type=string\ncol.dateToNow.formula=dates:diff({_date}, dates:now()).hours()\n\n\ncol.dateToDate2=DateToDate2\ncol.dateToDate2.type=string\ncol.dateToDate2.formula=dates:diff({_date}, {_date2}).hours()\n\ncol._date=date\ncol._date.type=datetime\n\ncol._date2=date2\ncol._date2.type=datetime","type":"text"}]}],"version":1}}}}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"paragraph","content":[{"text":"Some more examples","type":"text"}]},{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"__confluenceADFMigrationUnsupportedContentInternalExtension__","text":"","parameters":{"cxhtml":"

","block-inline":true,"nestedContent":{"type":"doc","content":[{"type":"codeBlock","content":[{"text":"dates:diff({dateColumnName_1}, {dateColumnName_2}).minutes()\ndates:diff({dateColumnName_1}, {dateColumnName_2}).hours()\ndates:diff({dateColumnName_1}, {dateColumnName_2}).days()\ndates:diff({dateColumnName_1}, {dateColumnName_2}).months()\ndates:diff({dateColumnName_1}, {dateColumnName_2}).quarters()\ndates:diff(dates:now(), {dateColumnName_1}).minutes()","type":"text"}]}],"version":1}}}}]},{"type":"paragraph","content":[{"text":"You can also use ","type":"text"},{"text":"dates:diff","type":"text","marks":[{"type":"strong"}]},{"text":" formula with ","type":"text"},{"text":"different date formats. ","type":"text","marks":[{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"For example, if the grid configuration of the Date column uses custom date format ","type":"text"},{"text":"dd/mm/yy","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":", you can add that custom date format into the ","type":"text"},{"text":"dates:diff ","type":"text","marks":[{"type":"strong"}]},{"text":"formula as an additional parameter.","type":"text"}]},{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"__confluenceADFMigrationUnsupportedContentInternalExtension__","text":"","parameters":{"cxhtml":"

","block-inline":true,"nestedContent":{"type":"doc","content":[{"type":"codeBlock","content":[{"text":"dates:diff({dateColumnName_1}, {dateColumnName_2}, \"dd/mm/yy\").minutes()\ndates:diff({dateColumnName_1}, {dateColumnName_2}, \"dd/mm/yy\").hours()\ndates:diff({dateColumnName_1}, {dateColumnName_2}, \"dd/mm/yy\").days()\ndates:diff({dateColumnName_1}, {dateColumnName_2}, \"dd/mm/yy\").months()\ndates:diff({dateColumnName_1}, {dateColumnName_2}, \"dd/mm/yy\").quarters()\ndates:diff(dates:now(), {dateColumnName_1}, ).minutes()","type":"text"}]}],"version":1}}}}]},{"type":"paragraph","content":[{"text":"For ","type":"text"},{"text":"dd.M.yy (","type":"text","marks":[{"type":"em"},{"type":"strong"}]},{"text":"20.Oct.09","type":"text","marks":[{"type":"textColor","attrs":{"color":"#333333"}},{"type":"em"},{"type":"strong"}]},{"text":")","type":"text","marks":[{"type":"em"},{"type":"strong"}]},{"text":" use the code as below","type":"text"}]},{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"__confluenceADFMigrationUnsupportedContentInternalExtension__","text":"","parameters":{"cxhtml":"

","block-inline":true,"nestedContent":{"type":"doc","content":[{"type":"codeBlock","content":[{"text":"dates:diff({dateColumnName_1}, {dateColumnName_2}, \"dd.MMM.y\").days()","type":"text"}]}],"version":1}}}}]},{"type":"paragraph","content":[{"text":"For ","type":"text"},{"text":"dd-M-yy (10-Oct-2009)","type":"text","marks":[{"type":"em"},{"type":"strong"}]},{"text":" use the code as below","type":"text"}]},{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"__confluenceADFMigrationUnsupportedContentInternalExtension__","text":"","parameters":{"cxhtml":"

","block-inline":true,"nestedContent":{"type":"doc","content":[{"type":"codeBlock","content":[{"text":"dates:diff({dateColumnName_1}, {dateColumnName_2}, \"dd-MMM-y\").days()","type":"text"}]}],"version":1}}}}]},{"type":"paragraph","content":[{"text":"Grid configuration with formula date function and custom date formatting","type":"text","marks":[{"type":"strong"}]}]},{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"__confluenceADFMigrationUnsupportedContentInternalExtension__","text":"","parameters":{"cxhtml":"

","block-inline":true,"nestedContent":{"type":"doc","content":[{"type":"codeBlock","content":[{"text":"gd.columns=date_one,date_two,dates_diff\ngd.tablename=actions\ngd.ds=jira\n\ncol.date_one=Date One\ncol.date_one.type=date\ncol.date_one.formatDate=d.M.y\n\ncol.date_two=Date Two\ncol.date_two.type=date\ncol.date_two.formatDate=d.M.y\n\ncol.dates_diff=Dates Diff\ncol.dates_diff.formula=dates:diff({date_one},{date_two},\"d.MMM.y\").days()","type":"text"}]}],"version":1}}}}]},{"type":"paragraph","content":[{"type":"hardBreak"}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Math functions","type":"text"}]},{"type":"panel","attrs":{"panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"col.foo = Foo","type":"text","marks":[{"type":"code"}]}]},{"type":"paragraph","content":[{"text":"col.foo.type = number","type":"text","marks":[{"type":"code"}]}]},{"type":"paragraph","content":[{"text":"...","type":"text","marks":[{"type":"code"}]}]},{"type":"paragraph","content":[{"text":"col.xyz = Math example","type":"text","marks":[{"type":"code"}]}]},{"type":"paragraph","content":[{"text":"col.xyz.formula=\"signum: ``\" + math:signum({foo}) + \"``; round: ``\" + math:round({foo}) + \"`` ; ceil:\" + math:ceil({foo})","type":"text","marks":[{"type":"code"}]}]},{"type":"paragraph","content":[{"text":"...","type":"text","marks":[{"type":"code"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Query functions (since v1.19.0)","type":"text"}]},{"type":"panel","attrs":{"panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Use query functions to perform SELECT SQL queries to your datasources","type":"text"}]},{"type":"paragraph","content":[{"text":"Get status of the issue by its number","type":"text"}]},{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"__confluenceADFMigrationUnsupportedContentInternalExtension__","text":"","parameters":{"cxhtml":"

","block-inline":true,"nestedContent":{"type":"doc","content":[{"type":"codeBlock","content":[{"text":"col.issuenum=issuenum\ncol.issuenum.type=string\n\ncol.status=status\ncol.status.type=string\ncol.status.formula=queries:value('jira', 'select ist.pname from issuestatus ist inner join jiraissue ji on ji.issuestatus = ist.id and ji.issuenum = ' + {issuenum})","type":"text"}]}],"version":1}}}}]},{"type":"paragraph","content":[{"text":"Decide if you should work on specific issue depending if it has a specific component selected","type":"text"}]},{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"__confluenceADFMigrationUnsupportedContentInternalExtension__","text":"","parameters":{"cxhtml":"

","block-inline":true,"nestedContent":{"type":"doc","content":[{"type":"codeBlock","content":[{"text":"col.issuenum=issuenum\ncol.issuenum.type=string\n \ncol.decision=decision\ncol.decision.type=string\ncol.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'}","type":"text"}]}],"version":1}}}}]},{"type":"paragraph","content":[{"type":"hardBreak"}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Related information ","type":"text"}]},{"type":"extension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"contentbylabel","parameters":{"macroParams":{"showLabels":{"value":"false"},"spaces":{"value":"TGPD"},"showSpace":{"value":"false"},"cql":{"value":"label = \"formula\" and space = \"TGED\""},"labels":{"value":"formula"}},"macroMetadata":{"macroId":{"value":"225f7d90-d3bc-454d-b291-56726b46a93c"},"schemaVersion":{"value":"4"},"title":"Content by Label"}}}}]},{"type":"layoutColumn","attrs":{"width":33.33},"content":[{"type":"extension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{"maxLevel":{"value":"2"}},"macroMetadata":{"macroId":{"value":"3fa7ac59-7e70-4939-8e82-656e9b3a35c5"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}}}}]}]}],"version":1}

Browser not supported