Overview

Level 1, Level 2 and Level 3 records are capable of executing advanced calculations and logical operations within their Formula fields.

Formulas can be established in two distinct locations for each of the Levels within the UTA.

• The formulas specified on the Application Configuration page are applicable to all records saved at the designated Level.

• The formulas on the Template page (Level 1) or Type page (Level 2 and Level 3) pertain solely to records associated with the specified Template/Type.

Should you require calculations on fields linked to Level 3, while storing the results in fields at Level 2, you would utilize the Level 2 Formula field. You may employ any expression along with several built-in functions.

Furthermore, you may prefer to utilize Template Formulas instead of a Calculated field, as the computations are executed server-side rather than by the browser.

Consequently, if you are conducting a calculation on a value that must be populated or computed on the same Level 3 item, this approach will prevent the necessity of saving twice to obtain the final result.

On the Use of the Semicolon

When employing multiple template formulas, it is imperative to use a semicolon to separate them. For instance:

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

• If a semicolon appears within the template formula itself, multiple template formulas can be separated by the term.
• This is particularly beneficial when a template formula is utilized to consolidate multiple checkbox selections into a single checkbox as follows:
  @level3.Main Checkbox@="@level3.Main Checkbox@;@level3.Checkbox1@;@level3.Checkbox2@"
• In this scenario, conclude this template formula, as well as all other template formulas at this level (general and template/type formulas), with the term.
• The resultant list of template formulas may appear as follows:

@level1.total@=@level1.cost@*@level1.quantity@ 
@level3.Main Checkbox@="@level3.Main Checkbox@;@level3.Checkbox1@;@level3.Checkbox2@"
@level1.gst@=@level1.total@*0.05

Type Standard Field Must Be Enabled

IMPORTANT: Template formulas will function only if the type standard field is enabled.

• Refer to enabling standard fields for guidance on enabling the field.

Execution Sequence

• Formulas are computed when a record is saved or saved as a draft.

• The General formula on the Application Configuration page is executed first, followed by the Template/Type specific formula (if applicable).

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

Level 1:

• When a Level 1 record is saved, the formulas are executed in the following order:

Level 1 Formula-->Level 1 Template Formula

Level 2:

• When a Level 2 record is saved, the formulas are executed in the following order:

Level 2 Formula-->Level 2 Type Formula-->Level 1 Formula-->Level 1 Template Formula

• Note: The Level 1 Formula and Level 1 Template Formula will only be executed if one of them references a Level 2 variable.

Level 3:

• When a Level 3 record is saved, the formulas are executed in the following order:

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 will only be executed if one of them references a Level 3 variable.

Defined Functions

A variety of pre-defined functions are available:

@level2.#items#@=@level3.count(*)@

@level2.amount@=@level3.sum(amount)@

@level2.#most#@=@level3.max(amount)@

@level2.#average#@=@level3.avg(amount)@

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

Criteria

When utilizing these defined functions, you can restrict which records are included in the aggregated functions by applying 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

In addition to utilizing Javascript to count business days, there are two SmartSimple functions available within the L1, L2, and L3 template formulas to count and add business days to any Date field.

To count the number of business days between two date fields, employ the following function:

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

The 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 specified number of business days, utilize the following function:

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

The example below adds five business days to the start date.

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

Please note that in order to account for holidays, it is necessary to create a holiday Calendar and associate this calendar as the holiday calendar within Global Settings.

Examples

• To assign the 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 hidden and will still populate. This holds true for all Template/Type formulas.

• To assign the owner of the company associated with 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 when the status is changed to Complete, use the following:

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

Utilizing a Level 2 Type Formula

To implement this technique in the current example, it is necessary to first enable the duration field in the Level 2 activity.

1. Navigate to Settings.

2. Select Application Configuration.

3. Click on the Activity Settings, Standard Fields link.

4. Enable the Duration field.

5. Click the Save button.

6. Return to Settings.

7. Select Activity Settings, Types tab.

8. Click on the Addendum type.

9. Scroll down to the Level 2 Formula.

10. Enter the following formula:

11. Click the Save button.

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

12. Click the My Actions tab.

13. Select either Level 3 activity type.

14. Click the Save button.

15. Review the associated Level 2 activity.

• The duration is determined by the aggregate of the durations of the associated Level 3 entities.

• This same method can be employed in the Level 1 Template formula to accumulate all Level 2 durations.

Summing Level 2 to Level 1 using field ID:

Access the Level 1 template and apply the following format:

@level1.#123456#@=@level2.sum(#987763#)@

Summing Level 3 to Level 2 using field ID:

Access the Level 2 template and utilize the following format:

