Subscribe via RSS Feed Connect on LinkedIn

Dynamics NAV 2013R2 Currency Dashboard Part 2

27th October 2014 0 Comments

This post is the Second in a Series on Microsoft Dynamics 2013R2 Development, Part One Covered the Three Modules I will be Posting over the Coming Weeks, this Post is the “Code Behind” a Basic Currency Dashboard Shown in Part One the Objects in Text Format can be Downloaded at the Foot of this Post.

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.

The basic solution can be run in each Company, as a new Currency Dashboard, the Dashboard has the following Functions, Get Currencies, Get Currency Exchange Rates from System and Insert or Update the Currency Exchange Rate table, this Post Shows the Objects I used and the Code Behind the Solution, in Developing and Planning the Solution Consideration was Given to Module Two and Module Three.

New Tables

Table 50001: Global Currency

Fields

This Table has a Primary Key of Company Name and Currency Code the use of the Company Name Field will become more apparent in Module 2, the LCY code is Populated from the G/L Setup and the Type is Set from the Values, I set the Decimals on the Decimal Fields to 0:0, only the two Decimal Fields will be Editable.

20141027162856

 

C/AL Globals

One Function was Created to Update the Type Field rather than writing the code in each Decimal Field or Other Triggers.

20141027162917

 

C/AL Code

The Code in the On Insert and On Modify Triggers Defaults the “Exchange Rate Amt.” if Required, here I used the Round Date Time to Round to the Nearest Minute for Display Reasons.

20141027163103

 

Only the “Exchange Rate Amount or the “Relational Exch. Rate Amt.” can have a Value not Both, these are used in the Calculation to Default a Rate and Set the Type Value, if either Field has a New Value the Other Field is Cleared, this is better than Error Messages, Only a User that Deals with the Currencies should be editing these Values.

20141027163139

 

Function Update Type

The Function was Initially designed to use the MOD function, setting the Decimal Places on the Field overrides this code, but I left the Code in case the Decimal Places Property was Changed, an End User’s Licence might allow the User to edit the Properties but not the Code, the Function Sets the Type based on the Decimal Values.

20141027163243

 

Data

When Populated the Inverse Rate (UGS) or Normal Rate (USD) Value is set, the Last Updated Date Time shows the Result of the Round Date Time Call.

20141027164259

 

Table 50002: Global Exchange Rate

This Table has a Primary Key of Company Name, Currency Code and Currency Date, the use of the Company Name Field will become more apparent in Module 2, the LCY code is Populated from the G/L Setup and the Type is Set from the Values, this Table is Updated in Functional Code and Holds Type Information from the Existing Currency Exchange Rate Table.

 

Fields

 

20141027175025

 

C/AL Global Text Constant

This variable is used in Module 3

20141027175107

 

C/AL Global Functions

These Three functions are used in the Field Validation to Update Record Values.

20141027175119

 

C/AL Code

The On Insert and On Modify triggers can set the Default “Exchange Rate Amount” Value and Call Validation Functions

20141027192220

 

Both the “Exchange Rate Amount” and the “Relational Exch. Rate Amt.” can have a Value, these are used in the Calculation to Calculate the Currency Factor and Set the Type Value.

20141027175331

 

Function InitRate

On Insert this Function is Called to Find the Last Currency Exchange Rate within a Date Filter, if a Rate is Found then the “Existing Rate Amount” and “Existing Rate Date” are Populated along with the Currency Description.

20141027175516

 

 

Function CalcFactor

A Currency factor based on the Type is Calculated to be used in Calculations.

20141027175641

 

Function UpdateStatus

The first part of the Code in this Function Validates the Decimal Fields for Errors.

20141027175941

The second Part of the Function Code Sets the Status of the “Currency Action” to see if a Record Should be Inserted, “Update Rate” is for when an Existing Rate has been Found but the Input Values are Different, if no Existing Currency Rate is found the Status is set to “Insert Rate”, some Rates are Rounded to Six Places the same as the Field Properties.

20141027180000

 

Codeunit 50002: Currency Dashboard Management

C/AL Global Text Constants

The Text Constants are used for the Dialogue Progress Window

20141027193816

 

C/AL Functions

The Three Functions are Called from the Page to Retrieve and Set the Data

20141027193829

 

Function GetCurrency

This Function Inserts the Currency Records and Sets Some Default Values for the Normal or Inverse Multiplier.

20141027195824

 

The C/AL Code Opens the Dialogue Window, Gets the General Ledger Setup and Tests for the “LCY Code”, if a Currency Record Set is found a REPEAT UNTIL LOOP is Started and the Dialogue Window is update on each Loop.

20141027193956

If the Global Currency Record is not Found then a New Record is Created, the Currency Exchange Rate table is Filtered to Find the Last Record and Set the Multiplier either the “Exchange Rate Amount” or the “Relational Exch. Rate Amt.”, on Insert the Type is Set.

20141027194139

This Code Closes the above Statements.

20141027194221

 

Function GetCurrencyRates

This Function Creates the Global Currency Rate Table from the System Currency Rates.

20141027195852

 

20141027202723

 

This Function is Called from a Report with the Currency Date and Populates the “Global Exchange Rates” Table, the  Code finds all the Currencies, Clears the existing Data, and Opens the Dialogue Window.

This Block of Code Checks a Date has been Passed In, filters the Global Currencies, Gets the General Ledger Setup for the LCY Code, Filters and Deletes the old Global Rates and Opens the Dialogue Window.

20141027202617

 

If a Record Set is found the the Global Currency rates are Inserted, the TRUE on the Insert Call runs the Table Code to Set the the “Existing Rate Amount” and “Existing Rate Date” and “Currency Action”.

20141027202707

 

Function ApplyCurrencyRates

This Function Update or Inserts the “Currency Exchange Rate” Table with the Global Exchange Rate Table Values.

20141027195933

 

The Global Exchange Rates are Filtered by “Currency Action” for the Insert or Update, if a Record Set is found the Dialogue Window is opened.

20141027194445

 

This Code tries to Get a Matching Record to Update, if it Cannot Find a Record it will Insert a New Record in the Currency Exchange Rate Table.

20141027194520

 

The Existing Rate fields and Currency Action Status are Updated to Refect the Changes.

20141027194550

 

Report 50002: Get Currency Rates

This Report is a simple Report that Runs the function GetCurrencyRates with a Currency Date

20141027204719

 

Page 50004: Currency Dashboard

This Page is the Main Page and has Two Page Parts for Currencies and Rates.

20141027205029

 

The Page has the Company Table as the Source Table, this will be used in Module 2, in this Module the Page is Filtered.

20141027205113

 

The Actions are in an Action Group of Functions, the Actions Call the Codeunit Functions.

20141027205145

 

Images are set on the Action Properties.

20141027205209

 

The Actions C/AL Code call the Codeunit Functions.

20141027205257

 

Page 50005: Currency

Displays the Global Currency Table in a Sub Page.

20141027205323

 

Page 50006: Currency Exchange Rate

Displays the Global Exchange Rates add allows the Rate to be Adjusted.

20141027205353

 

This Page has Two Page Actions to Display the System Exchange Rates and the Information Pop-up Dialogue.

20141027205413

 

This C/AL Code displays the Dialogue Box.

20141027205500

 

I hope this Post is of Use, Please take time to Comment and Feedback, I will Post Module 2 in about a Weeks Time.

Download “Currency Dashboard Module 1” Currency-Dashboard-Module-1.txt – Downloaded 237 times – 31 KB

Please Share this Post:

Leave a Reply

Spam protection by WP Captcha-Free