Wednesday, October 3, 2012

The most baffling Dynamics GP issue I've ever seen

I spoke with a customer today who discovered an extremely unusual problem with one of their Dynamics GP batches.  I think it rises to the top of my list to be the most baffling problem I've ever seen.

The client has two company databases--one is for a US company, and the other is for a Canadian company.  While reviewing some of their posted transactions, they found that a batch from the Canadian company had "posted" to their US company.

You read that right.  A batch from Company A ended up posting to Company B.  And this isn't an intercompany transaction.

What does that even look like?

This is a screen shot from the US Company.  Notice that the customer Name field is blank and the Currency ID is CAD.

The customer number 131654 doesn't even exist in the US company, and the US company doesn't process Canadian Dollar transactions.

In the Distribution window, there are no distributions.

And like the transaction window, the customer Name is blank.

Making it more fun is that the distributions posted to the GL in the Canadian company!

So the transaction posted to the subledger in one company, and the GL was affected in another company.

This is the case for all transactions (dozens) in a single batch.

Anyone who understands how GP is designed, how it works, how it posts transactions, and how the underlying company databases are setup would, I believe, say that such a situation is impossible.

I would have previously agreed, but now that I've seen the screen shots and stopped stuttering in disbelief, I can't dispute that it happened; however, I also can't begin to rationally explain how it could have possibly happened.

Like many puzzling situations, there are a few clues, although none directly explains what happened.

First, this batch was imported into GP--it was not manually entered.  So perhaps, maybe, somehow, something in the data that was imported could have resulted in some highly unlikely posting fluke.  But, I'm told the batch was imported using eConnect.  eConnect is pretty rigorous with its validation, so it's unlikely that any transaction data elements could have possibly caused this.  And even if we say that something was odd about this particular imported batch, I can't imagine any scenario that could possibly cause a batch to be posted to two different companies simultaneously.  Imported into the wrong company, maybe, but not two different companies.

Second, the batch was posted automatically using an ISV solution.  The logs from that batch posting solution show that the batch was detected and posted in the Canadian company.  So that is presumably where the posting occurred.  But even if something went wrong or there was a posting error or problem, how could that possibly explain what happened?  The batch posting solution simply logs into the GP company, selects the batch, and then posts it, all within the GP client application.  It isn't doing anything behind the scenes, calling stored procedures, or otherwise attempting to simulate the posting process outside of GP--it's all native GP functionality.

The client has been importing these batches for several months, and automatically posting them with the ISV batch posting solution for months.  So this batch was, in theory, no different than any other batch.  But obviously something went wrong.

I'm only left with one tenuous, and perhaps unprovable theory.  My wild guess is that when the automatic batch posting solution switched GP companies and logged into the Canadian company, something in GP stayed "connect to" or "logged into" the US company.  When the post command was issued for the batch in the Canadian company, somehow GP posted the subledger transactions in the US company, but posted the distributions in the Canadian company.

Or perhaps it's simpler than that.  Perhaps the transactions all "posted" in the Canadian company, but when GP moved the transactions from the Work to Open tables, the subledger transactions were moved from the Canadian company to the US company.  That's probably the best guess I can come up with, simply because it is the simplest technical explanation I can imagine--although even saying something like about Dynamics GP that sounds like complete crazy talk.  Unless it can be reproduced, which I'm guessing it can't, nobody is going to believe it.

This was only recently discovered, so the research is still ongoing and it will likely require a support case to resolve.

But I am pretty confident that it is a contender for one of the most unusual GP support issues.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.


Mariano Gomez said...

My gut feeling tells me this is one of those cases where the company ID value got "intertwined" for both of these companies.

Since you said that these transactions were integrated, you need to watch the INTERID column being imported.

Just some things that come to mind. By the way, I have seen this issue at least 5 or 6 times in the past and it all seems to point to incorrect company ID and/or incorrect INTERID column value.

Steve Endow said...

Hi Mariano,

Thanks for the note.

I am not seeing how INTERID is relevant in this particular case.