@level2.#123456#@=@level3.sum(#678954#)@

Summing Level 3 to Level 2 based on a specific Level 3 type:

Access the Level 2 template and employ the following format:

@level2.#50235#@=@level3.sum(#50291#):eventtype=8866@

In this instance, 50235 represents the custom field where the value will be stored, 50291 is the custom field to be summed, and 8866 is the Level 3 type ID.

Note: Currently, the system does not support summing from Level 3 to Level 1 using a custom field ID. Summation is limited to Amount and Duration fields.

Summing Level 2 to Level 2 based on a specific Level 2 type:

Access the Level 2 template and utilize the following format:

@level2.#50235#@=@level1.level2.sum(#987763#):eventtype=8860@

Utilizing a Level 3 Type Formula

The Level 3 Type Formula field on the Level 3 Type page can be utilized similarly to that of Level 2.

• The benefit of utilizing a Level 3 Type Formula over a Calculated field is that calculations are performed server-side, rather than by the browser.
  Consequently, if you are performing a calculation on a value that needs to be populated or calculated on the same Level 3 item, this approach will eliminate the necessity of saving twice to obtain the final result.

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

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

Alternatively, using the field ID instead of the field name

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

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

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

Utilizing Template/Type Formulas for Text

When employing a Template or Type formula to populate a field with text, rather than executing a calculation, it is essential to enclose the statement on the right side of the equal sign in quotation marks.

• Any additional text within the quotation marks, including spaces, will be concatenated to the result along with the variables:

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

It is important to note that the space between the "client first name" and "client last name" variables will introduce a space between them in the resulting statement.

Example 2

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

This would result in a comma and space between the first two items, and the word "and" between item two and three.

IMPORTANT: Single quotes may be used in place of double quotes; however, if a single quote mark is stored within one of the variables, it will interrupt the concatenation of the string. Similarly, using double quotes will yield incomplete results if one of the variables contains text that includes a double quote.

Utilizing Conditional Statements

IF-Else Condition

Conditional statements can be incorporated in formulas following this structure:

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

It is advisable to separate the criteria to prevent issues arising from variable values containing special characters such as quotation marks. For instance, 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@');

...employ 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, followed by the if statement being separated from the criteria values using :=:, and the separate criteria values are divided by :,:

Status-Trigger Condition

Under this condition, the template formula will only activate if the status of the record corresponds with the status 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 aforementioned example, the formula will trigger when the record is in the "LOI Draft" status.

Case-When Condition

The Case syntax can also be utilized:

@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 named locationpreference states "Home," place the value from the homeaddress field into Location. Otherwise, use the value from the worksaddress field for Location.

@level2.location@=if('@level2.locationpreference@'='Home','@level2.homeaddress@','@level2.workaddress@');

• If the Status is Closed AND no enddate is entered, populate the enddate with today's date. Otherwise, retain the existing enddate.

Note: To assess 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 assigned contact holds a role of 12345, insert their name into the Consultant field. Otherwise, utilize 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

Utilizing Object Syntax

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

The syntax differs from the basic object syntax in that the template formula must use double colons (i.e., ::) instead of a semicolon to separate the criteria from the object.

An example of this syntax is illustrated 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 possess an event type with a value of 3123 will be stored in the Name standard field of the parent Level 1.

Utilizing Trigger Points

Trigger Point syntax is employed to activate template formulas under specific conditions, such as triggering particular template formulas for designated UTA types and statuses.

Double colons will be utilized to separate the formula trigger from 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 encompass multiple type/status names, each delineated by a comma.

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

In the example below, the Current Date field will only be updated under two 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 reference the month in a date field, utilize Date_Format('@level2.fieldname@','%m'). Alternatively, utilize date('@level2.fieldname@') to extract yyyy-mm-dd.

UTA Provider Syntax

• When using template formulas to populate Level 1 fields with details on associated UTA providers, the following syntax must be employed:

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

• When using template formulas to populate Level 1 fields with details on associated UTA consumers, the following syntax must be employed:

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

• When using template formulas to populate Level 2 fields with details on associated Level 1 UTA providers, the following syntax must be employed:

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

• When using template formulas to populate Level 2 fields with details on associated Level 1 UTA consumers, the following syntax must be employed:

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

Additional Information

• Status cannot be modified using a Template/Type Formula.

• The Amount cannot be set using a Template/Type Formula.

• It is possible to trigger the execution of Template/Type formulas against a group of records without opening and saving them utilizing the Batch Update feature.

• When extracting data from a Text – Multiple Lines field, to eliminate line break codes such as <br>, utilize

@fieldname.value@