Archiving Integration Source Data

Yesterday I received an e-mail from a client inquiring about an AP credit memo that did not import into Dynamics GP properly--it imported into GP as an AP invoice.  After looking into it, I confirmed the problem and then looked at my eConnect integration code to determine the cause.

It turns out that I had made a change to the code back in May during testing, and that change caused the problem that was identified this week--all credit memos were importing as invoices.  Oh boy.  So that means that the integration has been running for 5 months with the problem.  The bug wasn't detected during testing, and somehow wasn't detected in production either.

Fortunately, the bug only affected AP credit memos, and it seems the client has processed very few of those, which explains why it wasn't caught earlier.

So now that I know the cause of the problem, other than fixing my code, how do we correct the credit memos that were imported into GP as invoices?  Well, in this case, the data in GP has no additional clues to tell us that an invoice should have been a credit memo (the invoices and credit memos look the same, only the transaction type is different), so my only option was to go back to the source data.

Fortunately, whenever I develop an integration, I keep a copy of every single transaction that I import.  If the source data is in SQL, I keep a transaction log table to record all of the data I received from the source system, when the data was imported, and the corresponding GP transaction that was created.  If the source data is Excel, I write back a status field to the Excel file, and then archive the file.  And if the source data is CSV, I just archive the file.

For archiving source data files, I use a third party .NET library to create zip files, so after a file has been imported, I move the original Excel or CSV file to a monthly archive zip file.

In this particular case, I had a copy of every source CSV file that had been provided by the external system.  To find all of the credit memos, I simply unzipped all of the CSV files to a single directory, then ran this interesting DOS command (that I had to look up):

copy *.csv AllFiles.csv

This automagically combined the hundreds of source CSV files into one CSV file.  And once I opened that single CSV file in Excel, I sorted the data to identify all of the credit memos that had ever been sent to GP by the external system.

In just a few minutes, I found that there were only four transactions that were affected, all of which were imported in September, and I was able to quickly document their voucher numbers so that the client could void them.  Quick and simple.  (Well, except for the embarrassment of finding the bug in my code.)

Previously, I've had situations where the vendor for the external system claimed that a file was sent when it wasn't, or that the file contained certain data when it didn't.  So this approach of keeping copies of all integration source data has been very valuable over the years.