Keeping the Exchange Rates of Currencies in Dynamics CRM Up-to-date

Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkedin

Dynamics CRM Developer Series:

Attention  CRM/.NET Developers: This post will review how to create a console application which uses Windows Task Scheduler to update currency records on a nightly basis in Dynamics CRM.

AdobeStock_80138094

The Situation

Currency fields in Dynamics CRM come in three parts.

1)      The main field, which essentially allows a decimal number.

2)      A Currency lookup field, which is used to select the currency of the transaction.

3)      A “(Base)” field, which is calculated from the main field divided by the exchange rate of the selected currency.

For example, let’s say your Organization’s base currency is CAD, and you are making a transaction in USD. You would select USD from the lookup, and enter the amount of the transaction (in USD) in the main field. For example, you enter $100. If the exchange rate (which is the value of a field on the Currency record) is 0.8, then the “(Base)” field will automatically be set to “$125” when the record is saved.

The Problem

This is a very handy feature when dealing with multiple international currencies as it lets users get an idea of how much the transaction is worth in both currencies at a glance. However, that exchange rate is set when a user initially adds the Currency record to CRM and must be updated manually. The problem, of course, is that exchange rates happen to vary over time and it would be quite tedious to have someone manually update all the exchange rates at set intervals. This is where the power of automation can come in handy, this blog will describe how to programmatically update the exchange rates of all currencies in Dynamics CRM at set intervals.

Dynamics CRM offers many powerful features and functions, but scheduled tasks is not one of them. Many other blogs have suggested ways of setting up recurring workflows that trigger themselves over and over, or plugins that trigger when creating/deleting records, along with scheduled bulk deletion jobs, but these seem more like Rube Goldberg contraptions than robust solutions. For example, you can run into recursion limits with workflows, and if something disrupts the perpetual motion machine it would require someone with knowledge of the solution to restart it cleanly.

The Solution

We decided to create a console application which would leverage the CRM client SDK to update the currency records based off Fixer.io’s exchange rate API on a nightly basis by scheduling a task using the Windows Task Scheduler.

The connection string and organization name are stored in a config file so those values can be updated without needing to recompile. Note that if the application is being run as an AD user who is a CRM user, the connection string need only be “Url=https://crm.company.com”, but if the application is being run remotely as a non-CRM user, then the connection string will need to specify the login credentials of a CRM user, such as “Url=https://crm.company.com; Username=DOMAIN\username; Password=password;”. More info on connecting to Dynamics CRM from .NET and the format of connection strings can be found here: https://msdn.microsoft.com/en-us/library/gg695810(v=crm.7).aspx

 

————————————————-

Note for CRM Online: This is most straightforward for On-Premise deployments, however I believe that this same approach would work for CRM Online, however the console application and the Scheduled Task would have to be deployed on some server that is controlled by your organization. Since this uses the Client SDK, as long as the .dlls are copied into the output folder then it should be possible to run the application successfully from any internet connected computer. However, as mentioned above, CRM user credentials would have to be stored in the app.config file and all appropriate security measures should be taken.

——————————————————-

The app.config:

The application code first establishes a connection to the CRM organization:

string _ConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
Microsoft.Xrm.Client.CrmConnection connection = CrmConnection.Parse(_ConnectionString);
using (OrganizationService service = new OrganizationService(connection))
{
OrganizationServiceContext serviceContext = new OrganizationServiceContext(service);
}

Next, we need to determine the Organization’s base currency:

string _OrgName = ConfigurationManager.AppSettings["OrganizationName"];
var orgInfo = (from org in serviceContext.CreateQuery("organization") where ((string)(org["name"])).Equals(_OrgName) select org).FirstOrDefault();
EntityReference baseCurrencyRef = orgInfo.GetAttributeValue("basecurrencyid");
ColumnSet cols = new ColumnSet(new String[] { "currencyname", "isocurrencycode", "exchangerate" });
_BaseCurrency = service.Retrieve("transactioncurrency", baseCurrencyRef.Id, cols);
Print("Base Currency ID: " + _BaseCurrency.Id);
Print("Base Currency Name: " + _BaseCurrency["currencyname"]);
Print("Base Currency Code: " + _BaseCurrency["isocurrencycode"]);

