AA Table Changes in GP 10 SP2 / Feature Pack - Update

Last month, I had written about the table changes in AA after GP 10 SP2.

Below are some more details, which might help those of you who have custom reports reading data from Analytical Accounting. Thanks to Rubal for detailing these out.

Earlier there were only AAG3XXXX series of tables for AA codes with GL Transactions. They contained data for both Open Year and Historical Year data.

Now, the Open Year and Historical Year data is stored in different tables. For Historical Years data AAG4XXXX series of tables have been added. 

There is one historical table corresponding to each open table. Example - For table AAG30001, new table AAG40001 has been added for historical data.

 

New Table Name	Corresponding Table for Open Year Data	Description
AAG40000	AAG30000	This is the Header table. It contains Journal Entry number and AAHdrID
AAG40001	AAG30001	Account Distribution of transactions
AAG40002	AAG30002	Assign Percentage details for the transactions
AAG40003	AAG30003	Dimension Codes used with the transactions

 

 

Tables in GP

New tables Added for AA Alias

Aliases for Dimension codes was a cool new feature that was added in the Feature pack. (Create a grouping of Analytical Accounting Transaction Dimension codes as defaults when entering analytical information on a transaction similar to the account alias in General Ledger. A default combination of transaction dimension codes will help increase speed and accuracy for data entry and enable users to create more than a single set of defaults for the transaction dimensions.)

This information is stored in:

New Tables

Display Name	Technical Name	Physical Name
AA Alias master	aaAliasMstr	AAG00800
AA alias transaction Dimension Code	aaAliasTrxDimCode	AAG00801

 

Modified tables:

 

Physical Name	Modification
AAG30002	aaAliasID field added

 

AA Error : "Cannot insert duplicate key in object"

If you are using Analytical Accounting and get the error which is something like "[Microsoft] [SQL Native Client] [SQL Server] Violation of PRIMARY KEY constraint 'PKAAG20003.' Cannot insert duplicate key in Object 'AAG20003'."

The table name could be different.

Your first steps should be to check out KB article "897280" which has a script to fix this error. This should solve the problem 90% of the times.

However,  if you still get the error, this means the numbers are correct in the AAG00102 table and a duplicate key error is often caused by an invalid record in the AAG2000X  series of tables.

Run the following script against your company database -

select * from AAG20000 where aaSubLedgerHdrId = 0

select * from AAG20001 where aaSubLedgerHdrId = 0

select * from AAG20002 where aaSubLedgerHdrId = 0

select * from AAG20003 where aaSubLedgerHdrId = 0

If you get any results - those are the entries causing the problem. The 0 in the aaSubLedgerHdrId is not a valid record since the next number needs to come from the AAG00102 table. 

Hope that helps!

Microsoft Surface and Dynamics!

Am I dreaming or could in a few years, we could be designing and using Dynamics Applications that worked on Microsoft Surface ?! I have been in love with Microsoft surface ever since I saw the first demo. Apple ofcourse kind of ruined it for Microsoft here too .... but thats another story.

I always thought Surface made sense only in Casino's and five star Hotels ... but this is fantastic!

Read more on Waldo's blog here

 "we got a new demo on the Surface. You know the Surface? It's a multi-finger-touchscreen-coffee table (yes … table (OK, not "coffee" table )) from Microsoft. Now, they are developing a new Warehouse-application on it. It is showing a map of the warehouse (the different zones, bins , locations, …). It is actually an intuitive application where you can move items, create transfers and other warehouse activities by dragging and dropping … . It's linked with dynamics, so it generates the necessary activities for your warehouse employees… . Cool! Next, they showed the integration of it with the Business Process Manager … . The Surface is delivering nice graphical capabilities, where it's gong to be quite intuitive to work with the apps that are run on it."

'Feel Good' Tech Support

So often we hear stories of bad tech support, so once in a while when you get good tech support, it might not be a bad idea to mention it.

Greenshades - with their e-Filing center has been popular with Great Plains customers for a while. Last week I got a call from a customer who was on a deadline and getting an error while filing. The customer was frantic and his sense of urgency transferred to me. He said he was trying, but just couldn't get in touch with Support.

I called the Greenshades tech support people. It took me about 30 minutes to wait in queue before I could speak to the tech support guy.

I was ready to blurt out the story with urgency befitting that of a person being rushed to the ER. However, as soon as the person on the other side of the phone said - "Hello there!" (Calm voice, possibly smiling), all I could do was just mention the problem. We got the problem resolved pretty quickly and it was all over.

This has happened to me multiple times over the years, with separate tech support people at Greenshades. Each time I call, and there is an emergency from a customer, and each time I hear a calm voice on the other side, with somebody who is smiling, and get a quick resolution.

Greenshades has the happiest group of Tech Support people I know of.

AA Table Changes with GP 10 SP2

If you are using GP 10 SP2, FRx Cannot Report on Analytical Accounting History Following an Year End Close.

With Microsoft Dynamics GP 10.0 Service Pack 2 - Microsoft has made some Database changes . When the year end close process is run - it automatically moves the AA transactions from the AAG30000 tables to the AAG40000 series tables.

FRx does not read from the AAG40000 series of tables and therefore, cannot report on historical data for Analytical Accounting. You get the error - "This report yields no data".

There is a hotfix available from Microsoft (hotfix 962862), but you need to contact Microsoft for it, its not available for public download yet.

I have not done any tests yet, but this should lead to performance improvements in your AA Reports. This would also mean that you need to update your custom AA Reports to read data from AAG40000 series tables.

1099 Minimum Amount

As per the IRS, the 1099 form must be issued to Vendors for amounts of $600 or greater. Somebody recently reported that the 1099s were not being produced for Vendors with amounts which were exactly $600.

By default, the minimum amount for 1099’s is set to be $600 (Microsoft Dynamics GP menu >> Tools >> Setup >> Purchasing >> Payables >> 1099 Setup button)

As per the documentation this is the “minimum amount to report on the selected type of 1099 statement.” So my guess was that vendors with amounts exactly $600 and greater would be reported in the 1099 Lists. I checked and in V9, this was the case. However, this seems to have changed in V10. V10 does not pick up Vendors with amounts exactly $600 in the edit list, if the minimum amount is set to be $600.

The solution that I figured out was to set the Minimum amount to be $599.99. This ensured that the vendors with 1099 amount $600 were reported.

Have any of you noticed this change?

Attachment Detector

One of the most embarrassing things in life is forgetting to send an attachment with an email, when you write down the whole 100 words of text describing the attachment. It was most heartening for me to know that a lot of people felt that way, and some came up with a solution.

There’s one solution out there for Outlook, and one for Gmail. The tools – “politely remind you to attach a file if it finds the word "attach" in your email and no actual attached file.”

Outlook Attachment Reminder via Lifehacker

Gmail's Forgotten Attachment Detector