0
edits
Changes
→Using Conditional Statements
==Using Conditional Statements==
===IF-Else Condition===
Conditional statements can be used in formulas with the following structure:
:<span style="font -size="3: medium;">'''@variable@=if(''conditional statement'',''value if true'',''value if false'');'''</fontspan>
It is recommended to separate out the criteria to avoid issues caused by variable values containing special characters like quotation marks. For example, instead of the following statement...
<pre style="white-space: -o-pre-wrap;word-wrap: break-word;">@level1.Success Criteria Count@=if('@level1.status@' IN ('Draft','Pending'),'@level1.xml.Success Criteria.Criteria.item.nodevalue@','@level1.Success Criteria Count@');</pre>
<pre style="white-space: -o-pre-wrap;word-wrap: break-word;">@level1.Success Criteria Count@=if(? IN ('Draft','Pending'),?,?):=:@level1.status@:,:@level1.xml.Success Criteria.Criteria.item.nodevalue@:,:@level1.Success Criteria Count@;</pre>
In the above example, the formula will trigger when the record is in ''"LOI Draft"'' status.
===Case-When Condition===
You can also use the '''Case''' syntax:
:<span style="font -size="3: medium;">'''@variable1@=CASE @variable2@ WHEN ''condition 1'' THEN ''value for condition 1'' WHEN ''condition 2'' THEN ''value for condition 2'' WHEN ''condition 3'' THEN ''value for condition 3'' ELSE ''value if not in any condition'' END<end>'''</fontspan> OR :<font size="3">'''@variable1@=CASE WHEN @variable2@ ''in (list items)'' THEN ''value for condition 1'' WHEN @variable2@ ''in (list items)'' THEN ''value for condition 2'' WHEN @variable2@ ''in (list items)'' THEN ''value for condition 3'' ELSE ''value if not in any condition'' END<end>'''</font>
: <span style="font-size: medium;">'''@variable1@=CASE WHEN @variable2@ ''in (list items)'' THEN ''value for condition 1'' WHEN @variable2@ ''in (list items)'' THEN ''value for condition 2'' WHEN @variable2@ ''in (list items)'' THEN ''value for condition 3'' ELSE ''value if not in any condition'' END'''</span>
===Examples===
* At Level 2: if the field called ''locationpreference'' says "Home" put the value in the ''homeaddress'' field into '''Location'''. Otherwise put the value from the ''worksaddress'' field into '''Location'''.
@level2.location@=if('@level2.locationpreference@'='Home','@level2.homeaddress@','@level2.workaddress@');
*If the '''Status''' is ''Closed'' AND no ''enddate'' entered, populate the '''enddate''' with today's date. Otherwise leave the existing ''enddate''.: ::'''Note''': to test for a blank date you should test against both '''=''''' and '''regexp '@|0000''''
:: '''Note''': to test for a blank date you should test against both '''=''''' and '''regexp '@|0000''''
@level1.enddate@=
if('@level1.status@'='Closed' and ('@level1.enddate@'='' '' or '@level1.enddate@' regexp '@|0000'),
date_format(now(),'%Y-%m-%d'),
* At Level 2, populate the Level 2 ''Funding Group'' based on a level 1 ''Funding Area''
@level2.FundingGroup@=CASE WHEN "@level1.FundingArea@" in ('Water", "Recycling","Environment") THEN "Water" WHEN "@level1.FundingArea@" in ("Active Health", "Arts & Culture","Education") THEN "Well-Being" WHEN "@level1.FundingArea@" in ("Women Empowerment") THEN "Women" ELSE "" END<end>
==Using Object Syntax==