Then, we pull the list of currency IDs on the CRM Server:

var currencies = (from currency in serviceContext.CreateQuery("transactioncurrency") select currency.Id).ToArray();

Then we iterate over that list, retrieving the entity:

ColumnSet cols = new ColumnSet(new String[] { "currencyname", "isocurrencycode", "exchangerate" });
for (int i = 0; i < currencies.Length; i++)
{
Entity currency = service.Retrieve("transactioncurrency", currencies[i], cols);
String name = currency.GetAttributeValue("currencyname");
String code = currency.GetAttributeValue("isocurrencycode");
Decimal exchangeRate = currency.GetAttributeValue("exchangerate");
Print(String.Format("Processing currency \"{0}\". Code:{1}. Current rate:{2}", name, code, exchangeRate));
}

Skipping the base currency with:
if (currency.Id != baseCurrencyRef.Id)

We then request the latest exchange rate from Fixer.io’s API. It is very straightforward. For example:

Requst: GET https://api.fixer.io/latest?base=CAD&symbols=USD

Response: {“base”:”CAD”,”date”:”2016-05-16″,”rates”:{“USD”:0.77381}}

Note: Fixer.io updates their rates on a daily basis. If more frequent updates are required, a different financial API provider would be necessary.

We build our request URI as:

Uri uri = new Uri(String.Format("http://api.fixer.io/latest?base={0}&symbols={1}", _BaseCurrency["isocurrencycode"], code));

And we query that URI with a WebClient:

using (WebClient wc = new WebClient())
{
wc.Headers[HttpRequestHeader.ContentType] = "application/json";
wc.Headers[HttpRequestHeader.KeepAlive] = "false";
wc.Encoding = System.Text.UTF8Encoding.UTF8;
result = wc.DownloadString(uri);
Print("\tRequest: GET " + uri.ToString());
Print("\tResponse: " + result);
}

The response is JSON, so the most straightforward way to parse it is with the Json.NET (Newtonsoft.Json) NuGet package:
var jsonResponse = JObject.Parse(result);
exchangeRate = Decimal.Parse(jsonResponse["rates"][code].ToString());
Print("\tNew exchange rate: " + exchangeRate);

Then we write that new value back to the Entity:

currency["exchangerate"] = exchangeRate;
service.Update(currency);

Putting it all together, with some error catching:

(Note: Download the full code here)


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ServiceModel;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Client;
using Microsoft.Xrm.Client.Services;
using System.Net;
using Newtonsoft.Json.Linq;
using System.Configuration;

