In this article, we learn about Calculated and Roll-Up Fields in Microsoft Power Platform.
By now you are already familiar with how to customise entities by adding fields of various data types where a value is stored directly and is not based on a formula or calculation. These fields are referred to as Simple fields.
When the value can be calculated or aggregated from related data, the platform supports Calculated and Rollup fields. The following table contains information about the field types available in Power platform:
| Field Type | Description | Example |
|---|
| Simple Field | Contains data that is not based on a formula. | First name, Birthday, Email, Price, Status |
| Calculated Field | Contains calculations that use fields from the current entity or related parent entities. | Full name: First name + Last Name Weighted Revenue: Estimated revenue x opportunity probability |
| Rollup Field | Contains an aggregate value computed from the records related to a record, or a value computed over a hierarchy. | Potential revenue: Sum of revenues of all open opportunities for a given account. Total open cases: The count of all open cases for a given customer |
Not all data types are supported for
Calculated and
Rollup fields as some operations simply do not apply. For example, it is not possible to perform any rollup operation over a text field. The following table summarises data types available for different field types:
| Data Type | Simple | Calculated | Rollup |
|---|
| Single Line of Text | X | X | |
| Multiple Lines of Text | X | | |
| Whole Number | X | X | X |
| Floating Point | X | | |
| Decimal | X | X | X |
| Currency | X | X | X |
| Date Time | X | X | X |
| Option Set/Two Options | X | X | |
| Multi-Select | X | | |
To create a Calculated or Rollup field, start creating a simple field by entering the Display Name and the Name and selecting one of the supported data types. Then press Add and select either Calculation or Rollup.
Note Once a field type is selected, you will get prompted to save the information. You cannot change an existing field type, i.e. you will not be able to change the field type once the field is defined as either simple, calculated, or rollup.
Common Characteristics and Limitations
Calculated and rollup attributes share some common elements and characteristics:
- They’re read-only.
- They’re not specific to the user. The calculation is performed using a system user account, so the values may be based on records that the user doesn’t otherwise have privileges to view, such as attributes that have field-level security enabled.
- Rollup and calculated fields are complementary to each other. You can use a rollup field as a part of the calculated field, and vice versa.
- Calculated and rollup fields do not trigger workflows (including Microsoft Flows) or plug-ins, nor do they trigger duplicate detection rules. Changes to the fields are not captured in audit.
- Modifiedby and modifiedon fields are not affected by calculated or rollup fields.
Calculated Fields
Calculated fields let you automate manual calculations by specifying an expression that calculates the field value. Expressions may include existing fields from the current entity or related parent entities, and the built-in functions such as ADDHOURS, ADDDAYS, DIFFINDAYS, CONCAT, TRIMLEFT, NOW, and many others (the list of available functions is expanded from release to release). The expression editor supports intellisense on functions and field names. Expressions may also include other calculated or rollup fields.
Related parent entity fields can be referenced as entity name.field name, for example ParentAccountid.Telephone1.
Conditions can be added to support branching and multiple conditions. The logical operations include AND and OR operators.
Limitations
You should be aware of certain conditions and limitations when working with calculated fields:
- Calculations are not stored valuesb – the calculation is performed during the retrieve proces (i.e. values will not be updated on a form until the form is refreshed). If users expect to see real-time changes as they edit data involved in the calculation, calculated fields would not be a good solution.
- A calculated field cannot directly or indirectly refer to itself.
- You can’t sort on calculated fields that use logical (e.g. address field), related record, or other calculated fields.
- Views and visualisations can use up to 10 unique calculated fields.
Rollup Fields
Rollup fields are designed to help users obtain additional data insights by calculating key business metrics. A rollup field contains an aggregate value computed over the records related to a specified record, such as open opportunities of an account. You can also aggregate data from the activities directly related to a record, such as emails and appointments, and activities indirectly related to a record via the Activity Party entity (for example, meeting attendees).
In more complex scenarios, you can aggregate data over the hierarchy of records. For example, if an account has a hierarchical self-relationship defined, you can calculate revenue across all sub-accounts in a hierarchy.
Definition of a rollup field includes the following:
- Source entity section, where you specify the entity for which the rollup field is defined and whether you aggregate over a hierarchy. You can add filters with multiple conditions to specify the records in the hierarchy you want to use for rollup.
- Related entity section, where you specify the entity over which you aggregate. This section is optional when you choose to rollup over the hierarchy on the source entity. You can add filters with multiple conditions to specify which related records to use in the calculation. For example, you can include the revenue from only the open opportunities where an annual revenue is greater than $1000.
- Aggregate section, where you specify the metric you want to compute. You can choose available aggregate functions, such as SUM, COUNT, MIN, MAX, or AVG.
The example below defines Active Quoted Value field for the Account entity. That field is calculated as the SUM of Total Amount field for all quotes with Active status where the account is a potential customer.
Updating Rollup Values
Rollup fields are calculated by scheduled system jobs that run asynchronously in the background. The jobs can be viewed and managed by an administrator. There are two types of system jobs:
- Mass recalculation that runs once per rollup field 12 hours after you created or updated a rollup field.
- Incremental recalculation job that runs per entity and recalculates all rollup fields for that entity by processing the records that were created, updated or deleted since the last recalculation. The default maximum recurrence setting is one hour.
It is possible to manually recalculate individual rollup fields by choosing the Refresh icon next to the rollup field on the entity form. User must have write access to the record on which refresh is requested. The maximum number of records during the manual rollup refresh is limited to 50,000 records, and the error is displayed if this limit is exceeded.
Limitations
You should be aware of certain conditions and limitations when working with rollup fields:
- A rollup over a rollup field is not supported.
- A rollup can’t reference a calculated field that uses another calculated field, even if all the fields of the other calculated field are on the current entity.
- The rollup can only apply filters to the source entity or related entities, simple fields, or non-complex calculated fields.
- Rollup fields are updated on schedule and not in real time. If a user has sufficient privileges, they can force manual recalculation of a rollup field in user interface.
- Rollup fields are limited to 10 per entity and 100 per organisation.
Tips
- Function NOW() can’t be used in conditions, but a calculated field with the value of NOW() can.
- You can use calculated fields as a simple alternative to Quick View forms to make related record values available. For example, a contact may include calculated field Office Phone that is set to the parent account’s telephone number.
- To avoid potential data overflow when performing rollup of a whole number field (for example, File Size), use decimal data type with precision 0 for the target rollup field.
No comments:
Post a Comment