You’re offline. This is a read only version of the page.
My client has 10 years' worth of data with 2 of their iso codes being 2 characters long. In order to deploy web services it appears as if I'm not only going to have to modify the key fields for these iso codes but all data in all modules for the client data. good thing that they only use GL, AR, AP and FA (obviously with many multi-currency transactions). Is there any article that you are aware of that helps me with this change. I may have to rebuild many of the tables with iso codes as key fields and this is a little overwhelming to be considering.
*This post is locked for comments
Ian Grieve was correct on his reply that it was only the ISO code and not the currency id. Beat .. thanks for the reply and you are right in that it would be horribly messed up if GP ever tried to enforce key field restrictions on something like currency id at this or any point in the future. The funny thing is that when I went through my currencies, the ISO code that was only 2 characters long was Z-EU as EU ... this was part of the install and not anything that was in use by the company .... so it was a GP delivered ISO code that stopped me from installing web services ... although it was a quick and easy fix
Hi Stephen,
This is actually pretty funny and ironic if that statement is true (about the 3char length)... Microsoft's own default "demo" currency for the Fabrikam Inc. company have all kinds of format :
CURNCYID CURRNIDX
Z-AUD 1000
Z-C$ 1001
Z-EURO 1002
Z-NZD 1003
Z-SA 1004
Z-SGD 1005
Z-UK 1006
Z-US$ 1007
Just looking quickly after the CURNCYID & CURRNIDX fields, they show up over 200 times each all over the place in GP.. I can't believe that Microsoft has made such a stupid move to impose a minimal length for the Currency Code or ID.
I know there are some scripts in SQL that could help do the search & replace across an entire company DB, but would definitely test it out before going on Live with the changes.
PS: the above data comes from DYNAMICS..MC40200
It is JUST the ISO code and not the currency id that needs to be 3 characters ... I switched all ISO codes to the proper 3 char country code (including 2 char iso codes from Z-UK etc that came with the install) and I'm able to get past the error.
Ian - Thanks for being the only one to give it a shot but...
The Currency Id is the ISO code that they are referring to. Basically what I am certain that I need to do is:
1) create new Currency Id's (3 chars)
2) Create the Tax Table data for the new currency ids from the old 2 char
3) Identify every table with Currency Id and create scripts to update them to the new currency id's
4) Additionally for every table that uses the CURRNIDX I'll also have to change it to the new index for the new currency
4) delete the old currency id's in the system so that Web Services is able to be deployed
I have scripts for identifying every table that certain fields are in but still it's a daunting task and I can't miss any table or there will be data integrity errors.
5) Some of the currency fields are PCurrencyID, ISOCURRC, TranslationCurrencyID, STCURRID, Last_Currency_ID, Currency_Note_Index, FUNLCURR, RPTGCURR, DENOMCURR, FROMCURR, ICCURRID, ICCURRIX, USRCURR1, USRCURR2, VALCURR1, VALCURR2 ... so as you may see this is a bit of a daunting task in that I must replace every currency_Id and or currencyidx with the new values and it has to be complete for every table with data
I'll use the following script to identify all tables with column name Currency_Id:
/* Following stmt will check in the tables only (replace xxxx with column name you are looking for */
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name like '%Curr%'
ORDER BY schema_name, table_name;
/* Following stmt will search entire database (including views) */
-- select name,* from sysobjects where id in(select id from syscolumns where name like '%Curr%')
Is the ISO currency code stored anywhere else in the system? As far as I recall, the transaction tables store the Currency ID, not the ISO code.
The option for GP2016 wasn't in the forum as yet .. this is an upgrade from GP2013 build 2014 to GP2016 and I need Web Services. Am not able to deploy with GP2016 due to iso code length in existing transactions being 2 chars.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (