Smartstaff, administrator
686
edits
Changes
no edit summary
==General Information==
==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>
===Case-When Condition===
You can also use the '''Case''' syntax:
: <span style="font-size: medium;">'''@level1.Notice Textvariable1@=CASE '@level1.statusvariable2@WHEN ' WHEN 'Status condition 1'' THEN 'Submission Cutoff is March 5th.'value for condition 1'' WHEN 'Status 'condition 2' ' THEN 'Thank 'value for your submissioncondition 2'' WHEN 'Status 'condition 3'' THEN 'Congratulations'value for condition 3'' ELSE 'I have no comment'value if not in any condition'' END;'''</span>OR
: <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'),
@level3.consultant@=if('@level3.contact.rolelist@' like '%12345%','@level3.contact.fullname@','@level2.owner.fullname@');
* 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
==Using Object Syntax==
<br/><br/><br/><br/>
==Using Trigger Points==
Trigger Point syntax to trigger template formulas under specific conditions e.g. trigger specific template formulas for specific UTA types and statuses.
Double colons will be used to separate the formula trigger and the formula itself.
<pre>
{"formula trigger" : [{"type" : "type1"}]}::template formula
</pre>
Each condition can be a combination of type and status.
<pre>
{"formula trigger" : [{"type" : "type1", "status" : "status1"}]}::template formula
</pre>
Each type/status condition can contain multiple type / status names , each delimited by comma.
<pre>
{"formula trigger" : [{"type" : "type1, type2"}]}::template formula
</pre>
In the example below the Current Date field will only be updated under 2 conditions:
* Type is 'Type 1' or 'Type 3' and status is 'Submitted'
* Type is 'Type 2' and status is 'Draft'
<pre>
{"formula trigger" : [{"type" : "Type 1,Type 3", "status" : "Submitted"}, {"type" : "Type 2", "status" : "Draft"}]}::@ level1.Current Date@ =now()
</pre>
==Manipulating dates in template formula==
* You cannot change the [[Status]] using a Template / Type Formula.
* You cannot set the Amount using a Template / Type Formula.
* You can trigger execution of the Template / Type formulas against a group of records without opening and saving them using the [[Batch Update]] feature.
* [[Ssescape|SSEscape - for using Template Formulas with text fields]]
[[Category:Universal Tracking ApplicationField Update Formulas]][[Category:Date FormatsUniversal Tracking Application]]
