Microsoft Dynamics CRM 2011 localizes in 42 languages. On any base language org, one can install language specific MUI packs and have the UI reflect in the language of the user’s choice. When considering global organizations that use MUI packs, the currency issue also becomes central. CRM has multi-currency handling built into the system. By navigating to Settings>Business Management > Currencies, one can create different currencies and specify the exchange rate as the base currency of the org. In this blog we will look into the challenges of writing roll-up plug-ins that is multi-currency aware.
Let’s consider a simple scenario where a business is running a campaign to raise money with a set goal. It’s a global business and the money is being collected through its subsidiaries in different countries.
- To model and track the collections in CRM, it uses the out of box Campaign entity and a custom “Collection” entity.
- To get the current collected amount of a campaign. The individual collections need to be rolled up to the parent campaign and displayed in USD.
- To model the data the Campaign entity has two new custom fields:
- Goal: A money field that tracks the target collection amount.
- Collected: A money field that tracks the current collection amount.
- The custom entity “Collection” also has two new fields:
- Source: A string field to track where the money came from.
- Amount: A money field to track the amount that was collected.
Interestingly, CRM has some built in features to help. Since the “Amount” field of the Collection entity is a money field, CRM will internally add the following fields.
- Amount_base: Tracks the amount collected in organization’s base currency.
- Transaction currency: Tracks the currency in which the amount was collected.
- Exchange rate: Tracks the exchange rate that will be used to calculate the base currency value.
* The transaction currency and the exchange rate are created once per entity. All money fields on a single record would use the same transaction currency and exchange rate.
* The base currency field is created once for each money field.
Instead of rolling up the Amount fields on the Collection entities, whose values may be in different currencies, the plug-in can roll-up the Amount field’s amount_base values and set the parent Campaign entity’s Collected field’s collected_base.
Here we run into another challenge due to the collected_base field being read only. So we can read the amount_base field on the Collection entity, but cannot set the collected_base of the Collected field on the Campaign entity. Interestingly, the Campaign entity also has the exchange_rate and transaction_currency fields which can be retrieved to calculate the currency specific value from base field.
For cautious developers, there are two more challenges to overcome. First is to check if the currency exchange rate has changed. If the currency exchange rate has changed the CRM platform will fetch the latest exchange rate to calculate the base value for given money field. So, rather than using the exchange rate of the Campaign record we need to use the transaction currency lookup and fetch the current exchange rate when calculating the currency specific value.
The second and last challenge is regarding simultaneous updates. Since CRM is a server, it’s possible that two Collection records are being created simultaneously and their registered plug-ins are running in parallel. CRM does not provide a way to serialize the plug-in execution or lock individual records. Thus it’s recommended that we run the plug-ins asynchronously and do roll-ups using aggregate queries rather than adding and subtracting the individual Collection amount to the Campaign’s collected value. In other words, every time a Collection entity is created, updated or deleted, the asynchronous plug-ins will aggregate all the amount_base fields for all Collection records and update the Collected field on the parent Campaign with the value = (total * exchange rate); the exchange rate is being fetched based upon the transaction currency associated with the parent Campaign.
With all this we now have a good working multi-currency aware roll-up plug-in.