Once in a while I get a call or an e-mail from a user that is stuck in GP in the Sales Order entry form or the PO receiving process, because they received an error message on screen telling them there is currently no valid Exchange Rate available for this transaction.
As in many companies when you enable Multi-Currency in GP, you’ll end up with a ton of Exchange Rate ID’s, considering you might need one for the daily transactions, the Average for a month, and the month-end rate for some reports. Even worse, if you use Management reporter, you may want to use different rate ID’s then the ones for daily use.
Anyhow, in our system with a dozen GP companies, we deal with as much as 46 Exchange Rate ID’s (and this doesn’t include the Fabrikam Z-xxx rates), ranging from BATH all the way to ZAR. Some of them are more often used pairs than others, so you get the picture: our users from the Finance department that are entering them every day (mostly) are sometimes behind or forget about some of the pairs (especially in month-end when they’re busy).
I had written an Excel report a few years back that would give them a quick overview of the various MC tables involved, notably MC00100 (Exchange Rate table), MC40300 (Rate ID’s) and MC60200 (MC company access). The problem with that report was that the data returned for MC00100 would just keep getting longer and longer as you don’t remove outdated rates.. Sorting the data by the expiration rate date wasn’t a good idea either, because due to some typo’s during entry, some of the expiration dates would be totally off (like in 3016 or 2106), thus not very reliable..
I tweaked the SQL query that I had built to extract the MC00100 data to return the most recent entered Exchange Rate only for each Rate ID… which limited the entries to the actual amount of Rate ID’s (or pairs) we have in the sytem. Much better and easier to quickly spot where there is an expired currency rate that needed a new entry.
Here is the SQL script that you can easily put into a refreshable Excel report (or if you own SmartList builder, create a view and use it with SmartList in GP).
/* This query allows to retrieve all the most current Exchange Rates from the Rate Table MC00100 in the DYNAMICS DB. Given the nature of error prone entries, it can't be based on the Expiration date, as sometimes totally unreallistic dates show up there (like 3016-12-31, due to a typo). The query was written in a way that lists in descending order by the latest EXCHDATE (c) 2017-02-03 B. Bucher */ SELECT EXGTBLID ,CURNCYID ,EXCHDATE ,XCHGRATE ,EXPNDATE FROM DYNAMICS..MC00100 Inner join (Select EXGTBLID as ExcTabID, max(EXCHDATE) as ExcDate from MC00100 Group by EXGTBLID) GroupDT on MC00100.EXGTBLID = GroupDT.ExcTabID and MC00100.EXCHDATE = GroupDT.ExcDate ORDER BY GroupDT.ExcDate DESC
PS: I know that some may arg we could use a data feed to update our currency rates, but I’ve looked around and as we draw them from the Bank Of Canada web site, I wasn’t able to find a feed that would work with their data (at least not for free).
Until next post..