Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
Announcing new navigation menus and the Engage with the Community forum!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Search and Replace val...
Microsoft Dynamics GP (Archived)

Search and Replace values throughout a database

(0) ShareShare
ReportReport
Posted on by Community Member

I have a customer looking to "re-implement" multi-currency.  They have multiple companies with transactions in each.  They have not actually been using rates or currency translations, so far.  The problem is that some companies show a functional currency ID of Z-US$, USD, or Z-USD (for example).  Of course, I get the "Activity for this functional currency already exists.  The functional currency can't be changed." message.  I am thinking I need only update the currency IDs to be on my merry way with the re-implementation.

Companies do not all use the same modules, so a static script won't do.  I want to avoid having to re-implement each company. To that end, does anyone has a script which would go through all the tables and replace Z-US$ with USD (for example)? 

I found this script which does a nice job of finding the values, but I need a script which could replace the value wherever it is found.  I vaguely recall there was a utility years back which could do this, but I do not recall who made it or the product name and The Google seems to have been cluttered with other things since.

http://www.gp-dynamics.com/dynamics-gp-command-details.asp?id=90

Thanks in advance!

 

*This post is locked for comments

  • Josh P Profile Picture
    Josh P 2,895 on at
    Re: Search and Replace values throughout a database

    Instead of deleting it, why don't you post what you did, so others who may have questions on this can see how others tackled the problem.

    Thanks,

    Joshua Pelkola

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    Re: Search and Replace values throughout a database

    Thank you for your reply, but I did manage to resolve the problem shortly after posting it. I thought I'd successfully deleted the question, but it seems it is still here.  If a moderator could be so kind, please delete the original post.  Thank you.

  • Suggested answer
    Josh P Profile Picture
    Josh P 2,895 on at
    Re: Search and Replace values throughout a database

    Hi,

    First, I would suggest you leave the functional currencies as they are, and set up the new currencies as needed using the ISO standards. That's my disclaimer.

    Second, you probably don't like first because the data is not consistent, so it can be done. The problem is you have both a currency index (CURRNIDX) and a currency id (CURNCYID) for transactions, functional id and index for each company (FUNLCURR) and (FUNCRIDX). You also have reporting currencies (RPTGCURR) (RPTCRIDX).

    You will need to change the fields for both the id and index which is based off the master table in the DYNAMICS database for every table in the system that holds this information. There are a lot of tables. Depending on how many transactions are already in the database, and what modules are being used, this will be at least about a 150 tables per company database.

    The general script used in sql would be:

    USE <Company Database>

    UPDATE <Table>

    SET <Field> = <New Value>

    WHERE <Field> = <Old Value>

    This script will give you a list of all tables which hold a specific column name (this is by far more friendly than the script you found):

    SELECT distinct(o.name) FROM sysobjects o, syscolumns c

    WHERE o.id = c.id AND o.type = 'U'

    AND c.name = 'CURNCYID'  /* run for each field name */

    ORDER BY o.name

    You would have to run this script on every table that holds the currency id column and currency index number in the dynamics database and each company database. I would recommend you consult a professional that has performed this type of conversion to do this for you, and make sure you back up your databases in case something fails.

    If you are not scared to death by this reply, you should be. Nearly all consultants/partners will tell you to create a new company database, export all of your records, then re-import to the new company. Changing the functional currency is generally not a good idea via SQL.

    Regards,

    Joshua Pelkola

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Check out the New Agent Creator Community
Announcing new navigation menus and the Engage with the Community forum!

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard >

Last month Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans