Formulas

Formulas allow you to leverage advanced logic at the individual field level to manipulate or gain insight into all the data Tonkean is tracking. In addition to basic formulas such as finding certain values in a data source, combining strings, or carrying out basic arithmetic, you can create more advanced formulas that enable a deeper, more complex analysis of your data.

Formulas often act as small but critical logic in a workflow. Below are some common use cases that appear in many solutions:

  • Reformatting or restructuring data so it can be effectively used by another system or application, or simply making a machine-readable date human-readable

    example_date_reformat.png
  • Adding a fallback value to support dynamic actions, and handle situations where the field in question is empty

    example_fallback.png
  • Extracting important information from a field, such as an email domain from an email address

    example_domain_base_name.png

Create a Formula

There are two ways to create a formula: from the module builder screen and from the Business Reports screen.

Create a Formula from the Module Builder Screen

  1. From the module builder screen, select the Fields button on the module builder toolbar. The Fields panel displays.

    module_builder_fields_button.png
  2. In the Text Extraction & Manipulation card, select + Add Field. The Field Settings screen displays.

    add_formula_field.png
  3. Enter a title for the field in the Name field, then locate the Formula Editor section and select Root.

    field_settings_screen.png

Create a Formula from the Business Reports Screen

  1. From the Business Reports screen, select the add field icon, add_field.png.

    business_report_add_field.png
  2. In the dropdown, select Add New. The Field Settings screen displays.

    business_reports_add_new_field.png
  3. In the Where should the data for this field come from? section, select Formula.

    field_settings_formula_option.png
  4. Enter a title for the field in the Name field, then locate the Formula Editor section and select Root.

    field_settings_screen.png

Write a Formula Expression

  1. On the Field Settings screen, select the Root button. The Root menu displays. This menu allows you to select from a list of all available formulas, enter text manually, or select an existing field to use as a variable:

    • Select formula_calculator.png to search for or select from the list of all available formulas. This option displays by default after selecting Root.

    • Select formula_free_text.png to manually enter a static value (for example, text, number, or date).

    • Select formulas_field.png to search for or select from a list of existing fields.

  2. Select the fields in the Formula Editor to write and modify your formula. The options available and the required fields can vary depending on the formula you select and values you choose to include.

    See Available Formulas for detailed information on formula syntax and examples.

    formula_editor_write_expression.png
  3. Select Next to move on to the Display Settings section.

  4. The options available in the Display Settings section vary depending on your selections in the Data Settings section, but for each option, you can select + Add field label to enter a brief and descriptive label for your field. When finished, select Next to move on to the Display Conditions & Follow-Ups section.

    manual_display_settings_add_field_label.png
  5. If relevant, select Add Rule to create a rule that determines the display condition and status of the field, based on a specified value. These conditions are useful when you need a module to display differently based on the particular value entered for the field.

    manual_display_conditions_add_rule.png

    Use the conditions dropdown to set the desired condition. For example, if you want to use color to draw attention to a value greater than zero on a business report, you can set the condition to Greater than and the color to Green; this causes the color to change to green when the value is greater than zero. The default value is Equal to and the default color is Green.

    manual_display_conditions_add_rule_conditions.png

    For more information about the conditions and logical operators, see Conditions.

  6. When finished, select Next to continue to the Additional Information section.

  7. In the Additional Information section, there are several configurable fields:

    1. Field description - Enter a brief description of the field, if relevant. While the field label you create should be as clear and unambiguous as possible, this description can still be helpful for forms that contain multiple similar fields of where some ambiguity is unavoidable. This description displays beneath the field when used on a form.

    2. Field group - Enter a name to assign this field to a field group. This is designed for advanced use cases whereby you can use entire groups of related fields, identified by this field group name, in formulas and other operations.

    3. Who can update - Select who can update the field value. This is an important permissions setting at the individual field level, because it determines who can change a field value after it has been entered.

      It is common to use a combination of these settings across different fields on a single item, depending on your use case and specific needs. For example, if you're including several manual fields on a form that is the primary input source for a module, you may want some fields to be editable only by the user who submitted the form while other fields (such as a field that notes the status of the form) to be editable by the owner who is processing or approving the submission.

      • Only admins - Only solution administrators can update the field value. Select this option for fields whose values should never be updated except in rare cases, such as correcting errors.

      • Only item owners - Only users that have been assigned an item in the related business report can update the field value.

        By default, there is no owner assigned to an item, so setting this field to Only item owners without assigning a user to that item would result in the field value being locked to all users.

      • Any collaborator (default) - Anyone can update this field value. Select this option to allow anyone, including external collaborators, to update the field value. Because manual fields are so commonly used for externally-facing forms, this is the default selection.

      • Only item creator - Only the user that entered the value in the field (for example, the user that completed the form that contains the field) can update the field value. When using a field on a form, select this option to ensure that only the person who completed and submitted the form can update the field value to protect the integrity of the data.

      manual_additional_information.png
  8. When finished, select Save in the upper right.

