Difference between revisions of "Template / Type Formula"

From SmartWiki
Jump to: navigation, search
(41 intermediate revisions by 8 users not shown)
Line 1: Line 1:
Level One, Level Two and Level Three records can perform advanced calculations and logic within their Formula boxes.
 
  
 +
==General Information==
  
 +
{{l1}}, {{l2}} and {{l3}} records can perform advanced calculations and logic within their Formula boxes.
  
==General Information==
+
Formulas can be defined in two different locations for each of the Levels within the [[UTA]]
Level One, Level Two and Level Three records can perform advanced calculations and logic within their Formula boxes.
 
  
Formulas can be defined in 2 different locations for each of the Levels within the [[UTA]]
 
 
* The formulas on the Application Configuration page apply to all records saved at the specified Level.
 
* The formulas on the Application Configuration page apply to all records saved at the specified Level.
* The formulas the Template page (Level 1) or Type page (Levels 2 and 3) apply to records on the given Template/Type only.
 
  
 +
* The formulas the Template page ({{l1}}) or Type page ({{l2}} and {{l3}}) apply to records on the given Template/Type only.
  
If you wish to perform calculations on fields associated with Level 3, but store the results in fields at the Level 2, you use the '''Level 2 Formula''' field. You can use any expression and also some built in functions.
+
If you wish to perform calculations on fields associated with Level 3, but store the results in fields at the Level 2, you use the '''Level 2 Formula''' field. You can use any expression and also some built in functions.
 +
 
 +
You may also wish to use Template Formulas instead of a Calculated field because the calculations are done server side, rather than by the browser.<br>
  
You may also wish to use Template Formulas instead of a Calculated field because the calculations are done server side, rather than by the browser.<br>
 
 
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.
 
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:
 +
 +
'''@level1.total@=@level1.cost@*@level1.quantity@;@level1.gst@=@level1.total@*0.05''';
 +
 +
* 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 saved or saved in draft.
 +
 +
* The ''General'' formula on the Application Configuration page is executed first, followed by the [[Template]]/[[Type]] specific formula (if present).
  
===Execution Sequence===
 
* The Formulas are calculated when a record is saved.
 
* The ''General'' formula for the on the Application Configuration page is executed first, followed by the [[Template]]/[[Type]] specific formula (if present).
 
 
* Saving a Level 2 or Level 3 record will also initiate the formulas on the parent level(s):
 
* Saving a Level 2 or Level 3 record will also initiate the formulas on the parent level(s):
  
 +
<u>{{l1}}:</u>
 +
 +
* When a {{l1}} record is saved the formulas are executed in the following sequence:
  
<u>Level 1:</u>
 
* When a Level 1 record is saved the formulas are executed in the following sequence:
 
 
::''Level 1 Formula-->Level 1 Template Formula''
 
::''Level 1 Formula-->Level 1 Template Formula''
  
 +
<u>Level 2:</u>
  
<u>Level 2:</u>
 
 
* When a Level 2 record is saved the formulas are executed in the following sequence:
 
* When a Level 2 record is saved the formulas are executed in the following sequence:
 +
 
::''Level 2 Formula-->Level 2 Type Formula-->Level 1 Formula-->Level 1 Template Formula''
 
::''Level 2 Formula-->Level 2 Type Formula-->Level 1 Formula-->Level 1 Template Formula''
 +
 
* '''Note''': The Level 1 Formula and Level 1 Template Formula are only executed if one of them refers to a Level 2 variable.
 
* '''Note''': The Level 1 Formula and Level 1 Template Formula are only executed if one of them refers to a Level 2 variable.
  
 +
<u>Level 3:</u>
  
<u>Level 3:</u>
 
 
* When a Level 3 record is saved the formulas are executed in the following sequence:
 
* When a Level 3 record is saved the formulas are executed in the following sequence:
 +
 
::''Level 3 Formula-->Level 3 Type Formula-->Level 2 Formula-->Level 2 Type Formula-->Level 1 Formula-->Level 1 Template Formula''
 
::''Level 3 Formula-->Level 3 Type Formula-->Level 2 Formula-->Level 2 Type Formula-->Level 1 Formula-->Level 1 Template Formula''
 +
 
* '''Note''': The Level 2 Formula and Level 2 Template Formula are only executed if one of them refers to a Level 3 variable.
 
* '''Note''': The Level 2 Formula and Level 2 Template Formula are only executed if one of them refers to a Level 3 variable.
  
Line 43: Line 71:
  
 
There are a number of functions that are pre-defined:
 
