Multi Currency Goals in Dynamics CRM 2015

Recently we encountered a question from a customer who wanted to set and maintain goals in the currency of the user. By default, Microsoft Dynamics CRM only supports goals to be set and maintained in the base currency of the organization. The goals entity contains currency and money fields, but these cannot be modified on the forms. This blog describes the solution we made for this customer.

By Jasper Boere, senior consultant Microsoft Dynamics CRM.

For this example, we’ve set up an extra currency (GBP) with an exchange rate of 0.7281090000. The base currency of the sample organization is EUR. The solution we’ve created relies on the Workflow Essentials provided by GAP Consulting. Requirements for the solution:

  • Blank CRM 2015 environment;
  • Workflow Essentials solution provided by GAP Consulting.

The solution can be divided in 3 parts:

  • Modifications to the User entity;
  • Modifications to the Goal entity;
  • Workflows to get everything working.

Each part of the solution will be described in this blog post.

Modifications to the User entity

In the user entity, we’ve added the existing Currency-field to the form and we’ve created a decimal field to contain the exchange rate. Since it is not possible to retrieve the currency from the user settings, we needed to add the currency to the user entity. This is the only part that needs to be maintained by the system administrator.

 

Click to enlarge.

Note that the Exchange rate is read-only on the form. This value is copied from the Currency entity upon saving the user record with a real-time workflow. More on this workflow later.

Modifications to the Goal entity

Most of the modifications for the solutions are to be done on the Goal entity. For the solution, we’ve added 3 fields to the goal entity:

  • Target (Money) (LCY)
  • Actual (Money) (LCY)
  • Today’s Target (Money) (LCY).

Note that we’ve chosen to use decimal fields instead of currency fields, since we do not want the currency_base-fields to be created.

On the Goal-form we replaced the default Target (Money) and Actual (Money) with the newly created fields:

 

Also, we’ve updated the View and Chart to contain these newly created fields instead of the default views:

The values in the background are still calculated in EUR, the base currency of the organization, which can be seen by opening the default view and chart:

Workflows to get everything working

Basically, there are 4 workflows needed to get everything working:

  1. To copy the exchange rate from the currencies to the user entity
  2. To distribute changes in the exchange rate to all underlying user records.
  3. To convert the target amounts in LCY to the base currency
  4. To convert the actual amounts in base currency to the currency of the goal

1. The first one is easy. Since we’ve made the currency field on the user mandatory, it has to be entered. Therefore, the workflow is set to be triggered on the change of this field. This workflow also has to run as a child workflow of the second workflow when the exchange rate of the currency changes.

2. The second workflow starts when the exchange rate of a currency changes. The exchange rate is then distributed to all linked user records. This workflow uses the DistributeOneToMany functionality from the GAP solutions Workflow Essentials:

3. The workflow to convert the Target amount in LCY to the base currency is set up as follows:

Basically, it takes the newly created field Target (Money) (LCY) and divides it by the exchange rate set on the user record of the goal owner. It then puts the outcome of this calculation in the original Target (Money) field on the goal entity.

4. The workflow to convert the actual amounts to the Local Currency works the other way around. It takes the actual values in the base currency, multiplies it by the exchange rate, and stores the outcomes in the newly created Actual (Money) (LCY) fields:

Conclusion

Since there are already currency fields in the system for the goal entity, it seems like Microsoft supports the multi-currency goals. However, when you start to look into it in more detail, it turns out that the currency on the goal cannot be modified and only the organization base currency can be used.

With some basic configuration and workflows and with some help of a third party solution, we managed to serve a functionality to the client that cannot be done with standard CRM. You can download the fully functional working solution from this blog as well.

Happy CRM-ing!


Terug naar overzicht