Type Formulas Reference

Type formulas define the functions, syntax, and rules available at Level 1, Level 2, and Level 3 in a UTA/module.

Defined Functions

The following functions are available in type formulas.

↑ Back to top

Limitations and Rules

Type formulas are subject to the following limitations and rules.

• Status cannot be set with a type formula.
• The Amount standard field cannot be set with a type formula.
• Type formulas can be run against a group of records without opening and saving each one by using the Batch Update feature.
• To remove line break codes such as <br> when reading from a Text - Multiple Lines field, use @fieldname.value@. The new syntax equivalent is {{fieldname.value}}.

↑ Back to top

Criteria Syntax

Criteria restrict which records are included in an aggregate function. Add the criteria after a colon.

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

↑ Back to top

Business-Day Functions

Two functions count and add business days to a Date field in Level 1, Level 2, and Level 3 type formulas. JavaScript can also count business days.

To count the business days between two date fields:

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

This example counts the business days between the Start Date and End Date:

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

To add a set number of business days to a date, where x is the number of business days:

SS_FUNC.addbusdays('Date', x)

This example adds five business days to the start date:

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

↑ Back to top

Aggregation by Field ID and Type

These patterns sum custom fields across levels using field IDs, with an optional type restriction.

Example: A formula that sums a Level 2 field to Level 1 using a field ID.

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

Example: A formula that sums a Level 3 field to Level 2 using a field ID.

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

Example: A formula that sums a Level 3 field to Level 2 for a specific Level 3 type.

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

In the example above, 50235 is the custom field that stores the value, 50291 is the custom field being summed, and 8866 is the Level 3 type ID.

Example: A formula that sums a Level 2 field to Level 2 for a specific Level 2 type.

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

↑ Back to top

Common Examples by Level

The following examples show common type formulas at Level 1 and Level 3. Level 2 aggregation examples appear in Aggregation by Field ID and Type.

Example: A Level 1 formula that removes special characters from the record name.

@level1.name@=IF("@level1.name.length@"=0,LEFT(REGEXP_REPLACE("<ssEscape>@level1.name@</ssEscape>", '{(\[^}\]*)}', ''),100),"<ssEscape>@level1.name@</ssEscape>");

Example: A Level 1 formula that assigns the company of the Level 1 Owner to the Level 1 Customer field.

New syntax:

{{level1.companyid}}={{level1.owner.companyid}};

Old syntax:

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

Example: A Level 1 formula that assigns the owner of the linked company to the Person field (enter in the Level 1 type formula box).

New syntax:

{{opportunity.peopleid}}={{company.ownerid}}

Old syntax:

@opportunity.peopleid@=@company.ownerid@

Example: A Level 1 formula that populates the End Date standard field with the date the status changes 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;

Example: A Level 3 formula that multiplies rate by quantity and stores the result in Total.

New syntax:

{{level3.total}}={{level3.rate}}*{{level3.quantity}}

Old syntax:

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

Example: A Level 3 formula that performs the same calculation using field IDs instead of field names.

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

Example: A Level 3 formula that references fields on the related Level 1 or Level 2 record.

New syntax:

{{level3.result}}={{level1.hourly rate}}*{{level2.total quantity}}

Old syntax:

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

↑ Back to top

Text Formulas

To populate a field with text instead of a calculation, enclose the right side of the equal sign in quotation marks. Text inside the quotation marks, including spaces, is joined to the result along with the variables.

Example 1: combine the client first and last name.

New syntax:

{{level1.name}}="{{level1.client first name}} {{level1.client last name}}";

Old syntax:

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

The space between the two variables produces a space between the names in the result.

Example 2: list three items.

New syntax:

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

Old syntax:

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

This produces a comma and space between the first two items and the word and between the second and third.

↑ Back to top

Conditional Statements

Type formulas support conditional logic using IF-Else, status-trigger, and Case-When structures.

IF-Else Condition

Add conditional logic to a formula with this structure:

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