There are a number of functions that are pre-defined:
{| border="1" style="background-color:#ffffcc;" cellpadding="20" cellspacing="0"  
+
 
 +
{| border="1" style="background-color:#ffffcc;" cellpadding="20" cellspacing="0"
 +
 
 
! Function
 
! Function
 +
 
! Example
 
! Example
 +
 
! Description
 
! Description
 +
 
|-
 
|-
 +
 
|Count(*)
 
|Count(*)
 +
 
|@level2.#items#@=@level3.count(*)@
 
|@level2.#items#@=@level3.count(*)@
 +
 
|Count the number of Level 3 records and store the result in the Level 2 '''Items''' custom field .
 
|Count the number of Level 3 records and store the result in the Level 2 '''Items''' custom field .
 +
 
|-
 
|-
 +
 
|Sum()
 
|Sum()
 +
 
|@level2.amount@=@level3.sum(amount)@
 
|@level2.amount@=@level3.sum(amount)@
 +
 
|Sum the '''amount''' standard field from all Level 3 items and store the result in the Level 2 '''Amount''' standard field
 
|Sum the '''amount''' standard field from all Level 3 items and store the result in the Level 2 '''Amount''' standard field
 +
 
|-
 
|-
 +
 
|Max()
 
|Max()
 +
 
|@level2.#most#@=@level3.max(amount)@
 
|@level2.#most#@=@level3.max(amount)@
 +
 
|Store the largest '''Amount''' in any Level 3 item into the '''Most''' Level 2 [[Custom Field]]
 
|Store the largest '''Amount''' in any Level 3 item into the '''Most''' Level 2 [[Custom Field]]
 +
 
|-
 
|-
 +
 
|Avg()
 
|Avg()
 +
 
|@level2.#average#@=@level3.avg(amount)@
 
|@level2.#average#@=@level3.avg(amount)@
 +
 
|Store the average '''Amount''' across all associated Level 3 items for the amount field into the Level 2 '''Average''' [[Custom Field]].
 
|Store the average '''Amount''' across all associated Level 3 items for the amount field into the Level 2 '''Average''' [[Custom Field]].
 +
 
|}
 
|}
  
 
'''Important:''' The ''sum'', ''max'' and ''avg'' functions will '''only''' work with the '''Amount''' [[Standard Field]] from Level 2 or Level 3.
 
'''Important:''' The ''sum'', ''max'' and ''avg'' functions will '''only''' work with the '''Amount''' [[Standard Field]] from Level 2 or Level 3.
  
 +
===Criteria===
  
===Criteria===
 
 
When using these defined functions you can restrict which records are included in the aggregated functions using criteria.
 
When using these defined functions you can restrict which records are included in the aggregated functions using criteria.
  
Line 86: Line 135:
  
 
@level1.Registered Clients@=@contacts.count(*):cf_123456='Yes'@;
 
@level1.Registered Clients@=@contacts.count(*):cf_123456='Yes'@;
 
 
 
 
When you wish to use multiple template formulas, you must use the semicolon to separate them.  For example:
 
'''@level1.total@=@level1.cost@*@level1.quantity@;@level1.gst@=@level1.total@*0.05'''
 
 
 
'''IMPORTANT''': ''Template formulas will only work if the Template standard field is enabled and visible on the page.''
 
 
  
 
===Counting and Adding Business Days===
 
===Counting and Adding Business Days===
Line 101: Line 140:
 
Aside from using [[Counting Business Days between Two Dates|Javascript]] to count business days, there are 2 SmartSimple functions you can use within the L1, L2, and L3 template formula to count and add business days to any Date field.
 
Aside from using [[Counting Business Days between Two Dates|Javascript]] to count business days, there are 2 SmartSimple functions you can use within the L1, L2, and L3 template formula to count and add business days to any Date field.
  
 +
'''To count the number of business days between 2 date fields, use the following function:'''
  
'''To count the number of business days between 2 date fields, use the following function:'''
+
'''SS_FUNC.countbusdays('Date1','Date2')'''
'''SS_FUNC.countbusdays('Date1','Date2')'''
 
  
 
This example below calculates the number of business days between the Start Date and End Date.
 
This example below calculates the number of business days between the Start Date and End Date.
'''@level1.DateDifference@=SS_FUNC.countbusdays('@level1.Startdate@','@level1.Enddate@')'''
 
  
 +
'''@level1.DateDifference@=SS_FUNC.countbusdays('@level1.Startdate@','@level1.Enddate@')'''
  
 +
'''To increment a date by a number of business days, use the following function:'''
  
