Subscribe via RSS Feed Connect on LinkedIn

Dynamics NAV 2013R2 Currency Dashboard Part 1

27th October 2014 0 Comments

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.

20141025083752_thumb2

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

20141025082425_thumb2

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

20141025083305_thumb2

 

Module 1

Currency Dashboard

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

20141027141932

 

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.

20141027142147

 

Get System Currency Rates

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

20141027145251

 

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”.

20141027142357

 

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

20141027142510

 

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.

20141027124238

 

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”.

20141027142827

 

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”.

20141027142912

 

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.

20141027143013

 

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.

Please Share this Post:

Leave a Reply

Spam protection by WP Captcha-Free