Dynamics NAV 2013R2 Currency Dashboard Part 1

This Post is the First in a Series on Microsoft Dynamics 2013R2 Development, Part One will look at the Requirement, in Part Two I will look at the Code Behind the First Module Solution with an Object Download available in Text format.

This Scenario has come up before on several occasions however I had never Designed a Solution, many Companies Deal with Multi-Currencies in multiple Companies, when they want to Update the Currency Rates they go into each Company and manually Update the Rates one Line at a Time, if there are a few Companies this can be very Time Consuming.

 

Requirement

I had a good think about this and have the following observations based on my knowledge of Currencies in Dynamics NAV 2013R2.

• Module 1. Would require a basic solution that can be run in each Company, a Currency Dashboard, the Dashboard will have the following Functions, Get Currencies, Get Currency Exchange Rates from System and Insert or Update the Rates.
• Module 2. Would require a more advanced solution that can be run in a Parent Company, a Global Currency Dashboard, the Global Currency Dashboard will have the following Functions, Get Currencies across all Companies, Get Currency Exchange Rates across all Companies, update the Rates by Triangulation of the Rates from the Parent Company and Insert or Update the Rates across all Companies.
• Module 3. Based on the Second Module, Job Queue Automation of a Currency Rate XML Feed to Import the Rates from the Bank, update and maintain the Rates across all Companies in a Single Database.

 

Modules

I have decided to Split the Modules into New Posts over Three Weeks, this Weeks Module is just the Basic Solution, in the Design I have added additional Fields and structured the Development to accommodate Modules Two and Three, I have done this by Adding a Company Name to the Primary Key of the two New Tables.

 

Inverse Currency Rate

You will see a Option Field Reference to the “Normal Rate” and “Inverse Rate”, if you are familiar with the Currency Exchange Rate table in the Default Cronus Database the Values used are all “Inverse Exchange Rates” like 0.623885, Rates are often referred to in the rounded “Normal” Currency Rate like 1.60, if the Currency Rate is 1 GBP = 1.60 USD then the Inverse Rate is 1 USD = 0.62 GBP.

In Dynamics NAV looking at the Currency Exchange Rate Table, I can Setup Lines both ways Normal or Inverse, in the Cronus UK database if I create Two Lines in the Currency Exchange Rate table for USD, I can create either Normal or Inverse.

If I then were to create two Purchase Orders that span these Dates and Lookup the Currency Rate the Different formats are shown.

In Both Purchase Orders the Purchase Order Line “Unit Cost (LCY)” value is the Same for a One Dollar Value.

 

Module 1

Currency Dashboard

Here is the Page for Module One a Document page with two List Parts and Three Page Actions.

 

Get Currency Lines

The Page Action “Get Currency Lines” loops through the Currency Table, Looks up any related Currency Exchange Rates to Set the Type to Normal or Inverse, fields are used in Calculations and will be used again in Module 2.

 

Get System Currency Rates

The Page Action Runs a Report to Set a Currency Date before populating the Lines.

 

When Run for each Currency a Codeunit Function will Filter by Date any Currency Exchange Rate entries and Return the “Existing Rate Amount” and “Existing Rate Date” to a New Table, if a Rate is Found the Currency Action is Set to “Skip Rate”.

 

Selecting the Section Actions, Line –> Show Information, a Dialogue Window will Appear, showing the Normal Rate and Inverse Rate.

 

To test the Module I looked at the XE GBP Currency Rates which have Both the Normal and Inverse Rates Displayed, the “EX Currency” Screenshot was taken after I entered and Applied the New Rate in the example below so there is a Difference  of 0.000017.

 

Making the Currency Dashboard Page Editable will allow either the “Exchange Rate Amount” or “Relational Exch. Rate Amount” to be Updated, for the USD Rate I use the Normal Rate from XE, and Check the Line Information, XE round to Five Places and Dynamics NAV allows rounding to Six Places notice that the “Currency Action” changed to “Update Rate”.

 

Apply Exchange Rates

This Page Action calls a Codeunit Function that looks for “Insert Rate” and “Update Rate” records, Applies the New Rates to the Currency Exchange Rate Table and updates the Currency Action to “Applied”.

 

If the User Runs the “Get Currency Lines” for the Same Date they would see the Applied rate has been Returned and the Currency Action is now Skip Rate.

 

Part Two

In Part Two I will show the different Objects for Module 1 and the Code Behind that Populates the Values, at the Foot of Part Two there will be the Objects in Text format to Download, you can read Part Two Here.