'''To increment a date by a number of business days, use the following function:'''
+
'''SS_FUNC.addbusdays('Date', x ) where x = # of business days'''
'''SS_FUNC.addbusdays('Date', x ) where x = # of business days'''
 
  
 
This example below adds 5 business days to the startdate.
 
This example below adds 5 business days to the startdate.
 
'''@level1.DateDifference@=SS_FUNC.addbusdays('@level1.Startdate@',5)'''
 
  
Note in order to take into considering holidays, you will need to create a holiday [[Entering_a_New_Calendar_Event|Calendar]] and associate this calender as the holiday calendar within [[Global Settings]].
+
'''@level1.DateDifference@=SS_FUNC.addbusdays('@level1.Startdate@',5)'''
  
 +
Note in order to take into considering holidays, you will need to create a holiday [[Entering_a_New_Calendar_Event|Calendar]] and associate this calendar as the holiday calendar within [[Global Settings]].
  
 +
==Examples==
  
==Examples==
 
 
* If you want to assign company of the Level 1 '''Owner''' to the Level 1 '''Customer''' field you would use the following:
 
* If you want to assign company of the Level 1 '''Owner''' to the Level 1 '''Customer''' field you would use the following:
@level1.companyid@=@level1.owner.companyid@;
+
 
 +
@level1.companyid@=@level1.owner.companyid@;
 +
 
 
:''Note: the '''Customer''' field can be be hidden, and will still populate. This is true for all Template/Type formulas.''
 
:''Note: the '''Customer''' field can be be hidden, and will still populate. This is true for all Template/Type formulas.''
  
 
* To assign the owner of the company attached to the Level 1 item to the ''Person '' field use the following statement in the Level 1 template formula box:
 
* To assign the owner of the company attached to the Level 1 item to the ''Person '' field use the following statement in the Level 1 template formula box:
@opportunity.peopleid@=@company.ownerid@
+
 
 +
@opportunity.peopleid@=@company.ownerid@
  
 
* To populate the '''End Date''' standard field with the date the status is changed to '''Complete'''
 
* To populate the '''End Date''' standard field with the date the status is changed to '''Complete'''
@level1.enddate@=CASE WHEN "@level1.status@"="Complete" and "@level1.enddate@"="0000-00-00" THEN CURDATE() WHEN "@level1.status@"="Complete" and "@level1.enddate@"!="0000-00-00" THEN "@level1.enddate@" ELSE "0000-00-00" END;
+
 
 +
<pre style="white-space: pre-wrap;
 +
 
 +
white-space: -moz-pre-wrap;
 +
 
 +
white-space: -pre-wrap;
 +
 
 +
white-space: -o-pre-wrap;
 +
 
 +
word-wrap: break-word;">@level1.enddate@=CASE WHEN "@level1.status@"="Complete" and "@level1.enddate@"="0000-00-00" THEN CURDATE() WHEN "@level1.status@"="Complete" and "@level1.enddate@"!="0000-00-00" THEN "@level1.enddate@" ELSE "0000-00-00" END;</pre>
  
 
==Using a Level 2 Type Formula==
 
==Using a Level 2 Type Formula==
Line 145: Line 195:
  
 
[[Image:Uta129.png]]
 
[[Image:Uta129.png]]
+
 
 
5. Click the '''Save''' button.
 
5. Click the '''Save''' button.
  
Line 157: Line 207:
  
 
[[Image:Uta130.png]]
 
[[Image:Uta130.png]]
+
 
 
10. Enter the following formula:
 
10. Enter the following formula:
  
 
[[Image:Uta131.png]]
 
[[Image:Uta131.png]]
+
 
 
11. Click the '''Save''' button.
 
11. Click the '''Save''' button.
  
Line 175: Line 225:
  
 
[[Image:Uta132.png]]
 
[[Image:Uta132.png]]
+
 
 
* The duration is set to the sum of the durations of the associated [[Level 3 Entity|Level 3 entities]].
 
* The duration is set to the sum of the durations of the associated [[Level 3 Entity|Level 3 entities]].
 +
 
* The same technique can be applied to the Level 1 [[Template]] formula to sum all the Level 2 durations.
 
* The same technique can be applied to the Level 1 [[Template]] formula to sum all the Level 2 durations.
 
  
 
'''Summing Level 2 to Level 1 using field ID:'''
 
'''Summing Level 2 to Level 1 using field ID:'''
Line 184: Line 234:
 
Go to level 1 template and use the following format @level1.#123456#@=@level2.sum(#987763#)@
 
Go to level 1 template and use the following format @level1.#123456#@=@level2.sum(#987763#)@
  