Conditions

Where formulas define the ways that one or more fields are calculated or manipulated, conditions add another layer of logic that helps further define the interaction between different fields or data sources.

For example, you may want to compare one field against another, extract a string from a data source only when it contains certain words, or any number of other operations. Formulas and conditions used together provide an almost endlessly customizable way to manipulate and understand data. Below, each of these conditions is defined:

Logical Conditions

Equal To

Being of the exact same value compared to the specified value. This condition support numbers, text, dates, and most other field types.

Not Equal To

Being any value that differs from the specified value. This condition support numbers, text, dates, and most other field types.

Contains

The value contains the specified value. This condition is case-sensitive.

Not Contains

The value does not contain the specified value. your criteria. This condition is case-sensitive.

Match

The value matches the specified value, depending on the level of matching. This condition is not case-sensitive.

There are two levels of matching based on the desired looseness or strictness (spelling and grammar mistakes, nicknames, acronyms). Regardless of which level of matching you choose, the Match condition always checks each phrase individually against the specified value; if one phrase matches the specified value, it is considered a match.

The level of matching largely determines how many mistakes are tolerated while still considering a particular phrase a match.

Levels of matching:

  • Loose - Relatively close to the specified value. Misspellings and other mistakes are acceptable, depending on the length of the phrase. For example, with a Loose level of matching for the value "hello", "gello" would be considered a match because it contains only one mistake, but "grllo" would not be considered a match because it represents two mistakes that comprise too large a portion of the phrase (two out of five letters). The level of variance is dependent on the length of the evaluated phrase, with each evaluation allowing approximately the same percentage of variance for each phrase.

  • Strict - Needs to match almost one to one to the specified value. There is a very low tolerance for variance.

Does Not Match

The value does not match the specified value. This condition is not case sensitive.

There are multiple levels of matching based on the desired looseness or strictness (spelling and grammar mistakes, nicknames, acronyms). See above for level definitions.

Is Empty

The value does not have any contents (is null).

Is Not Empty

The value has any content (is not null).

Numerical Conditions

Between

Selects values within a given range. The values can be numbers, text, or dates. Note that “Between” is inclusive: beginning and end values are included.

Greater Than

When a quantity or number is bigger or larger than the second quantity or numbers.

Lesser Than

When a quantity or number is smaller or lesser than the second quantity or numbers.

Increased By

When your original value increases by an exact amount, or less than or more than a specific amount.

Decreased By

When your original value decreases by an exact amount, or less than or more than a specific amount.

Date and Time Conditions

In the Past

Takes the exact date and time when the trigger runs, and, based on the value you input, calculates (subtract) whether the criteria matches or not. Possible conditional values are: days, hours, and minutes.

Example: Close-Date = 1/23/2020 Timestamp of Trigger = 1/25/2020 Condition = {} in the past 4 days Result = Trigger will run because it’s only been 2 days since the trigger ran and the date of the Close-Date

In the Next

Takes the exact date and time when the trigger runs, and, based on the value you input, calculates (add) whether the criteria matches or not. Possible conditional values are days, hours, and minutes.

Example: Close-Date = 1/27/2020 Timestamp of Trigger = 1/25/2020 Condition = {} in the next 4 days Result = Trigger will run because the Close-Date is within the next 4 days

Before

Takes the date field you enter and determines if that date is before the specified value. The conditional value is a hard-coded date in mmm-dd-yyyy format.

After

Takes the date field you enter and determines if that date is after the specified value. The conditional value is a hard-coded date in mmm-dd-yyyy format.

In This

Takes the date field you enter and determines if it falls within the parameters of the selected conditions. Possible conditional values are week, month, and quarter.

Older Than

Takes the exact date and time when the trigger runs, and, based on the value you input, calculates whether the criteria matches or not. Possible conditional values are days, hours, and minutes.

Example: Close-Date = 1/23/2020 Timestamp of Trigger = 1/25/2020 Condition = {} older than 4 days Result = Trigger will not run because it’s only 2 days old

Further Than

Takes the exact date and time when the trigger runs, and, based on the value you input, calculates whether the criteria matches or not. Possible conditional values are days, hours, and minutes.

Example: Close-Date = 1/30/2020 Timestamp of Trigger = 1/23/2020 Condition = {} further than 4 days Result = Trigger will run because 1/30/2020 is further than 4 days away from 1/23/2020

In Past

A binary condition that takes the provided date value and determines if the specified value is in the past, based on the time the trigger runs. This condition uses the exact date and time of when the trigger runs.

In the Future

A binary condition that takes the provided date value and determines if the specified value is in the future, based on the time the trigger runs. This condition uses the exact date and time of when the trigger runs.

Available Formulas

Follow the links below for detailed information about each type of formula, including the specific format and examples, where relevant: