Adding Formulas to an Object Type

Overview

A Formula uses numeric and variable values (e.g., select lists, numeric or date fields, or workflow states) to generate Incident Severity, Estimated Damage, or Incident Likelihood. Formulas are added to an Object Type through a Relationship or Reference.

A formula appears on a form as a number, label (e.g., Low, Medium, High), numbers and labels, gauge, or as a formula card.


Related Information/Setup

For more information on formulas, see the following articles:


Navigation

  1. From the Home screen, click the Administration icon.

Administration Icon

  1. From the Admin Overview screen, click the Object Types tile on the Data Models section.

Object Types Tile

  1. From the Object Types screen, enter an Object Type Name in the Search field to narrow down the Object Types list. 
  2. Click the Object Type's Name you want to edit.

Click the Object Type's Name

  1. From the Edit Object Type screen, scroll down and select the Formulas tab.

Formulas Tab

  1. From the Formulas tab, click on the Add Formula button.

Add Formula Button

Adding a Formula to an Object Type

  1. From the Create New Formula screen, enter a formula name in the Name field (e.g., Estimated Vehicle Damage).

Name Field

  1. Click the Create button.
  2. The Formulas tab will appear, listing the newly created formula.
  3. Click the new formula to open the Edit Formula pop-up.

Edit Formula Pop-up

  1. (Optional) Enter a description documenting the Formulas internal use in the Description field.
  2. From the Variables section, Click the + Add Variable button.

+ Add Variable Button

  1. From the Variables section, select a Variable Type from the drop-down list. A Variable is a value in which the formula calculations are performed.
  • Field: After selecting the Field variable, the following field will appear:
    • Available Components: Select a field or formula from the Available Components drop-down field adding it directly to the Object Type.

Note:
Fields can be added to formulas after they are added to an Object T
ype or if they are associated through a relationship or reference. Only numeric fields, date fields, and select lists with numeric values are accepted. For more information, see the Fields article.

Variable Type = Field

  • Relationship: After selecting the Relationship variable, the following fields will appear:
    • Relationship: Select the Object Type Relationship from the drop-down list. Relationships connect two or more objects. The user must add a Relationship to an Object Type to appear on the Relationship drop-down list. See the Add Relationships to an Object Type article for further information on adding a Relationship to an Object Type.
    • Available Components: Select a field or formula from the Available Components drop-down field adding it directly to the Object Type.
    • Sub Type: Select a Sub Type from the drop-down list. Subtypes specify how the data from multiple objects are compiled, calculated, and displayed. For more information on Subtypes, see the Sub Type Table in the Variables, Operations, & Functions article. 
      • Array: Creates a set of values from the variable.
      • Sum: Calculates a total from the variable's set of values and returns a single number. Select list variables cannot use Sum Sub Types.
      • Count: The number of times a variable has been added to an object.
      • Average: Calculates an average number from the variable's set of values. Select list variables cannot use Average Sub Types.
      • Every: Checks if the variable contains a value on the objects in the relationship/reference.
      • Min: Calculates the lowest number from the variable's set of values. Select list variables cannot use Min Sub Types.
      • Max: Calculates the highest number from the variable's set of values. Select list variables cannot use Max Sub Types.

Variable Type = Relationship

  • Reference: After selecting the Reference variable, the following fields will appear:
    • Reference: Select the Object Type Reference from the drop-down list. References indicate that an object is connected to another object through a relationship. References are automatically created when a relationship is created.  For further information on adding a Relationship to an Object Type, see the Add References to an Object Type article.
    • Available Components: Select a field or formula from the Available Components drop-down field adding it directly to the Object Type.
    • Sub Type: Select a Sub Type from the drop-down list. Subtypes specify how the data from multiple objects are compiled, calculated, and displayed.

Variable Type = Reference

  • Property: After selecting the Property variable, the following field will appear:
    • Property: Select a Property type from the drop-down list:
      • Is Submitter Confidential: This property type creates a formula that compares the number of confidential submissions against the number of not confidential submissions for customers that use the Confidential Reporting Portal.

Variable Type = Property

  1. The system will automatically populate the Name field with the field or formula's unique ID by default.
  2. (Optional) Enter a Variable name in the Name field.

