Open Forum

Expand all | Collapse all

Copying Chart of Accounts

  • 1.  Copying Chart of Accounts

    TOP CONTRIBUTOR
    Posted Apr 03, 2019 06:31 PM
    I'm trying to set up a new company.  The user would like to copy over an existing chart of accounts from another company in the same GP instance to get things going a little faster.  I've looked at two potential "copy" utilities for setting up new companies in GP, but neither looks like it will do what I want.  The first was RapidStart - but that looks like a utility to port data from some other program and that's not what I'm after.  The other is the Company Copy utility in the PSTL, but I don't want to copy the entire module.  I just want the chart of accounts - not the customers, etc.

    Is there something I'm missing here?

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------
    GPUG Summit - Post


  • 2.  RE: Copying Chart of Accounts

    GOLD CONTRIBUTOR
    Posted Apr 03, 2019 06:55 PM
    Hi Blair,

    re: PSTL Company Copy utility...
    You could probably just mark Financial... and then use MDGP >> Maintenance >> Clear Data on the transaction tables...



    ------------------------------
    Ryan Galang
    BTP Technologies LLC
    Chantilly VA
    ------------------------------

    GPUG Summit - Post


  • 3.  RE: Copying Chart of Accounts

    TOP CONTRIBUTOR
    Posted Apr 04, 2019 08:42 AM
    The Company copy PSTL tool only copies reports and mater records. Not transactions, so you should be good to go!

    Using Company Copy (From the PSTL manual):
    • This tool allows the setup and report option information from one company to be copied to another company within Microsoft Dynamics GP. You can choose the modules for which information will be copied from the source company to the destination company.
    • This tool requires that the user be logged in as "sa."
    Before using the Company Copy tool, be aware of the following important issues:
    •  You should use this tool only after creating a SQL database backup of the destination company. The copy operation is irreversible without a backup.
    •  When the copy process is run, the setup tables and report options tables in the modules you selected will be deleted for the destination company. If you have manually added setup or report option information for a module in the destination company, and you do not want this information deleted, do not use the Company Copy tool to copy data for that module.
    • You should be the only user who is logged into Microsoft Dynamics GP when the operation is performed.
    • The operation can take a significant amount of time to complete, so you should consider performing it during off-peak hours. 
    Personally I have never had the process take a significant amount of time, but maybe I have never worked with a huge database. Just plan accordingly.

    ------------------------------
    Barbara Gavron
    jacksonville beach FL
    904-536-0129
    ------------------------------

    GPUG Summit - Post


  • 4.  RE: Copying Chart of Accounts

    GOLD CONTRIBUTOR
    Posted Apr 04, 2019 08:10 AM
    Blair,

    I would not be concerned with using PSTL to migrate just your chart and no data.  But, if you like complete control and knowledge of what gets migrated, I would export the few required fields to excel using SmartList.  Then use Integration Manager or a word-merge macro to import them into the new company.

    GL accounts are a simple import.  And, typically, someone in accounting will take advantage of the opportunity of being able to manipulate the chart in Excel to tailor it for the new company.

    Chris


    ------------------------------
    Chris Donnelly
    Sr Mgr of Info Systems and Financial Reporting
    Healthmark Industries
    ------------------------------

    GPUG Summit - Post


  • 5.  RE: Copying Chart of Accounts

    SILVER CONTRIBUTOR
    Posted Apr 04, 2019 08:15 AM
    You can also export the chart of accounts by going to SmartList, Financial, Accounts and then import via IM - quick and simple and you get just what you want. You can also remove any accounts that you don't want to import.

    ------------------------------
    Sally Higgins
    arvada CO
    ------------------------------

    GPUG Summit - Post


  • 6.  RE: Copying Chart of Accounts

    Posted Apr 04, 2019 09:33 AM
    Hi Blair,

    I just had the same scenario for two new databases at our company. I created the companies in DynUtils, logged into each one to create the Account Format and Fiscal Years (with a macro I use).

    Next I used SQL to copy the contents of GL00100 (Account Master), GL00102 (Account Categories), and GL40200 (Segment Master).

    **Note that for GL00102 I deleted all records in the new database first, then inserted the records from the old database.

    Lastly I ran check links on the Financial Series.

    We also use Multicurrency, so I had a few steps to perform there, but I took care of all that in GP.

    Hope that helps,
    Jeff

    ------------------------------
    Jeff Pfershy
    Sr Business Analyst
    BluJay Solutions Inc
    Holland MI
    ------------------------------

    GPUG Summit - Post


  • 7.  RE: Copying Chart of Accounts

    TOP CONTRIBUTOR
    Posted Apr 04, 2019 10:01 AM
    Thanks @Jeff Pfershy.  I am familiar with moving data with SQL, just didn't know the tables involved.​

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------

    GPUG Summit - Post


  • 8.  RE: Copying Chart of Accounts

    TOP CONTRIBUTOR
    Posted Apr 04, 2019 09:22 AM
    @Blair Christensen​​

    When copying setups and some master records I always use the SQL Import tool.

    This is always a FIRST Step and I have not done any setups yet that involve master records....

    Couple of assumptions - you have no segment in the COA that determine the company for the COA (if you do you can still use this, but then you'd have to modify using the Mass Modify for COA)

    Also, assuming you have no transactions and that you have done NO setups using an existing COA - such as setting any default accounts for posting.

    Right click on the DB you want to copy to or from, and follow these procedures - apologies, I want to answer quickly so if anything is unclear, please let me know:













    ------------------------------
    Jo deRuiter, MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    WEBSITE: https://aislingdynamics.com/
    BLOG: https://community.dynamics.com/gp/b/gplife
    770-906-4504 (Cell)

    ------------------------------

    GPUG Summit - Post


  • 9.  RE: Copying Chart of Accounts

    TOP CONTRIBUTOR
    Posted Apr 04, 2019 10:10 AM
    So I tried the import wizard and despite setting Identity Insert ON, the import failed writing to the DEX_ROW_ID field:

    Import Error Detail
    Any suggestions, @Jo deRuiter?​  I mean if I can get away with a simply INSERT INTO ... SELECT ... FROM I'm okay with that, I just didn't know if there was a better tool nor did I have a good list of the tables involved.

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------

    GPUG Summit - Post


  • 10.  RE: Copying Chart of Accounts

    TOP CONTRIBUTOR
    Posted Apr 04, 2019 10:15 AM
    @Blair Christensen​​

    Did you choose to delete what is already there?

    ------------------------------
    Jo deRuiter, MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    WEBSITE: https://aislingdynamics.com/
    BLOG: https://community.dynamics.com/gp/b/gplife
    770-906-4504 (Cell)

    ------------------------------

    GPUG Summit - Post


  • 11.  RE: Copying Chart of Accounts

    TOP CONTRIBUTOR
    Posted Apr 04, 2019 11:40 AM
    @Jo deRuiter.  Yep.  Followed your screenshots to the letter.  (And I've used the utility before.  I was just surprised that it said that the field was "read only".  That's a new one for me... ​

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------

    GPUG Summit - Post


  • 12.  RE: Copying Chart of Accounts

    TOP CONTRIBUTOR
    Posted Apr 04, 2019 11:43 AM

    New to me as well!

     

    Thank You,

     

    Kind Regards,

     

    logo   

    Jo A deRuiter Dynamics GP Practice Lead

    Aisling Dynamics Consulting, LLC

    770-906-4504 |

     

    Milwaukee, WI

     

     

     

     

     

     

    https://d36urhup7zbd7q.cloudfront.net/b463c824-0138-4d64-b3cb-e90b65d7392c/LOGOSFOREMAILSIGNATURE.format_png.resize_520x.png

    IMPORTANT: The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email by mistake, please notify the sender immediately and do not disclose the contents to anyone or make copies thereof.

     

     

     




    GPUG Summit - Post


  • 13.  RE: Copying Chart of Accounts

    GPUG ALL STAR
    Posted Apr 04, 2019 10:15 AM
    Edited by Beat Bucher Apr 04, 2019 10:16 AM
    Hi @Blair Christensen
    Don't get fooled by the RapidStart Migration utility, it's not just for data migration from other accounting systems.. I actually used the set of Excel Templates to create 2 new GP companies of which we needed to have the same Chart of Account as other companies, but with slight differences in on segment (region). The data could be retrieved from our main company within the Excel template, adjusted all the values and then run the process to populate the new companies.
    No complicate export / import involving table manipulation and reconciles. All by the book !
    I know Microsoft doens't provide the tool anymore since GP2013 I believe, but you can still apply it to the most recent versions. ​

    PS: we also imported all the inventory items that we need in those companies as well as a few base Customer / Vendor Cards for intercompany transactions.

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    GPUG Summit - Post


  • 14.  RE: Copying Chart of Accounts

    TOP CONTRIBUTOR
    Posted Apr 04, 2019 11:43 AM
    @Beat Bucher  Can you point me to something that tells how to use the utility?  The stuff I've found has been fluff pieces about how great the tool is (but without any instructions) or youtube videos that show the import from another accounting system such as Quickbooks.  Nothing just on how to use it to import a few specific things (like a Chart of Accounts) from another Dynamics database.  I've never used the tool before, so something step-by-step would be a real help.  Thanks!

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------

    GPUG Summit - Post


  • 15.  RE: Copying Chart of Accounts

    GPUG ALL STAR
    Posted Apr 04, 2019 11:51 AM
    @Blair Christensen
    The last good known documentation (and setup) I have is for GP 2013. Attached is the User Guide to setup the tool and how to use it (about 70 pages). I've also put the 2010 version, which was called Rapid Implementation Tool (instead of Rapid Start Services).
    That should you get started.


    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Attachment(s)

    GPUG Summit - Post


  • 16.  RE: Copying Chart of Accounts

    TOP CONTRIBUTOR
    Posted Apr 05, 2019 01:59 PM
    You may have already gotten past your pain point using the method Beat suggested.  But on the SQL method of exporting/importing where the DEX_ROW_ID was blocking....in my past life I had to do this very thing a lot not just for the COA.  There were other specific setups I copied from one to the other every time we spun up a new company database.  The thing I think you may have needed to do was on the Edit Mappings page, scroll down to where you can see the DEX_ROW_ID column.  Switch the destination to the setting where it's ignored (I don't remember exactly the term used as I haven't had to do this for quite some time).  In the destination, it will create it's own set of dex ids rather than trying to copy the source dex ids to the destination.

    And maybe this is irrelevant as I don't remember using the Enable Identity Insert back then (and not familiar with it).  Is this meant to copy the source dex ids to the destination?

    ------------------------------
    Diana Doane
    Ascend Learning, LLC
    Leawood KS
    ------------------------------

    GPUG Summit - Post


  • 17.  RE: Copying Chart of Accounts

    GPUG ALL STAR
    Posted Apr 05, 2019 03:43 PM
    This is a key indication.. DEX_ROW_ID is a self-generated key field and as such should never be provided in the import data set. That field witl be auto-populated by GP when you re-import the data into the new company, so you can safely leave it out when exporting from another system/company.

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    GPUG Summit - Post


  • 18.  RE: Copying Chart of Accounts

    TOP CONTRIBUTOR
    Posted Apr 08, 2019 09:54 AM
    Thanks, @Diana Doane.  I'm familiar with the DEX_ROW_ID column and the use of SQL, I was just hoping I could take advantage of an existing tool rather than write my own SQL.  I've found that with anything GP, it is so easy to miss something when trying to use SQL to do setup tasks that any time I can rely on a proven tool I'm ultimately in a better place.​

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------

    GPUG Summit - Post


  • 19.  RE: Copying Chart of Accounts

    TOP CONTRIBUTOR
    Posted Apr 08, 2019 04:56 PM
    Yes, I agree.  That's why I was hoping to point out what looked like might have been missed in the instructions for importing from one table to another within SQL.  Once yo know the specific setup tables, this import tool within SQL is GREAT!

    For each table you are attempting to export/import you have to edit the mapping for the DEX_ROW_ID to ignore it for the destination.  If you didn't set it to ignore I thought that was why you were getting a read only error on it.  There would be protections to prevent you to transfer data to that column.
    SQL Import ignore DEX_ROW_ID
    If you are already aware of this, sorry for the repetition.  You've probably got it all solved another way by now.  Just thought this might have been the missing piece in getting this tool to work for you.  Historically I did not use the Enable identity insert.  I set it to delete anything existing (it was always blank anyway as it was a new database)

    ------------------------------
    Diana Doane
    Ascend Learning, LLC
    Leawood KS
    ------------------------------

    GPUG Summit - Post


  • 20.  RE: Copying Chart of Accounts

    TOP CONTRIBUTOR
    Posted Apr 08, 2019 09:50 PM
    In my last company many years ago our company wanted to split their single company GL accounts and GL transactions into a main company and lots of puppy dog subsidiary companies.  Then a few years ago the new CFO wanted to simplify everyone's life by re-consolidating both the GL accounts and GL transactions for most of these puppy dog subsidiary companies back into the main company.  This was done by SQL queries.

    The complication was that the account index numbers overlapped in places, so during the re-consolidation my SQL queries had to take this into account.

    This was accomplished by building a custom keys table linking the account numbers to the account indices in the various companies.  The process was successful, we had no problems.

    ------------------------------
    Bruce Strom
    Programmer Analyst
    Associated Grocers of Florida / Supervalu
    Sunrise FL
    ------------------------------

    GPUG Summit - Post


If you've found this thread useful, dive deeper into User Group community content by role