It's a trite phrase, but I constantly say that "The Devil is in the Details" when it comes to developing integrations.
Some integrations are very simple, while other integrations present you with situations that leave you scratching your head. And some integrations that you think will be very simple turn out to be surprisingly complex for reasons that you would be hard pressed to have anticipated.
One of my clients recently contacted me regarding a small issue they were having with a Purchasing Invoice integration that I developed for them. The integration reads data from a CSV file, then creates Purchasing Invoices and automatically matches them to receipts. It processes thousands of invoices a month and has pretty extensive logic to perform the automatic receipt matching process.
We worked through dozens of challenges and quirks over many months to the point where the original code was spaghetti, but after refactoring most of the integration code, the new version of the integration worked great and was able to handle all of the oddities required by the import process.
Or so we thought.
After processing hundreds of thousands of invoices, the client came across a new requirement.
The issue? One of their vendors is issuing all-numeric invoice numbers that begin with a zero.
No big deal, right? The Dynamics GP Document Number is a text field, so a leading zero shouldn't be an issue. And in that respect it isn't. My integration can process such invoices fine.
But one of the features of the integration is that if any errors are detected in a data file, it outputs a modified version of the CSV data file so that a user can open up the file, make some edits, and reprocess the file. Very simple.
Except when the vendor uses an all-numeric invoice number with a leading zero. If a user opens a CSV file in Excel, an invoice number of "012345" is displayed as "12345". The leading zero is dropped by Excel. The user then makes their edits, saves the CSV, and reprocesses the file, resulting in invoice 12345 being imported into GP rather than 012345.
Technically this isn't an issue with my integration--it's an issue with Excel. But because Excel is the best tool for editing CSV files, we're stuck with this limitation. Unfortunately, there is no easy way to get Excel to retain the leading
zero in a CSV file. There is at least one workaround, but it is tedious
and impractical for my client.
My client reviewed this issue internally and had a discussion with the vendor, and apparently the vendor is unwilling or unable to change their invoice numbers, and my client apparently needs to retain the leading zero on the invoice number.
So I now have to modify the integration to add an alpha character in the CSV file to invoice numbers that begin with a zero. If the invoice number is "~012345", Excel treats the value as an alpha text value. I then have to make sure that this character is removed from the invoice number prior to importing the transaction into Dynamics GP.
Fortunately my integration is designed in a way that makes this change quite easy. But it is a good example of just one of the many unforeseen details that can make an integration more complex than expected.
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.
http://www.precipioservices.com
No comments:
Post a Comment