Open Forum

Like what you see? Discover the benefits of the GPUG Community. Learn More

1.  Updating Account Category using Excel table

Posted 10-11-2017 01:03 PM
​Our organization has been using GP for some time, but lacks an in depth understanding of the product.  Very recently, we starting using JET reports and realized that we could significantly streamline our reporting if we modified the account categories to match our financial statements.  We have almost 3000 accounts that need updated, and we already have in excel (for pivot reporting purposes) the account category we'd like to change this to.  Can anyone suggest the best method to use the excel table to update the category rather than manually having to input this?  Are there other tables we need to be concerned with?  Thanks for your help.

Lori Farkas
Sisters of the Holy Cross

2.  RE: Updating Account Category using Excel table

Posted 10-12-2017 02:11 AM

There are two steps.
1. Import the account categories using Table Import or enter the account categories. You will need a column with a category number. If any of the account category descriptions match an account category in GP, you won't want to import those. You can query GL00102 to get the last category number. Start your new categories with the next number.
2. Use Integration Manager to update the chart of accounts with the categories. There is a sample integration for updating account numbers. If you need help, though, respond to this and we'll get you more help.

Charles Allen
Senior Managing Consultant
BKD Technologies
Houston, TX

3.  RE: Updating Account Category using Excel table

Posted 10-12-2017 09:21 AM
The GL00100 contains the Account Category Number in the ACCATNUM field (The GL00102 maps the number to the name).  The GL00100 however does not contain the Account Number per se.   The ACTNUMST field in the GL00105 is the account number and that is linked to the GL00100 by the ACTINDX in the two tables.  Another alternative then is using your excel spreadsheet to generate update statements (  Typically a rather dangerous endeavor, but since the account category is really as you indicate for reporting, not something too terribly dangerous to update for those familiar with SQL and the links of the Account Tables in GP.  I hope this provides you enough information to know there are some alternatives and that it is definitely possible.

Heather Maneiro
Senior Consultant
Implementation Specialists (IS)
Hawley MN

4.  RE: Updating Account Category using Excel table

Posted 10-12-2017 02:13 PM
There's a third party product called TitaniumGP Copy/Paste.  You can easily copy from Excel and paste to the Account Maintenance window in GP, as long as you have one column for Account Number and another column for Category in your Excel file.

Lucy Lu
Southwest Key Programs, Inc.
Austin TX