Calculated Fields:
You can create a calculated field for any data type, except Multiple Line of Text, Image, or Lookup fields
Rollup fields benefits
Some examples of rollup fields include:
Workflows aren’t triggered by field updates
If you have created workflows in the past, you are aware that a workflow can be triggered on the update of a field. This is certainly true…with the exception of calculated and rollup fields.
Latest values not available in plugin create/update pipeline
A similar, yet certainly different, limitation is that plugins won’t trigger off of the update of a calculated or rollup field. While neither the workflow or plugin limitations are all that significant (at least in my mind) it is certainly something to be aware of as you are designing your solutions.
Can only have all ANDs or all ORs in Conditions
When creating conditions, you have the ability to use “and‘s” or “or’s”. However, if you need to use multiple “and’s” or “or’s”, you cannot combine them. For example, if you look at the screen shot below, you’ll see the logic of this condition is using only ‘and’s’. If you were to try and change one of them to an ‘or’, it would automatically change the other to an ‘or’.
Not available for offline
When operating Microsoft Dynamics CRM through Microsoft Outlook, you have the ability to operate it either connected (online mode) or disconnected (offline mode). When operating in offline mode, calculated fields do not calculate. Once you go online, however, they certainly will.
Can only go 1 level up in N:1 relationships
As was discussed in an earlier article, when using lookup fields in your calculated field scripts, you can only go up one level to pull data. For example, if I had an account named Company Z and its parent account was Company Y, I could grab data on the Company Y record. If, however, I wanted to grab a value from Company Y’s parent account while I was on the Company Z record, I could not capture any of that particular data in my calculated field script.
Rollup fields Considerations:
Rollup Fields:
Alternate keys
Alternate keys enable data integration in an efficient manner. Users can now define an attribute in a Microsoft Dynamics CRM entity to correspond to a unique identifier (or combination of columns) used by an external data store. Use this alternate key to uniquely identify a record in CRM in place of the primary key. This feature enhances the developer and customer experience by:
Reducing roundtrips to look up record IDs from other unique columns.
Increasing overall throughput of bulk data processes, especially with CRM Online.
Simplifying programming from external systems without CRM record IDs.
Change tracking
You can greatly improve efficiency of code that monitors changes to CRM data by using the RetrieveEntityChangesRequest message included in this release. The new change tracking feature provides a way to keep the CRM data stored in an external data source, synchronized in a performant way by detecting what data has changed since the data was initially extracted or last synchronized.
Upsert for updating CRM with external data
The new UpsertRequest message reduces the complexity involved with data integration scenarios by doing an update if the record already exists or a create if the record doesn’t exist. This is especially useful when you want to set the state of a record in CRM and don’t know if it already exists.
Optimistic concurrency
On a multi-threaded and multi-user system like Microsoft Dynamics CRM, operations and data changes often happen in parallel. A problem arises when two or more update or delete operations on the same piece of data happen at the same time. This situation could potentially result in data loss. New in this version is the ability for your applications to detect whether an entity record has changed on the server from when your application retrieved the record to when it tries to update or delete the record. Attempting to update or delete a record that has been changed by another user results in an error condition.
Custom actions in workflows or dialogs
Developers can still call custom actions using code, but now actions can also be called directly in workflows or dialogs.
You can create a calculated field for any data type, except Multiple Line of Text, Image, or Lookup fields
Rollup fields benefits
- Visual editing is easy. You can create rollup fields by using the Field Editor, just like you do when you create a regular field.
- Wide selection of aggregate functions. You can aggregate data by using the following functions: SUM, COUNT, MIN, MAX and AVG.
- Full filter support for aggregation. You can set various filters for the source entity or related entity while setting multiple conditions.
- Seamless integration with the user interface. You can include the rollup fields in forms, views, charts and reports.
- Rollup fields are solution components. You can easily transport the rollup fields as components between organizations and distribute them in solutions.
- Rollup fields and the calculated fields are complementary to each other. You can use a rollup field as a part of the calculated field, and vice versa.
Some examples of rollup fields include:
- Total estimated revenue of open opportunities of an account
- Total estimated revenue of open opportunities across all accounts in a hierarchy.
Workflows aren’t triggered by field updates
If you have created workflows in the past, you are aware that a workflow can be triggered on the update of a field. This is certainly true…with the exception of calculated and rollup fields.
Latest values not available in plugin create/update pipeline
A similar, yet certainly different, limitation is that plugins won’t trigger off of the update of a calculated or rollup field. While neither the workflow or plugin limitations are all that significant (at least in my mind) it is certainly something to be aware of as you are designing your solutions.
Can only have all ANDs or all ORs in Conditions
When creating conditions, you have the ability to use “and‘s” or “or’s”. However, if you need to use multiple “and’s” or “or’s”, you cannot combine them. For example, if you look at the screen shot below, you’ll see the logic of this condition is using only ‘and’s’. If you were to try and change one of them to an ‘or’, it would automatically change the other to an ‘or’.
Not available for offline
When operating Microsoft Dynamics CRM through Microsoft Outlook, you have the ability to operate it either connected (online mode) or disconnected (offline mode). When operating in offline mode, calculated fields do not calculate. Once you go online, however, they certainly will.
Can only go 1 level up in N:1 relationships
As was discussed in an earlier article, when using lookup fields in your calculated field scripts, you can only go up one level to pull data. For example, if I had an account named Company Z and its parent account was Company Y, I could grab data on the Company Y record. If, however, I wanted to grab a value from Company Y’s parent account while I was on the Company Z record, I could not capture any of that particular data in my calculated field script.
Rollup fields Considerations:
- You have to save the record before calculated field is updated.
- Only Calculated fields using all simple fields can be sorted.
- You cannot subtract two dates and determine the number of days, months, year’s difference they have between them.
- cannot use “+” or “&” when doing concatenations.
- can only use string values in my concatenations.
- Not all lookups available when looking across entities in calculated fields.
- Floating point numbers cannot be used in calculated fields.
Rollup Fields:
Rollup fields are used to perform record level aggregation from related records. For example, if you wanted to have a field on the Account that rolled up the Estimated Revenue for all Open Opportunities related to the Account, you could easily identify at the Account level what the Total Open Revenue was and then know what Accounts you should focus most of your time and energy on.
Similar to Calculated Fields, Rollup Fields do not support all data types. Rollup fields are only available for Whole Number, Decimal Number, Date & Time, and Currency fields. The table below shows how you can rollup child data using the rollup fields for the different data types.
Data Type
|
COUNT
|
MAX
|
MIN
|
SUM
|
Whole Number
|
x
| |||
Decimal
|
x
|
x
|
x
|
x
|
Currency
|
x
|
x
|
x
| |
Date & Time
|
x
|
x
|
Similar to Calculated Fields, when you’re creating your field, you’ll see an Edit button appear if you choose Rollup for Field Type.
Rollup field Limitations & Considerations- Rollup fields cannot be used as workflow trigger fields or in wait conditions.
- Modified on/Modified by fields do not get updated each time the rollup fields are re-calculated.
- Rollup fields cannot be audited.
- No more than 100 rollup fields per organisation and 10 per entity may be added.
- Child fields cannot also be rollup fields
- Child fields if marked as calculated cannot reference fields outside of the entity on which they reside.
- Roll-ups are not available for N:N relationships, they will only work with direct 1:N relationships.
- When a rollup field is added, two additional fields are added to store the ‘state’ and ‘last calculated’ date.
- Adding a rollup field will execute the calculation for all records in the system the first time the system job runs. This should be considered & tested of millions of records exist.
- If a hierarchical field is selected for the rollup field, it will calculate to a maximum depth of 10.
- Once a field is created, you cannot change the “Field Type”. This must be set at creation of the field.
- Roll-ups operate on the asynchronous process, and the roll-up is performed every hour. However, you can also trigger them via pressing the “refresh” icon in the actual roll-up field on the form:
| State Value | Description |
|---|---|
0
|
NotCalculated: Attribute value is yet to be calculated.
|
1
|
Calculated: Attribute value has been calculated per the last update time in <attribute SchemaName>_Date attribute.
|
2
|
OverflowError: Attribute value calculation lead to overflow error.
|
3
|
OtherError: Attribute value calculation failed due to an internal error, next run of calculation job will likely fix it.
|
4
|
RetryLimitExceeded: Attribute value calculation failed because the maximum number of retry attempts to calculate the value were exceeded likely due to high number of concurrency and locking conflicts.
|
5
|
HierarchicalRecursionLimitReached: Attribute value calculation failed because maximum hierarchy depth limit for calculation was reached.
|
6
|
LoopDetected: Attribute value calculation failed because a recursive loop was detected in the hierarchy of the record.
|
Alternate keys
Alternate keys enable data integration in an efficient manner. Users can now define an attribute in a Microsoft Dynamics CRM entity to correspond to a unique identifier (or combination of columns) used by an external data store. Use this alternate key to uniquely identify a record in CRM in place of the primary key. This feature enhances the developer and customer experience by:
Reducing roundtrips to look up record IDs from other unique columns.
Increasing overall throughput of bulk data processes, especially with CRM Online.
Simplifying programming from external systems without CRM record IDs.
Change tracking
You can greatly improve efficiency of code that monitors changes to CRM data by using the RetrieveEntityChangesRequest message included in this release. The new change tracking feature provides a way to keep the CRM data stored in an external data source, synchronized in a performant way by detecting what data has changed since the data was initially extracted or last synchronized.
Upsert for updating CRM with external data
The new UpsertRequest message reduces the complexity involved with data integration scenarios by doing an update if the record already exists or a create if the record doesn’t exist. This is especially useful when you want to set the state of a record in CRM and don’t know if it already exists.
Optimistic concurrency
On a multi-threaded and multi-user system like Microsoft Dynamics CRM, operations and data changes often happen in parallel. A problem arises when two or more update or delete operations on the same piece of data happen at the same time. This situation could potentially result in data loss. New in this version is the ability for your applications to detect whether an entity record has changed on the server from when your application retrieved the record to when it tries to update or delete the record. Attempting to update or delete a record that has been changed by another user results in an error condition.
Custom actions in workflows or dialogs
Developers can still call custom actions using code, but now actions can also be called directly in workflows or dialogs.
No comments:
Post a Comment