How Do I Add ACTNUMBR_7 and ACTNUMBR_8 Columns to GL00105?

(0) Share

Report

Posted on by bootlogic

We have some back-office software that among other things, transfers invoices into Microsoft Dynamics GP. We are testing an upgrade of that software and when trying to transfer invoices, we are getting an error about a missing ACTNUMBR_7 and ACTNUMBR_8 column. (ours only goes to ACTNUMBR_6). We did a trace and they are trying to get those columns from GL00105 table. Seems like they forgot that in some implementations those columns do not exist.

We were hoping to complete the upgrade on 9/11 and am looking for a workaround. Unfortunately I'm not a GP expert which is why I'm asking the question here. We have reported the bug to the customer and they said they are working on it but it could be a while. This is a somewhat minor part of functionality of this whole upgrade and I'm looking for workarounds.

Is it possible to add the ACTNUMBR_7 and ACTNUMBR_8 columns (via straight SQL) without corrupting GP? Here is a sample row from our GL00105.

Would I need to add these columns also to GL00100?

ACTINDX	ACTNUMBR_1	ACTNUMBR_2	ACTNUMBR_3	ACTNUMBR_4	ACTNUMBR_5	ACTNUMBR_6	ACTNUMST	DEX_ROW_ID
1749	3090	4	0	0			3090-4-0-000	3164

Again I'm not an expert in GP tables. I've went through a few GP upgrades but this isn't my specialty. If this is a way off the wall question, we can wait until they release another update that fixes this bug.

But this is the only thing we have came up with in our testing and was hoping for a workaround.

Thanks

Categories:

Microsoft Dynamics GP

All responses (8)

Answers (1)

Suggested answer

David Musgrave MVP ... 13,928 Most Valuable Professional on at

Like (0)

Report

RE: How Do I Add ACTNUMBR_7 and ACTNUMBR_8 Columns to GL00105?

Hi

What does your table structure for the GL_Account_MSTR (GL00100) contain?

The Account Framework (the maximum number of segments and the maximum size of each segment) is controlled by these two tables in the system (DYNAMICS) database.

SY_Account_Definition_HDR (SY003001)

SY_Account_Definition_LINE (SY00302)

When you install a Dynamics GP system, the Dynamics Utilities runs a Synchronize process to match the table definitions in the DYNAMICS.DIC dictionary to the setup.

If the Account Framework does not include at least 8 segments, then those fields will not be available in many tables.

So the question is.... do you need to increase your account framework systemwide?

Is there a mismatch between the settings, the dictionary and the tables?

Is it only the GL00105 table missing the columns?

GP Power Tools Database Validation can check for and fix many account framework issues including where tables have the incorrect definitions.

Also, if you drop and recreate a table which has data in it, and GP Power Tools is installed, it will offer to backup and restore the data so the table (and its associated zDP stored procedures) are in the correct format without any data loss.

For manual steps to recreate a single table see

https://winthropdc.wordpress.com/2010/11/03/backing-up-and-restoring-data-when-recreating-sql-tables/

** Do not manipulate the tables in SQL yourself as you will not properly match correct table definition and will not update the stored procedures to match **

Regards

David
Richard Wheeler 75,730 on at

Like (0)

Report

RE: How Do I Add ACTNUMBR_7 and ACTNUMBR_8 Columns to GL00105?

There will be other areas of GP that are going to have no idea these new columns exist. So if you go to Cards->Financial->Accounts you will not see them nor will you see them in any inquires screens. GP actually posts using the ACTINDEX column. There are relationship between the GL00100, GL00105 and GL40200 tables.You will run into issues with check links not to mention when performing an upgrade. Additionally now your reports dictionaries will be out of sync as well as your dynamics dictionary. Using Reformatter is your safest answer to this. In my opinion changing the table structure of the GL00105 table is asking for trouble. I would definitely try this in a test company first and only implement in production after thorough testing. The other possibility is to create an ancillary table and point your customization to that table. It is always wise to not mess with the structure of any GP tables.
bootlogic 5 on at

Like (0)

Report

RE: How Do I Add ACTNUMBR_7 and ACTNUMBR_8 Columns to GL00105?

Thanks Bill,

We did as you suggested and it appears to be working fine. Thanks for your help!
Verified answer

Bill Campbell 22,647 on at

Like (1)

Report

RE: How Do I Add ACTNUMBR_7 and ACTNUMBR_8 Columns to GL00105?

To answer this, you will need to see what happens when you Post the transactions you are entering. IF the BackOffice app is only looking to see if there is a values in Seg_7 and Seg_8 then this might be an acceptable solution for now.

I would like to see what happens when you post from anywhere else in the system - see if anything else is effected by the new columns in the GL00105 and GL00100
bootlogic 5 on at

Like (0)

Report

RE: How Do I Add ACTNUMBR_7 and ACTNUMBR_8 Columns to GL00105?

Bill,

My reply to Richard crossed with your answer.

1) The backoffice app doesn't add the Seg_7 or Seg_8 fields. They just have a query that looks for them which is the resulting bug.

2) The current Seg_5 and Seg_6 columns in the database are blank.

3) Here is the account format setup

I totally agree they need to fix their backoffice tool and they are working on it. This is part of a major upgrade and this is the only problem we have found and was hoping it wouldn't stop the big upgrade this weekend with the suggested tweaks. What I was curious was if adding these columns would break GP functionality?

We have multiple companies within GP but there is only one company we transfer invoices into from the backoffice product we are upgrading.

We don't yet know when they will release an "official" fix for the bug.
bootlogic 5 on at

Like (0)

Report
RE: How Do I Add ACTNUMBR_7 and ACTNUMBR_8 Columns to GL00105?

Max segments shows 6. We are only using 4. The max segments is greyed out and we cannot change.

As a test of the bug, we inserted 2 columns called ACTNUMBR_7 and ACTNUMBR_8 similar to this in both GL00105 and GL00100 tables.

alter table gl00100 add ACTNUMBR_8 char(11) not null default ('')

That fixed our issue and the invoices transferred just fine with the appropriate accounts. Will this be a major issue with other GP processes if we do this until they release their fix?
Suggested answer

Bill Campbell 22,647 on at

Like (0)

Report

RE: How Do I Add ACTNUMBR_7 and ACTNUMBR_8 Columns to GL00105?

While I agree with Richard in broad strokes, I have a few comments or questions to pose.

Question 1- does the backoffice app actually add a value to the Seg_7 or Seg_8 fields? If so, that would be more problematic.

Question 2 - If it is just looking for a value - would a value of ZERO work?

Question 3 - what is the Max Account Length? This could also be a factor in adding the additional Segments.

If the Segments are only being added to complete the BackOffice app integration, then you might be able to just add the Segments (in the application) from the Account Format Setup window.

Be sure to work with the client on this and make sure they understand adding these segments to their accounts can create other issues related to data entry and reporting that may not be considered at this time. While not complicated to complete - there are issues that can come up to bit you in the "end" if you are not aware of them.

I personally would go back to the provider of the BackOffice tool and get them to look at the GL00105 and figure out the correct number of segments in each of the databases - remember every company can have a different configuration - one site - multiple answers. Better to do this calculation from their application than requiring the client to incease the segment count in the company database simply to fit this tool.

Just my two bits worth.
Richard Wheeler 75,730 on at

Like (1)

Report

RE: How Do I Add ACTNUMBR_7 and ACTNUMBR_8 Columns to GL00105?

If you go to Tools->Setup->Company->Account format what are your setting for account length and number of segments? The number of segments must be at least 8 to accomplish what you are trying to do. If not, contact crgroup.com about their Reformatter product.