Currency Symbol not showing correctly for Users

I resolved an interesting case the other day and thought I would write this one up on the blog.

The Problem

The customer reported that for a couple of their users, the currency symbol on the GL Transaction Entry window where not matching the symbols as defined by the Currency ID setup. This issue occurred with multiple Currency IDs.

The screenshot below shows the window using the currency Moroccan Dirhams (MAD) which should be showing a Currency Symbol of "MAD". You will notice that the Currency Symbol in the scrolling window is showing the Pound (£) symbol and the Currency Symbol for the totals is showing the Dollar ($) symbol.

GL Transaction Entry window showing Currency Symbol issue

Also why would this only some users and not others. 

The Cause

The clue that helped identify the cause was that the problem with the Currency Symbols displaying incorrect only occurred when the transaction was using the Functional Currency view. When in Originating Currency view, it did correctly show the Currency Symbols.

Now it everything started to make sense. The problem was with the Functional Currency. That's why it affected all Currency IDs. As many windows in Microsoft Dynamics GP remember the last multicurrency view used on a per user basis, it also explained why it affected specific users and not others. It only affected users who last displayed the Functional Currency view.

Now, the first thing I had to do was explain to the customer that the currency symbol being incorrect is a display only issue. The figures in the SQL database are stored without a currency symbol and would be correct even if they were displayed with the wrong Currency Symbol on the screen. This problem does not cause any data damage.

The Functional Currency for the company was US Dollars and that is why the Dollar ($) Symbol was displayed in the totals section, but now we had to find out why the Currency Symbol for the Functional Currency was incorrectly displayed in the scrolling window.

Here is a little bit of theory about how Currency Symbols work. The formatting of a Currency field is controlled by Currency Index value. In fact, it is really controlled by a "Phantom" Currency Index which is a combination of two values. The first is a value between 0 and 5 which controls the number of decimal places to display. The second value is the base Currency Index associated with the Currency ID. The base Currency Index starts at 1000 and is incremented each time a Currency ID is added to the Currency Setup (MC_Currency_SETP) MC40200 table in the System Database.

The Functional Currency for a company is defined in the Multicurrency Setup (MC_SETP) MC40000 table in the Company Database.

To make sure that the Currency Index value for the Functional Currency matches the Currency Index for that Currency ID we can use the following Transact-SQL query against the Company Database (assuming the System Database is called DYNAMICS):

select M.CURNCYID, S.FUNLCURR, M.CURRNIDX, S.FUNCRIDX
from DYNAMICS.dbo.MC40200 M
join MC40000 S on S.FUNLCURR = M.CURNCYID

The results should show the Currency ID for the Functional Currency in columns 1 & 2 and the Currency Index in each table in columns 3 & 4. If the values in columns 3 & 4 don't match.... you have a problem. A problem which will result in the Functional Currency being displayed with an incorrect Currency Symbol.

The Solution

The solution is fairly simple, all we need to do is correct the Currency Index in the Company's Multicurrency Setup table so that it matches the Currency Index for the Currency ID in the Currency Setup table. 

The following Transact-SQL update statement will make the required change:

update S set FUNCRIDX = M.CURRNIDX
from DYNAMICS.dbo.MC40200 M
join MC40000 S on S.FUNLCURR = M.CURNCYID

After this change was made to the table, the system started behaving correctly.

David