Rule Formulas allow you to use a combination of exports to set a value. Formulas are supported with the following Rule Types:
Field
Application Header
Person Header
Person Index
Dataset Index/Header
Dataset Username
Dataset Name
This article will first explain how Formulas work, as the usage is exactly the same regardless of the Rule Type, and then it will provide some examples.
A note about field rules
Rule formulas do not work with all fields. In general, prompt-driven fields cannot be set using a rule formula. The exception are fields that use country, language, state, or user as their prompt setting. These special prompts are discussed later in the article.
How Formulas Work
When the Action of a Rule is set to Replace Values from Formula, you are presented with the ability to add Exports and Joins.
You can add base joins (one-to-one joins) and any direct exports or subquery exports as needed. Exports must have a name with no spaces or special characters.
To use the exports in the formula, add an @ symbol in front of the export name. For example: @ref_id.
Click Join or the subquery join icon to join to create a one-to-one base join. Unlike in the query tool, base joins will appear above the exports, but they still represent a one-to-one join and will only return data from one related row.
Simple Formula Rules
In many cases, the formula can be constructed entirely within a subquery export.
There many benefits to this approach:
You might not need to write a formula at all. For example, by using the Coalesce or Concatenate output setting.
You can configure your subquery export just like you would in a query, giving you familiarity and minimizing possible mistakes.
It can simplify the logic since only one item is needed.
A good example is a Person Header rule. This type of rule can customize the text that appears under a person’s name when using omni search.
The value shown in the screenshot is the default. Here is how it would be built within a subquery export:
Complex Formula Rules
Rule formulas can use the exact same formula elements as formulas in queries. The linked article discusses formula formatting and common use cases.
Example: Performing Calculations
Rules can perform mathematical operations like addition (+), subtraction (-), multiplication (*), division (*), and modulo (%). When writing mathematical formulas, always confirm the data type of each element to make sure that they will be treated as numbers (Integer or Real).
Mathematical operations can be performed with a simple formula by using a subquery export. For example, if we have a Scholarships entity, we can Sum all of the values in the Amount field:
However, we may have data in more than one place. In this example, we also have a flat field that stores the value of the Presidential Scholarship, which is not included in the Scholarships entity. After adding it as an export, we have the following formula:
Make sure to choose the correct Format Type for each export. A numeric format (Real, Integer, or Money) must be chosen in order for the calculation to work.
Special Prompt Values
Most prompt-driven fields can’t be set with a rule formula, since they store the GUID of the selected prompt. The following prompts are stored as text, allowing them to be set via formula:
country fields store the two-letter country code (e.g. “US”). Although these are not GUIDs, the codes can be found using the “GUID” export on the System > World - Country base.
language fields store the two-letter language code (e.g. “EN”). These codes are the Index values on the “language” prompt, which can be queried using the System > Prompt base.
state fields store the two-letter state code (e.g. “OR”). These codes can be found using the “Alpha2” export on the System > World - Region base.
user fields store usernames. These can be found using the “Username” export on the Related > User base.
Example: Staff Assignment by Formula
In this example, a translation code exists to associate states with Slate users. We’ve set this up using the translation code source format to easily populate our values:
In a rule with a type of Field, scroll to the Field dropdown and select your Staff Assigned field. In the Action dropdown, select Replace Values from Formula. To create an export to display the student’s state, we’ll make a base join to Address by Rank Overall and choose the rank 1 address. Next, we’ll add the export for Region:
We now need to edit this export to give it a machine-friendly name (no spaces) and apply the translation code:
Our rule is now almost complete. We just need to add the export to the formula using the @ symbol:
That’s it! Our rule uses the student’s address, looks up the relevant staff member using the translation code, and sets the Staff Assigned field - all with a very simple formula and no subqueries needed.