Calculation on Multiple Linked Records
This data type takes existing linked fields or records and performs calculations using the data. For example, you might want a field that holds a count of how many Opportunities are linked to a contact, or the average value of all linked contracts. You might want these calculated values as reference information for users, or you might need them to run background rules based on the number of related records.
You can use a pre-built calculation, such as sum, average, or count, or you can create your own formula to get the results you need. This data type can perform calculations only on related tables or links to selected fields with multiple values enabled (MVE).
In the Contracts table, the Number of Approvals Needed and Number of Approvals Completed are both calculations on multiple linked records.
Creating a Calculation on Multiple Linked Records
To create a new Calculation on Multiple Linked Records:
- Click Setup [Table], or click the Setup gear in the top-right corner, click Tables, select the table, and click Edit.
- Go to the Fields tab.
- Hover over New and select Calculation on Multiple Linked Records. This opens the wizard on the General tab.
On the General tab, identify the data you want to perform a calculation on.
- Enter a name for your new field.
- In the drop-down list, select the Related Table or Link to Selected Fields with MVE you want to perform a calculation on.
- In the Admin Notes field, enter a brief description of the field and its function, as well as any interaction with other fields or automation that other administrators might need to know.
On the Options tab, configure the calculation you want to perform.
- Choose one of the calculation options. Count calculates the total number of linked records, while the other options perform a calculation on a specific field.
- If you chose an option other than Count, choose the field used in the calculation. For example, you might choose Contract Amount if you wanted a Sum calculation of the total amount for all the linked contracts.
- Choose whether to treat null values as zero. This is important for certain calculations, like averages or minimum values. For example, if Contract Amount is left blank until the amount is agreed upon, you don't want to treat that as an amount of $0.00 in a calculation on average contract value.
- If necessary, you can select or create a saved search to narrow the records used in the calculation. For example, you might filter out inactive records.
- Choose whether to round the result and, if so, to what decimal place.
- Select or clear the checkbox for using child subtypes.
- Choose one of the options for if and when to recalculate the value:
- If desired, add visibility conditions to hide or show the field as needed.
The Permissions tab contains options similar to other wizards. You also have the option to replace these permissions with those copied from a field in the current table. To copy the permissions from a field in the current table, select it and click Copy Permissions.
Keep in mind that due to the nature of this data type, users can't actually edit the value of the calculation, even if they have permission to edit the field.
Now that you've defined the behavior, determine how the field should look.
- Enter the size of the input box, if you want to show one.
- Choose whether to use the user's locale or a specific locale for any language-dependent displays.
- Choose whether to use grouping in large numbers. For example, select Yes to show 1,000 instead of 1000. The separation character and grouping size are locale-specific.
- If desired, choose a currency symbol to display before or after the field.
- Choose whether to pad decimals where applicable. This is commonly used when displaying currency to make values more readable. For example, you would use this option to make sure to display $10.10 instead of $10.1.
- By default, a Calculate button appears to the right of the field so users can manually recalculate the results as needed. You can rename and reposition this button, or prevent it from appearing entirely.
- Complete the remaining standard field display options: input instructions, text alignment, and label and instruction placement.