namespace DynamicsCRM_CurrencyExchangeRateUpdater
{
class Program
{
// The name of the Organization
private static string _OrgName = ConfigurationManager.AppSettings["OrganizationName"];

// The connection string used to connect
private static string _ConnectionString = ConfigurationManager.AppSettings["ConnectionString"];

private static bool _Silent = false;
private static Entity _BaseCurrency;

static void Main(string[] args)
{
// This .exe will be on the CRM server set as a scheduled task. The scheduled task will pass the arg "silent" just to skip all the "Console.WriteLine"s
_Silent = (args.Contains("silent"));

try
{
Microsoft.Xrm.Client.CrmConnection connection = CrmConnection.Parse(_ConnectionString);
using (OrganizationService service = new OrganizationService(connection))
{
OrganizationServiceContext serviceContext = new OrganizationServiceContext(service);

// Get the Organization details. This is to find the base currency.
var orgInfo = (from org in serviceContext.CreateQuery("organization") where ((string)(org["name"])).Equals(_OrgName) select org).FirstOrDefault();

// Save the reference to the baseCurrency for later comparison.
EntityReference baseCurrencyRef = orgInfo.GetAttributeValue("basecurrencyid");
ColumnSet cols = new ColumnSet(new String[] { "currencyname", "isocurrencycode", "exchangerate" });
_BaseCurrency = service.Retrieve("transactioncurrency", baseCurrencyRef.Id, cols);

Print("Base Currency ID: " + _BaseCurrency.Id);
Print("Base Currency Name: " + _BaseCurrency["currencyname"]);
Print("Base Currency Code: " + _BaseCurrency["isocurrencycode"]);
Print();

try
{
// Get an array of the currency Ids from CRM
var currencies = (from currency in serviceContext.CreateQuery("transactioncurrency") select currency.Id).ToArray();

for (int i = 0; i < currencies.Length; i++)
{
try
{
// Retrieve the current currency entity
Entity currency = service.Retrieve("transactioncurrency", currencies[i], cols);

String name = currency.GetAttributeValue("currencyname");
String code = currency.GetAttributeValue("isocurrencycode");
Decimal exchangeRate = currency.GetAttributeValue("exchangerate");

Print(String.Format("Processing currency \"{0}\". Code:{1}. Current rate:{2}", name, code, exchangeRate));

// Skip the base currency
if (currency.Id != baseCurrencyRef.Id)
{
//Construct the url to query
Uri = new Uri(String.Format("http://api.fixer.io/latest?base={0}&symbols={1}", _BaseCurrency["isocurrencycode"], code));
String result = "";

// Query Fixer.io for the latest exchange rates
try
{
using (WebClient wc = new WebClient())
{
wc.Headers[HttpRequestHeader.ContentType] = "application/json";
wc.Headers[HttpRequestHeader.KeepAlive] = "false";
wc.Encoding = System.Text.UTF8Encoding.UTF8;

result = wc.DownloadString(uri);

if (!_Silent)
{
Console.WriteLine("\tRequest: GET " + uri.ToString());
Console.WriteLine("\tResponse: " + result);
}
}

// Parse the response from fixer.io
try
{
var jsonResponse = JObject.Parse(result);
exchangeRate = Decimal.Parse(jsonResponse["rates"][code].ToString());
Print("\tNew exchange rate: " + exchangeRate);

try
{
// Update the exchange rate on the currency entity
currency["exchangerate"] = exchangeRate;

// Tell the organization service to update the currency entity
service.Update(currency);

Print("\tUpdated currency.");
}
catch (Exception ex)
{
Print("\tException updating CRM record... EX: " + ex.Message);
}
}
catch (Exception ex)
{
Print("\tException parsing JSON... EX: " + ex.Message);
}
}
catch (Exception ex)
{
Print("\tException getting result from fixer.io... EX: " + ex.Message);
}
}
else
{
Print("\tBase currency.");
}
Print();
}
catch (Exception ex)
{
Print("\tError processing currency... EX: " + ex.Message);
}
}
}
catch (Exception e)
{
Print("Exception iterating over CRM currencies... EX: " + e.ToString());

// Handle the exception.
throw; //Or not.
}
}
}
catch (Exception ex)
{
Print("Exception connecting to CRM and getting the base currency... EX: " + ex.Message);
}

if (!_Silent) { Console.ReadLine(); }
}

private static void Print(string message)
{
if (!_Silent)
{
Console.WriteLine(message);
}
}
private static void Print() { Print(""); }
}
}

(Note: Download the full code here)

Test the console application on the server by double-clicking the .exe. The expected output should be something like:

crm12

Finally, schedule the task on the CRM Server:

1)      Move the contents of the build to the Dynamics CRM Server.

2)      Open Windows Task Scheduler

3)      Create a new task

4)      Set the trigger to be daily (for example, run at 1:00am)

5)      Set the action to be running the compiled .exe, passing the parameter “silent”. (Without the quotes. This will suppress the console output)

6)      Make sure the task will run even if the user is not logged on

7)      Test the task by running it manually and checking CRM to see if the currencies are updated.

———————————————————-

We can help! If you have any questions or would like assistance configuring your Dynamics System for automated exchange rate updates, please contact the experts at CRGroup at 1.800.576.6215 or email us at crg@crgroup.com

———————————————————–

mattfoy2About the Author:

Matthew Foy is a .NET developer working on CRGroup’s SharePoint and CRM consultancy team. He has a passion for robust solutions and a keen interest in solving problems.

 

 

Show Buttons
Hide Buttons