Separate the criteria to avoid problems when a value contains special characters such as quotation marks. Instead of:

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

use:

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

The criteria are referenced first as ? marks. The if statement is separated from the criteria values with :=:, and the criteria values are separated with :,:.

↑ Back to top

Status-Trigger Condition

A status-trigger formula runs only when the record's status matches the status named in the formula.

{"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 this example, the formula runs when the record is in the LOI Draft status.

↑ Back to top

Case-When Condition

The Case syntax is also supported:

@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

↑ Back to top

Conditional Statement Examples

Example: At Level 2, if locationpreference is Home, place homeaddress in Location; otherwise use workaddress.

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

Example: If Status is Closed and no enddate is entered, set enddate to today; otherwise keep the existing enddate.

@level1.enddate@=if('@level1.status@'='Closed' and ('@level1.enddate@'= or '@level1.enddate@' regexp '@|0000'),date_format(now(),'%Y-%m-%d'),'@level1.enddate@');

Example: At Level 3, if the contact's role list contains 12345, insert the contact's full name in Consultant; otherwise use the Level 2 Owner's full name.

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

Example: At Level 2, set Funding Group from the 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

↑ Back to top

Object Syntax

Object syntax iterates through the level below and assigns values based on criteria. In a type formula, use double colons (::) instead of a semicolon to separate the criteria from the object.

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

In this example, the Subject standard field from the child Level 2 records with event type 3123 is stored in the Name standard field of the parent Level 1.

↑ Back to top

Trigger Points

Trigger point syntax runs a type formula only for specific types and statuses. Double colons separate the trigger from the formula.

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

Each condition can combine a type and a status:

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

Each type or status condition can list multiple names separated by a comma:

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

In this example, the Current Date field updates only when the type is Type 1 or Type 3 and the status is Submitted, or when the type is Type 2 and the status is Draft:

{"formula trigger" : [{"type" : "Type 1,Type 3", "status" : "Submitted"}, {"type" : "Type 2", "status" : "Draft"}]}::@level1.Current Date@ =now()

↑ Back to top

Date Manipulation

To reference the month in a date field, use Date_Format('@level2.fieldname@','%m'). To extract the date as yyyy-mm-dd, use date('@level2.fieldname@').

↑ Back to top

UTA Provider and Consumer Syntax

Use this syntax in type formulas to populate fields with details about related UTA providers and consumers.

Example: A Level 1 formula that populates a field with related UTA provider details.

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

Example: A Level 1 formula that populates a field with related UTA consumer details.

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

Example: A Level 2 formula that populates a field with related Level 1 UTA provider details.

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

Example: A Level 2 formula that populates a field with related Level 1 UTA consumer details.

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

↑ Back to top

Semicolon Usage

Separate multiple type formulas at the same level with a semicolon. For example:

New syntax:

{{level1.total}}={{level1.cost}}*{{level1.quantity}};{{level1.gst}}={{level1.total}}*0.05;

Old syntax:

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

When a semicolon appears inside a formula itself, such as a formula that combines several checkbox selections into one checkbox, end that formula and every other formula at the level (general and type) with the semicolon term:

New syntax:

{{level3.Main Checkbox}}="{{level3.Main Checkbox}};{{level3.Checkbox1}};{{level3.Checkbox2}}"

Old syntax:

@level3.Main Checkbox@="@level3.Main Checkbox@;@level3.Checkbox1@;@level3.Checkbox2@"

The resulting list of formulas may look like this:

New syntax:

{{level1.total}}={{level1.cost}}*{{level1.quantity}}

{{level3.Main Checkbox}}="{{level3.Main Checkbox}};{{level3.Checkbox1}};{{level3.Checkbox2}}"

{{level1.gst}}={{level1.total}}*0.05

Old syntax:

@level1.total@=@level1.cost@*@level1.quantity@

@level3.Main Checkbox@="@level3.Main Checkbox@;@level3.Checkbox1@;@level3.Checkbox2@"

@level1.gst@=@level1.total@*0.05

↑ Back to top