'''Summing Level 3 to Level 2 using field ID:'''  
+
'''Summing Level 3 to Level 2 using field ID:'''
  
 
Go to level 2 template and use the following format @level2.#123456#@=@level3.sum(#678954#)@
 
Go to level 2 template and use the following format @level2.#123456#@=@level3.sum(#678954#)@
  
'''Summing Level 3 to Level 2 base on a particular level 3 type:'''  
+
'''Summing Level 3 to Level 2 base on a particular level 3 type:'''
  
 
Go to level 2 template and use the following format @level2.#50235#@=@level3.sum(#50291#):eventtype=8866@
 
Go to level 2 template and use the following format @level2.#50235#@=@level3.sum(#50291#):eventtype=8866@
 +
 
where 50235 is the custom field where you want the value to store in, 50291 is the custom field that you want to sum and 8866 is the level 3 type ID,
 
where 50235 is the custom field where you want the value to store in, 50291 is the custom field that you want to sum and 8866 is the level 3 type ID,
  
 
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.
 
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 a Level 3 Type Formula==
  
 
You can use the Level 3 Type Formula field on the Level 3 Type page in a similar manner as for Level 2.
 
You can use the Level 3 Type Formula field on the Level 3 Type page in a similar manner as for Level 2.
 +
 
* The advantage of using a Level 3 Type Formula instead of a Calculated field is that the calculations are done server side, rather than by the browser.<br /> Thus if you are doing a calculation on a value that needs to be populate or calculated on the same Level 3 item, this will avoid you having to save twice in order to get the final result.
 
* The advantage of using a Level 3 Type Formula instead of a Calculated field is that the calculations are done server side, rather than by the browser.<br /> Thus if you are doing a calculation on a value that needs to be populate or calculated on the same Level 3 item, this will avoid you having to save twice in order to get the final result.
  
 +
The following example calculates the product of the values stored in fields named '''rate''' and '''quantity''', and saves it in a field called '''Total''' with a field ID of 1731330:
 +
 +
'''@level3.total@=@level3.rate@*@level3.quantity@'''
  
The following example calculates the product of the values stored in fields named '''rate''' and '''quantity''', and saves it in a field called '''Total''' with a field ID of 1731330:
+
''or using the fieldid instead of fieldname''
  
'''@level3.total@=@level3.rate@*@level3.quantity@'''
+
'''@level3.#123100#@=@level3.#123098#@*@level3.#123099#@'''
        ''or using the fieldid instead of fieldname''
 
'''@level3.#123100#@=@level3.#123098#@*@level3.#123099#@'''
 
  
 +
* To refer to fields on the associated Level 1 or Level 2 records use '''level1.fieldname''' and '''level2.fieldname''':
  