Transactions imported using eConnect do not have an INTERID node or value. They are integrated into a single, specific database based on the connection string, and eConnect handles the inserts, so there is no company or database indicator in the imported transaction data.

And there is no INTERID column in the company database tables for Receivables or SY00500 that I can see.

The batch was properly imported into the Canadian company, so I haven't been able to find any clues that would indicate that the imported data is at fault or could possibly have any pointers that would cause the batch to post in multiple companies.

Since they have used this integration for months and this is the first issue that they have found, it would seem that the integration works properly.

Given all of this, my bet is still on an issue with the GP client caused by company switching. Although it is still incredibly bizarre.

I have asked that they restore the Canadian DB into a test company and re-run the import so that the imported data can be reviewed, but unfortunately (although understandably) I don't think they want to invest the time.



David Joosten said...

Hi Steve,
This would seem to be a impossible situation, until you mention auto posting!
Over the years, I have tried many approaches to auto posting and it really is not as simple as it may seem. Dexterity does not have the same access to modal messaging and processes as you would think that it should.
The hint in this scenario is that what you are seeing in the US company is actually from the last part of the posting routine or the "clean-up". This is where GP will move the posted data into the current transaction tables but this is after the GL transaction data has been created.
I know that GP will report a posting routine as "complete" back to the Dexterity host before the routine has actually completed. This happens as GP does a "background" process for posting. I have had to build data verification into my processes to ensure that the posted data has actually completed in the past.
Therefore, my guess would be that the posting was called, it appeared to have been completed and the auto posting went on to change the company. If you where a GP user at this point, you would have received a modal window that would have warned you that there are processes that have not completed, but this is not available to an automated procedure and it would not have known and changed the company.
The developer has the possibility to build in secondary verification for testing if posting is complete. A timed delay by itself is usually not good enough as timing is so variable.
You could also try to use the process server for posting, since it does database context posting, it should not have the same issues but since many of the auto posting applications use the dexterity posting routines directly, it may not offload to DPS even if it is setup.
The other alternative could be to use a separate server for each company or even virtual machines so that they do not have the chance to cross post. You may still see some odd side effects though, such as batches in batch recovery.
I would refer the issue back to the ISV though as it will require some "fine tuning" to get it right which I think is why there are so many auto posting applications available as they each fit a specific scenario.
Hope that this helps.

Steve Endow said...

Thanks David.

Your description of the sequence of events makes a lot of sense.

If the movement of the data to the Open table is one of the last steps, then it would seems that a company switch immediately "after" the batch appeared to post could trigger that problem.

The ISV auto-posting solution is in use by over 100 customers and has processed countless batches for customers with over 200 Dynamics GP company databases, but this is the first time that this issue has been observed.

I agree that there are definitely some inconsistent behaviors in the GP posting processes, and there is definitely great variability in the change company process, so it does make sense that the issue occurs there.

I'll speak with the ISV and suggest that they add an extra check of the batch records before completing the company switch.



dkrizik said...

Hi Steve,
We just had a customer have a situation where the Customer ID and name is completely missing from the customer list, yet they just posted invoices & credits yesterday, and if they run a smartlist, they can see this customer ID and name with transactions. Any ideas?

Steve Endow said...

Hi Donna,

If the Customer ID and Customer Name is blank, and the fields are empty in the RM00101 table, that sounds just as odd, or perhaps stranger than my story. Obviously GP should not have any code or process that could possibly delete those two customer fields, so presumably something external, or a system/database issue wiped out the values?

To possibly explain the SmartLists, if they are seeing the customer ID and name on SOP invoices, then I could possibly understand that, as those values are stored in SOP10100 and SOP30200. But if those are RM transactions, then I can't begin to explain it, since the RM tables don't store the Customer Name.

I would confirm the customer fields are blank in RM00101 and then try running checklinks. (after a full backup, of course)

If check links doesn't fix it, hopefully you can just manually update the customer record via SQL Management Studio.

Sounds like there are some gremlins hiding in GP!