Smartstaff, administrator
686
edits
Changes
no edit summary
==General Information==
Thus if you are doing a calculation on a value that needs to be populated or calculated on the same Level 3 item, this will avoid you having to save twice in order to get the final result.
===On Using the Semicolon===
When you wish to use multiple template formulas, you must use the semicolon to separate them. For example:
* If a semicolon is used in the template formula itself, multiple template formulas can be separated by the term ''<end>''
* This is of use, for example, when a template formula is used to store multiple checkbox selections into a consolidated checkbox as follows: <br />''@level3.Main Checkbox@="@level3.Main Checkbox@;@level3.Checkbox1@;@level3.Checkbox2@"''
* In this case, end this template formula, and all other template formulas on this level (general and template/type formulas) with the term ''<end>''
* The resultant list of template formulas might look like this:
@level1.total@=@level1.cost@*@level1.quantity@<end>
@level3.Main Checkbox@="@level3.Main Checkbox@;@level3.Checkbox1@;@level3.Checkbox2@"<end>
@level1.gst@=@level1.total@*0.05<end>
<!--The <end> syntax is only available with May 2014 upgrade-->
===Template Standard Field must be enabled and visible===
'''IMPORTANT''': ''Template formulas will only work if the Template standard field is enabled and visible on the page.''
* See [[UTA_Standard_Field_Settings#Adding_a_field|enabling standard fields]] for information on enabling the field.
* See [[Visibility Condition]] for information and making it visible.
==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]]