* To refer to fields on the associated Level 1 or Level 2 records use ''level1.fieldname''' and '''level2.fieldname''':
+
'''@level3.result@=@level1.hourly rate@*@level2.total quantity@
'''@level3.result@=@level1.hourly rate@*@level2.total quantity@
 
  
 
==Using Template/Type Formulas for Text==
 
==Using Template/Type Formulas for Text==
 +
 
If you are using a Template or Type formula to populate a field with '''text''', rather than perform a calculation, you must enclose the statement on the right-side of the equal sign in quotations.
 
If you are using a Template or Type formula to populate a field with '''text''', rather than perform a calculation, you must enclose the statement on the right-side of the equal sign in quotations.
 +
 
* Any other text inside the quotation marks, including spaces, will be concatenated to the result with the variables:
 
* Any other text inside the quotation marks, including spaces, will be concatenated to the result with the variables:
@level1.name@="@level1.client first name@ @level1.client last name@";
+
 
 +
@level1.name@="@level1.client first name@ @level1.client last name@";
 +
 
 
:''Note that the space between the "client first name" and "client last name" variables will place a space between them in the resultant statement.''
 
:''Note that the space between the "client first name" and "client last name" variables will place a space between them in the resultant statement.''
  
 +
'''Example 2'''
 +
 +
@level1.name@="@level1.item 1@, @level1.item 2@ and @level1.item 3@";
  
'''Example 2'''
 
@level1.name@="@level1.item 1@, @level1.item 2@ and @level1.item 3@";
 
 
:''Would result in a comma and space between the first 2 items and the word "and" between item 2 and 3.''
 
:''Would result in a comma and space between the first 2 items and the word "and" between item 2 and 3.''
  
Line 226: Line 287:
  
 
==Using Conditional Statements==
 
==Using Conditional Statements==
 +
===IF-Else Condition===
 
Conditional statements can be used in formulas with the following structure:
 
Conditional statements can be used in formulas with the following structure:
:<font size="3">'''@variable@=if(''conditional statement'',''value if true'',''value if false');'''</font>
 
  
 +
: <span style="font-size: medium;">'''@variable@=if(''conditional statement'',''value if true'',''value if false'');'''</span>
 +
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>
 +
 +
...use the following syntax...
 +
 +
<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>
 +
 +
Note that the criteria for the ''if'' statement are first referenced as ''?'' marks, then the ''if'' statement is separated from the criteria values using '':=:'', then the separate criteria values are separated by '':,:''
 +
 +
===Status-Trigger Condition===
 +
With this condition the template formula will only trigger if the status of the record is in the status which has been specified in the formula. Example:
 +
 +
 +
 +
{"formula trigger" : [{"status" : "LOI Draft"}]}::@level1.LOI Deadline Date@=IF("@level1.LOI Extend Deadline@"!="Yes","@level1.[#(?object=utaproviderL2::criteria=typename="Call")$LOI Submission Deadline Date$#]@","@level1.LOI Deadline Date@")
 +
 +
 +
 +
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:
 
You can also use the '''Case''' syntax:
@level1.Notice Text@=CASE '@level1.status@' WHEN 'Status 1' THEN 'Submission Cutoff is March 5th.' WHEN 'Status 2'
 
THEN 'Thank for your submission' WHEN 'Status 3' THEN 'Congratulations' ELSE 'I have no comment' END;
 
 
  
 +
: <span style="font-size: 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'''</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===
 
===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'''.
 
* 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@');
 
  
 +
@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''''
 +
@level1.enddate@=
 +
 +
if('@level1.status@'='Closed' and ('@level1.enddate@'='' or '@level1.enddate@' regexp '@|0000'),
  
*If the '''Status''' is ''Closed'' AND no ''enddate'' entered, populate the '''enddate''' with today's date. Otherwise leave the existing ''enddate''.:
+
date_format(now(),'%Y-%m-%d'),
::'''Note''': to test for a blank date you should test against both '''=&#39;&#39;''' and '''regexp '@|0000''''
 
@level1.enddate@=
 
if('@level1.status@'='Closed' and ('@level1.enddate@'=&#39;&#39; or '@level1.enddate@' regexp '@|0000'),
 
date_format(now(),'%Y-%m-%d'),
 
'@level1.enddate@');
 
  
 +
'@level1.enddate@');
  
 
* At Level 3, if the contact assigned is a member of role 12345 put their name into the ''Consultant'' field. Otherwise use the Level 2 ''Owner'' for the Consultant field:
 
* At Level 3, if the contact assigned is a member of role 12345 put their name into the ''Consultant'' field. Otherwise use the Level 2 ''Owner'' for the Consultant field:
@level3.consultant@=if('@level3.contact.rolelist@' like '%12345%','@level3.contact.fullname@','@level2.owner.fullname@');
 
  
 +
@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==
 
==Using Object Syntax==
Object syntax can be used in template formulas to loop through the level below and to assign a value or values based on some defined criteria.
 
  
The syntax differs from the basic [[Web_Page_View_Field_Variables#To_Display_a_List_of_Activities.2C_Notes.2C_Transactions.2C_Contacts.2C_Companies.2C_Consumers.2C_Providers|object syntax]] in that the template formula must use double colons (i.e. '''::''') rather than a semi-colon to separate the criteria from the object.  
+
Object syntax can be used in template formulas to loop through the level below and to assign a value or values based on some defined criteria.
 +
 
 +
The syntax differs from the basic [[Web_Page_View_Field_Variables#To_Display_a_List_of_Activities.2C_Notes.2C_Transactions.2C_Contacts.2C_Companies.2C_Consumers.2C_Providers|object syntax]] in that the template formula must use double colons (i.e. '''::''') rather than a semi-colon to separate the criteria from the object.
  
 
An example of the syntax for this is shown below:
 
An example of the syntax for this is shown below:
 +
 
<br/><br/>
 
<br/><br/>
 +
 
:<font size="3">'''@level1.name@="@level1.[#(?object=activity::criteria=eventtype=3123) ~subject~ #]@"'''</font>
 
:<font size="3">'''@level1.name@="@level1.[#(?object=activity::criteria=eventtype=3123) ~subject~ #]@"'''</font>
  
<br/>In this example the value in the Subject standard field for the child level 2s that have an eventtype with value of 3123 will be stored in the Name standard field of the parent level one.
+
<br/>In this example the value in the Subject standard field for the child level 2s that have an eventtype with value of 3123 will be stored in the Name standard field of the parent {{l1}}.
 +
 
 
<br/><br/><br/><br/>
 
<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==
 
==UTA Provider syntax==
* If you are using template formulas to populate Level One fields with details on associated UTA providers then you must use the following syntax:
+
 
 +
* If you are using template formulas to populate {{l1}} fields with details on associated UTA providers then you must use the following syntax:
 +
 
 
@level1.fieldname@=@level1.utaprovider.count(*)@;
 
@level1.fieldname@=@level1.utaprovider.count(*)@;
  
 +
* If you are using template formulas to populate {{l1}} fields with details on associated UTA consumer then you must use the following syntax:
  
* If you are using template formulas to populate Level One fields with details on associated UTA consumer then you must use the following syntax:
 
 
@level1.fieldname@=@level1.utaconsumer.count(*)@;
 
@level1.fieldname@=@level1.utaconsumer.count(*)@;
  
 +
* If you are using template formulas to populate {{l2}} fields with details on associated {{l1}} UTA providers then you must use the following syntax:
  
* If you are using template formulas to populate Level Two fields with details on associated Level One UTA providers then you must use the following syntax:
 
 
@level2.fieldname@=@level2.utaprovider.count(*)@;
 
@level2.fieldname@=@level2.utaprovider.count(*)@;
  
 +
* If you are using template formulas to populate {{l2}} fields with details on associated {{l1}} UTA consumers then you must use the following syntax:
  
* If you are using template formulas to populate Level Two fields with details on associated Level One UTA consumers then you must use the following syntax:
 
 
@level2.fieldname@=@level2.utaconsumer.count(*)@;
 
@level2.fieldname@=@level2.utaconsumer.count(*)@;
  
 
==Additional Information==
 
==Additional Information==
 +
 
* You cannot change the [[Status]] using a Template / Type 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.
 
* 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==
 
==See Also==
 +
 
* [[Role Formula]]
 
* [[Role Formula]]
 +
 
* [[The Musings of Chin - Template Formulas]]
 
* [[The Musings of Chin - Template Formulas]]
  
[[Category:Universal Tracking Application]][[Category:Date Formats]]
+
* [[Ssescape|SSEscape - for using Template Formulas with text fields]]
 +
 
 +
[[Category:Field Update Formulas]][[Category:Universal Tracking Application]]

Revision as of 15:01, 23 October 2017

General Information

Level 1, Level 2 and Level 3 records can perform advanced calculations and logic within their Formula boxes.

Formulas can be defined in two different locations for each of the Levels within the UTA

  • The formulas on the Application Configuration page apply to all records saved at the specified Level.
  • The formulas the Template page (Level 1) or Type page (Level 2 and Level 3) apply to records on the given Template/Type only.

If you wish to perform calculations on fields associated with Level 3, but store the results in fields at the Level 2, you use the Level 2 Formula field. You can use any expression and also some built in functions.

You may also wish to use Template Formulas instead of a Calculated field because the calculations are done server side, rather than by the browser.

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:

@level1.total@=@level1.cost@*@level1.quantity@;@level1.gst@=@level1.total@*0.05;

  • 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:
    @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>


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.

Execution Sequence

  • The Formulas are calculated when a record is saved or saved in draft.
  • The General formula on the Application Configuration page is executed first, followed by the Template/Type specific formula (if present).
  • Saving a Level 2 or Level 3 record will also initiate the formulas on the parent level(s):

Level 1:

  • When a Level 1 record is saved the formulas are executed in the following sequence:
Level 1 Formula-->Level 1 Template Formula

Level 2:

  • When a Level 2 record is saved the formulas are executed in the following sequence:
Level 2 Formula-->Level 2 Type Formula-->Level 1 Formula-->Level 1 Template Formula
  • Note: The Level 1 Formula and Level 1 Template Formula are only executed if one of them refers to a Level 2 variable.

Level 3:

  • When a Level 3 record is saved the formulas are executed in the following sequence:
Level 3 Formula-->Level 3 Type Formula-->Level 2 Formula-->Level 2 Type Formula-->Level 1 Formula-->Level 1 Template Formula
  • Note: The Level 2 Formula and Level 2 Template Formula are only executed if one of them refers to a Level 3 variable.

Defined Functions

There are a number of functions that are pre-defined:

Function Example Description
Count(*) @level2.#items#@=@level3.count(*)@ Count the number of Level 3 records and store the result in the Level 2 Items custom field .
Sum() @level2.amount@=@level3.sum(amount)@ Sum the amount standard field from all Level 3 items and store the result in the Level 2 Amount standard field
Max() @level2.#most#@=@level3.max(amount)@ Store the largest Amount in any Level 3 item into the Most Level 2 Custom Field
Avg() @level2.#average#@=@level3.avg(amount)@ Store the average Amount across all associated Level 3 items for the amount field into the Level 2 Average Custom Field.

Important: The sum, max and avg functions will only work with the Amount Standard Field from Level 2 or Level 3.

Criteria

When using these defined functions you can restrict which records are included in the aggregated functions using criteria.

  • Examples:

@level2.count(*):typename='Referee Form'@

@level2.count(*):statusname='Submitted'@

@level2.count(*):typename='Referee Form' and statusname='Submitted'@

@level1.companies.count(*):rolename='Funding Agency'@

@level1.contacts.count(*):rolename='Legal Representative'@

@level1.Total Number of Reports@=@level2.count(*):typename="Report" and status.statusid not in (2226,2238)@;

@level1.Registered Clients@=@contacts.count(*):cf_123456='Yes'@;

Counting and Adding Business Days

Aside from using Javascript to count business days, there are 2 SmartSimple functions you can use within the L1, L2, and L3 template formula to count and add business days to any Date field.

To count the number of business days between 2 date fields, use the following function:

SS_FUNC.countbusdays('Date1','Date2')

This example below calculates the number of business days between the Start Date and End Date.

@level1.DateDifference@=SS_FUNC.countbusdays('@level1.Startdate@','@level1.Enddate@')

To increment a date by a number of business days, use the following function:

SS_FUNC.addbusdays('Date', x ) where x = # of business days

This example below adds 5 business days to the startdate.

@level1.DateDifference@=SS_FUNC.addbusdays('@level1.Startdate@',5)

Note in order to take into considering holidays, you will need to create a holiday Calendar and associate this calendar as the holiday calendar within Global Settings.

Examples

  • If you want to assign company of the Level 1 Owner to the Level 1 Customer field you would use the following:

@level1.companyid@=@level1.owner.companyid@;

Note: the Customer field can be be hidden, and will still populate. This is true for all Template/Type formulas.
  • To assign the owner of the company attached to the Level 1 item to the Person field use the following statement in the Level 1 template formula box:

@opportunity.peopleid@=@company.ownerid@

  • To populate the End Date standard field with the date the status is changed to Complete
@level1.enddate@=CASE WHEN "@level1.status@"="Complete" and "@level1.enddate@"="0000-00-00" THEN CURDATE() WHEN "@level1.status@"="Complete" and "@level1.enddate@"!="0000-00-00" THEN "@level1.enddate@" ELSE "0000-00-00" END;

Using a Level 2 Type Formula

In order to use this technique in the current example you will need to first enable the duration field in the Level 2 activity.

1. Click Settings.

2. Click Application Configuration.

3. Click the Activity Settings, Standard Fields link.

4. Enable the Duration field.

Uta129.png

5. Click the Save button.

6. Click Settings.

7. Click Activity Settings, Types tab.

8. Click the Addendum type.

9. Scroll to the Level 2 Formula.

Uta130.png

10. Enter the following formula:

Uta131.png

11. Click the Save button.

This expression will be executed any time a Level 3 item is saved.

12. Click the My Actions tab.

13. Click either Level 3 activity type.

14. Click the Save button.

15. Review the associated Level 2 activity.

Uta132.png

  • The duration is set to the sum of the durations of the associated Level 3 entities.
  • The same technique can be applied to the Level 1 Template formula to sum all the Level 2 durations.

Summing Level 2 to Level 1 using field ID:

Go to level 1 template and use the following format @level1.#123456#@=@level2.sum(#987763#)@

Summing Level 3 to Level 2 using field ID:

Go to level 2 template and use the following format @level2.#123456#@=@level3.sum(#678954#)@

Summing Level 3 to Level 2 base on a particular level 3 type:

Go to level 2 template and use the following format @level2.#50235#@=@level3.sum(#50291#):eventtype=8866@

where 50235 is the custom field where you want the value to store in, 50291 is the custom field that you want to sum and 8866 is the level 3 type ID,

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

You can use the Level 3 Type Formula field on the Level 3 Type page in a similar manner as for Level 2.

  • The advantage of using a Level 3 Type Formula instead of a Calculated field is that the calculations are done server side, rather than by the browser.
    Thus if you are doing a calculation on a value that needs to be populate or calculated on the same Level 3 item, this will avoid you having to save twice in order to get the final result.

The following example calculates the product of the values stored in fields named rate and quantity, and saves it in a field called Total with a field ID of 1731330:

@level3.total@=@level3.rate@*@level3.quantity@

or using the fieldid instead of fieldname

@level3.#123100#@=@level3.#123098#@*@level3.#123099#@

  • To refer to fields on the associated Level 1 or Level 2 records use level1.fieldname and level2.fieldname:

@level3.result@=@level1.hourly rate@*@level2.total quantity@

Using Template/Type Formulas for Text

If you are using a Template or Type formula to populate a field with text, rather than perform a calculation, you must enclose the statement on the right-side of the equal sign in quotations.

  • Any other text inside the quotation marks, including spaces, will be concatenated to the result with the variables:

@level1.name@="@level1.client first name@ @level1.client last name@";

Note that the space between the "client first name" and "client last name" variables will place a space between them in the resultant statement.

Example 2

@level1.name@="@level1.item 1@, @level1.item 2@ and @level1.item 3@";

Would result in a comma and space between the first 2 items and the word "and" between item 2 and 3.

IMPORTANT: You can use single quotes instead of double quotes, but if there is a single quote mark stored within one of the variables it will stop the concatenation of the string. Similarly using double quotes will provide incomplete results if one of the variables stores text that includes a double quote.

Using Conditional Statements

IF-Else Condition

Conditional statements can be used in formulas with the following structure:

@variable@=if(conditional statement,value if true,value if false);

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...

@level1.Success Criteria Count@=if('@level1.status@' IN ('Draft','Pending'),'@level1.xml.Success Criteria.Criteria.item.nodevalue@','@level1.Success Criteria Count@');

...use the following syntax...

@level1.Success Criteria Count@=if(? IN ('Draft','Pending'),?,?):=:@level1.status@:,:@level1.xml.Success Criteria.Criteria.item.nodevalue@:,:@level1.Success Criteria Count@;

Note that the criteria for the if statement are first referenced as ? marks, then the if statement is separated from the criteria values using :=:, then the separate criteria values are separated by :,:

Status-Trigger Condition

With this condition the template formula will only trigger if the status of the record is in the status which has been specified in the formula. Example:


{"formula trigger" : [{"status" : "LOI Draft"}]}::@level1.LOI Deadline Date@=IF("@level1.LOI Extend Deadline@"!="Yes","@level1.[#(?object=utaproviderL2::criteria=typename="Call")$LOI Submission Deadline Date$#]@","@level1.LOI Deadline Date@")


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:

@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

OR

@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

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'

@level1.enddate@=

if('@level1.status@'='Closed' and ('@level1.enddate@'= or '@level1.enddate@' regexp '@|0000'),

date_format(now(),'%Y-%m-%d'),

'@level1.enddate@');

  • At Level 3, if the contact assigned is a member of role 12345 put their name into the Consultant field. Otherwise use the Level 2 Owner for the Consultant field:

@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

Object syntax can be used in template formulas to loop through the level below and to assign a value or values based on some defined criteria.

The syntax differs from the basic object syntax in that the template formula must use double colons (i.e. ::) rather than a semi-colon to separate the criteria from the object.

An example of the syntax for this is shown below:



@level1.name@="@level1.[#(?object=activity::criteria=eventtype=3123) ~subject~ #]@"


In this example the value in the Subject standard field for the child level 2s that have an eventtype with value of 3123 will be stored in the Name standard field of the parent Level 1.





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.

{"formula trigger" : [{"type" : "type1"}]}::template formula 


Each condition can be a combination of type and status.

{"formula trigger" : [{"type" : "type1", "status" : "status1"}]}::template formula 


Each type/status condition can contain multiple type / status names , each delimited by comma.

{"formula trigger" : [{"type" : "type1, type2"}]}::template formula 


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'
{"formula trigger" : [{"type" : "Type 1,Type 3", "status" : "Submitted"}, {"type" : "Type 2", "status" : "Draft"}]}::@ level1.Current Date@ =now()

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 [1] for more examples of syntax.

UTA Provider syntax

  • If you are using template formulas to populate Level 1 fields with details on associated UTA providers then you must use the following syntax:

@level1.fieldname@=@level1.utaprovider.count(*)@;

  • If you are using template formulas to populate Level 1 fields with details on associated UTA consumer then you must use the following syntax:

@level1.fieldname@=@level1.utaconsumer.count(*)@;

  • If you are using template formulas to populate Level 2 fields with details on associated Level 1 UTA providers then you must use the following syntax:

@level2.fieldname@=@level2.utaprovider.count(*)@;

  • If you are using template formulas to populate Level 2 fields with details on associated Level 1 UTA consumers then you must use the following syntax:

@level2.fieldname@=@level2.utaconsumer.count(*)@;

Additional Information

  • 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