Dynamics NAV 2013R2 Currency Dashboard Part 2

admin • 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.

C/AL Globals

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

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.

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.

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.

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.

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

C/AL Global Text Constant

This variable is used in Module 3

C/AL Global Functions

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

C/AL Code

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

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.

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.

Function CalcFactor

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

Function UpdateStatus

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

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.

Codeunit 50002: Currency Dashboard Management

C/AL Global Text Constants

The Text Constants are used for the Dialogue Progress Window

C/AL Functions

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

Function GetCurrency

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

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.

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.

This Code Closes the above Statements.

Function GetCurrencyRates

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

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.

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