Difference between revisions of "Template / Type Formula"

Revision as of 08:03, 26 August 2013

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 UTA Settings Page apply to all records saved at the specified Level.
• The formulas on the Template page (Level 1) or Type page (Levels 2 and 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.

Execution Sequence

• The Formulas are calculated when a record is saved.
• The General formula on the UTA Settings 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:

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

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

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.businessdays('Date1','Date2')

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

@level1.DateDifference@=SS_FUNC.businessdays('@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

• To count number of Companies of a certain type assigned to Level 1 use the following statement in the Level 1 template formula box:

@opportunity.#123456#@="@companies.count(*):rolename='EPS Partner'@";

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

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 on the Entity tab.

3. Click the Activity Settings, Standard Fields link.

4. Enable the Duration field.

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.

10. Enter the following formula:

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.

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

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

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

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

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;

Using List Syntax ([#(?object=... #])

The normal [#...#] syntax for listing multiple object such as listing all Level 2 objects attached to a Level 1 can be used.

• Because Template Formulas use semi-colons to delimit separate activities you must replace the semi-colon prior to the criteria and/or sort order with two colons:
• @level1.name@="@level1.[#(?object=activity::criteria=eventtype=1234) ~eventtype~ #]@"

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

Additional Information

• You cannot change the Status using a Template / Type Formula.

Note: Currently this works for level 2 only. 
You can use @level2.statusid@='12345'; to change the status of a level 2 using a template formula (Any workflows at that status are not triggered). 

• 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't have comments in formulas.

See Also

• The Musings of Chin - Template Formulas
• The Musings of Chin - SmartSimple's Order of Operations
• Role Formula
• Adding Business Days
• Counting Business Days between Two Dates

Setting Standard Fields for Level 3

Enabling the Security Matrix