Changes

==General Information==

{{l1}}, {{l2}} and {{l3}} records can perform advanced calculations and logic within their Formula boxes.

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.

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''';

* 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@"''

* The resultant list of template formulas might look like this:

<!--The <end> syntax is only available with May 2014 upgrade-->

* See [[UTA_Standard_Field_Settings#Adding_a_field|enabling standard fields]] for information on enabling the field.

==Execution Sequence==

* The Formulas are calculated when a record is saved or saved in draft.

* Saving a Level 2 or Level 3 record will also initiate the formulas on the parent level(s):

* When a {{l1}} record is saved the formulas are executed in the following sequence:

<u>Level 2:</u>

* When a Level 2 record is saved the formulas are executed in the following sequence:

* '''Note''': The Level 1 Formula and Level 1 Template Formula are only executed if one of them refers to a Level 2 variable.

* When a Level 3 record is saved the formulas are executed in the following sequence:

* '''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:

|-

|-

|-

|-

|}

===Criteria===

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

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

===Counting and Adding Business Days===

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.

==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@;

* 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 populate the '''End Date''' standard field with the date the status is changed to '''Complete'''

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

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

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

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

'''Example 2'''

@level1.name@="@level1.item 1@, @level1.item 2@ and @level1.item 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==

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

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

You can also use the '''Case''' syntax:

===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@');

@level1.enddate@=

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

@level3.consultant@=if('@level3.contact.rolelist@' like '%12345%','@level3.contact.fullname@','@level2.owner.fullname@');

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

<br/><br/>

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

==UTA Provider syntax==

* If you are using template formulas to populate {{l1}} fields with details on associated UTA providers then you must use the following syntax:

==Additional Information==

* You can trigger execution of the Template / Type formulas against a group of records without opening and saving them using the [[Batch Update]] feature.

==See Also==

* [[Role Formula]]

* [[Ssescape|SSEscape - for using Template Formulas with text fields]]