Wednesday, May 18, 2011

Fun eConnect Bug: Check those field lengths!

Today I deployed an updated version of an AR Invoice eConnect integration. The customer has used the integration previously, and it worked fine, but when we ran it with a new batch of data, the Edit List report indicated that some transactions had missing distributions or incorrect distribution amounts.

Puzzled, I checked the transactions and their distributions in Dynamics GP, and everything looked fine.  But when I ran the Edit List again, the same errors were listed--except for any transactions that I had opened and viewed.


So I queried the RM Distribution Work table, RM10101 to see what was going on.

For the one transaction that I had opened and viewed in GP, I saw four distribution lines instead of the two that should be present.  I was pretty sure I didn't import four lines, but I wasn't sure where the other two came from.

After looking closely at the four records, I noticed one discrepancy.  The DOCNUMBR field value for two of the lines was different than the other two distribution lines.  One had the full document number that was imported, but the other had a truncated document number, missing the last 4 characters.  And the account indexes for the two sets of distributions were different.

And that is when it dawned on me.

Back in 2008, I wrote this blog post discussing how eConnect handles character values that exceed the length of the eConnect / GP field.  Normally, it truncates character data values without any warning or error and proceeds happily, letting you discover that you have some data missing.

Well, in this case, the client had an invalid AR invoice number that was 20 characters long, due to a change in data formatting.  eConnect happily imported the invoice, truncating the invoice number to 17 characters.

But, it appears that there is a minor 'bug' in eConnect 10, as the AR distribution imported the full 20 characters into RM10101.  No truncation!

So, in the RM10301 Sales Work table, we have a 17 character invoice number, and in RM10101 Distribution Work table, we have a 20 character invoice number.  Naturally, when the GP edit list tries to match the invoices to distributions, it can't match the two numbers, so it appears as if the invoice does not have any distributions at all.  And when I opened the invoice distribution window, everything looked fine because GP was defaulting distributions based on the customer account settings.  And when the distribution window is opened, GP creates new distribution records, but using the customer default accounts.

In short, the distributions that were imported with the full 20 character invoice number are "orphaned".  They are not associated with any invoice, and even if you delete the invoices, the distributions will not be deleted.  They will have to be cleaned out manually or via Check Links.

So, the lesson here is that you shouldn't rely on eConnect to manage the length of your character field values.  Ideally, you should include field length checks in your integration validation code to detect data values that may exceed a GP field length.

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

No comments: