Smartstaff, administrator
686
edits
Changes
no edit summary
==General Information==
==Execution Sequence==
* The Formulas are calculated when a record is savedor saved in draft.
* The ''General'' formula on the Application Configuration page is executed first, followed by the [[Template]]/[[Type]] specific formula (if present).
Note: Currently the system doesn't support summing from level 3 to level 1 using a custom field id. The summing would only work with Amount and Duration field.
'''Summing Level 2 to Level 2 base on a particular level 2 type:'''
Go to level 2 template and use the following format @level2.#50235#@=@level1.level2.sum(#987763#):eventtype=8860@
==Using a Level 3 Type Formula==
==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==
To refer to month in a date field, make use of Date_Format('@level2.fieldname@'),'%m'. Alternatively use date('@level2.fieldname@') to extract yyyy-mm-dd. Go to sscalculation [http://wiki.smartsimple.com/wiki/Sscalculation] for more examples of syntax.
==UTA Provider syntax==
* 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.
* When pulling data from a Text – Multiple Lines field, to remove line break code such as < br >, use @fieldname'''.value'''@.
==See Also==
* [[The Musings of Chin - Template Formulas]]
* [[Ssescape|SSEscape - for using Template Formulas with text fields]] [[Category:Universal Tracking ApplicationField Update Formulas]][[Category:Date FormatsUniversal Tracking Application]]