Warning:
Using a function name (Sub Type Name) in the name field will cause an error.

Variable Name

  1. (Optional) Enter a Variable description in the Description field.
  2. (Optional) Select the Treat empty values as Null checkbox to exclude blank objects from a formula calculation. For more information, see the Null Values in Formulas article.
  3. Click the Create button to add the variable. The system will perform the Syntax Validation function, and an error message will appear under the Formula field, reminding the user to add the variable name to the Formula field. 

Variable Error Message

  1. Click the Insert Variable button and select a Variable from the dropdown menu. The selected Variable will be added to the Formula field.

Insert Variable Button

  1. Click the Save Formula button. The Formula field will indicate if the Formula syntax is correct. If the Formula syntax is correct the Formula will be saved.

Formula Syntax

Note:
Click the Expand icon on the Formula field to open the Expandable screen mode.

Expand Icon

  1. Repeat steps 11 - 16 to add additional variables.
  2. Click the x icon next to the variable to delete the variable.

X Icon - Delete a Variable

  1. From the Formula section, enter a Formula using the variable name(s) you entered in the Name field under the Variables section. Include operators and functions in the Formula field (e.g., INCIDENTSE==3). For more information on Operators, see the Operators Table in the Variables, Operators, & Functions article.
  2. A system notification will appear under the Formula field, indicating that Your formula is not saved
  3. The Recalculate and Reformat button will be greyed out, preventing invalid formulas from being sent to the processing queue and causing a potential slowdown.

System Notification - Your Formula is Not Saved

  1. Click on the Save Formula button. The system will perform a Syntax Validation on the formula if the formula is:

Warning:
The Autosave function was removed from the 
Edit Formula pop-up. Changes to the Formula field require a user to click the Save Formula button.

 Changes not manually saved will be discarded, and the system will revert to the previously saved state.

  • Valid: A system notification will appear under the Formula field; Formula syntax is correct. Formula saved. The Recalculate and Reformat button will be active.

System Notification - Valid Formula

  • Invalid: A system notification will appear under the Formula field; Error Syntax error in part (char 1). The formula is not saved. The error will indicate the character (char) location of the error in the formula and that the formula is invalid and not saved. The Recalculate and Reformat button will be greyed out, preventing invalid formulas from being sent to the processing queue and causing a potential slowdown.

System Notification - Invalid Formula

  1. (Optional) Click the Recalculate and Reformat button to recalculate all the formulas in your organization.
  2. In the Display section, select a format from the Format drop-down list:
  • None: Uses no display formats.

Format = None

  • Numeric:  Allows the user to choose how the numbers will be displayed using the options on the table.
    • Num: Displays numbers in numeric format.
    • %: Displays numbers using percentage format.
    • $: Displays numbers using dollar format.
    • 0.00: Displays numbers using decimal format.
    • Layout: Previews the number format selected. 

Format = Numeric

  • Range as: 
    • None: The formula will display the numeric results only.
    • Label: The formula will display the range labels only (e.g., Low, Medium, High) in the color selected for that range.
    • Label and Result: The formula will display the numeric results and labels (e.g., Low – 1000) in the color selected for that range.
    • Result: The formula will display the numeric results only in the color selected for that range.
  1. If a user selects Label, Label and Results, or Results from the Range as drop-down list, the system will automatically add three default formula labels Low, Medium, and High.

Formula Labels

  1.  Click the Edit icon next to the Formula Label you want to edit.

Edit Icon

  1. The Formula Label fields will appear, allowing the user to edit the field values:
  • Color: Click the Color drop-down to reveal the color picker and select a new color for the label. You can also type a hex color into this field to select a color.
  • Label: Enter a new name for the label in the Label field.
  • Max Value: Enter a maximum value in the Max Value field, creating a numeric value range for the Formula Label.

Formula Label Fields

  1. Click the Save icon to save your changes.

Save Icon

  1. Click the Delete icon to delete a Formula Label.

Delete Icon

  1. Click Add Label button to add a new label.

Add Label Button

  1. If changes are made to the Formula Label on the Display section, you must click the Reformat button before the changes are displayed.

Reformat Button

  1. Click the x in the Edit Formula pop-up header to close the pop-up.

x Closes the Edit Formula Pop-up