Friday, May 20, 2011

eConnect Integrations: 90% Data, 10% eConnect

eConnect for Microsoft Dynamics GP is fantastic.  It's easy for a .NET developer to use, it's fairly comprehensive, it's fairly well documented, and it's relatively fast.  Except for the occasional missing field, unsupported transaction or bug, I love working with it.

Unfortunately, I have found that among non-technical folks, there is often a misconception about what it takes to develop a complete eConnect custom integration.  I am guessing that they think that eConnect should be no different than Integration Manager, where in a few hours or less, you should be able to produce a fancy integration.

It's rarely that simple.  While trying to explain to a GP partner why a POP + SOP + Project Accounting integration would really take 40 hours to develop, I came up with this way of describing it:  eConnect is the easy part.  That's the 10% of the equation.  It's a standard API with good documentation.  It's a known quantity.  The other 90% is dealing with the client's source data file, developing business logic for processing the data, and handling exceptions in that data.  And don't forget testing, debugging, minor changes, installation, and documentation.

Sure, I have had situations where customers provided a very simple and clean data file that went straight into GP.  No data transformations, no lookups, no exceptions or errors--a perfect integration.  Those are simple and easy.  But those situations are generally rare--in part because such integrations can often be done with Integration Manager or even a table import (not that I'd recommend a table import...).

The real power and some of the greatest benefits of custom eConnect integrations comes with complex integrations.  And with such integrations, it isn't eConnect that provides the benefit as much all of the extra logic that can be included with custom integration.  For example, importing a customer with eConnect is essentially always the same.  Customer object, address object, populate the properties, and send it off to eConnect.  Simple.

But when the customer data is in multiple files, or different data formats, or the address isn't parsed at all or is incomplete, or the data needs transformation, or has exceptions that need to be handled, that is when additional custom .NET code can come into play to make the integration much more valuable than a generic data import.

A timely example:  I had a phone call today with a client that receives over 600,000 cash receipts every month, a number that is rapidly growing.  What they actually receive is a single giant payment from an external trading partner, and that trading partner also provides them with a 150MB text file containing the list of every customer payment.  Not only do these cash receipts need to be entered, but they also need to be applied to invoices--so think of it as over 1,000,000 transactions.  Every month.  They would like to automate this process to eliminate the 3 full days of work that are required to enter and apply the cash receipts.  In concept that doesn't sound so bad, and eConnect can obviously handle that.

So I then ask if there are any exceptions in the data that are handled manually, or problems that require attention before the cash receipt can be entered or applied.  "No, not really" the customer answered. Hmmm, that seems unlikely.

About 30 seconds later, I ask them which column in the data file is the Dynamics GP customer ID.  It turns out that the customer ID is not in the data file--only the customer name.  Sometimes the customer name is the same as GP, sometimes it's similar, and sometimes it is completely different.  The accounting staff manually looks up each customer name in GP to determine which GP customer sent the payment.  I don't know about you, but I would call that a giant 600,000 line exception.  Okay, so we can setup an easy method to map the customer name in the data file to the GP customers.  Not a problem, but note that this has nothing to do with eConnect.

I asked about the additional worksheet in Excel where some of the data had been separated.  "Oh, that's a different type of cash receipt, we handle those separately."  So now we have regular cash receipts, and special cash receipts.  Another exception, to be handled well before eConnect gets involved.

And what about the payment applications?  Any issues with those?  "No, those are pretty standard."  But, the first cash receipt we look at in GP is not applied to an invoice, because there is no invoice available for that customer.  Another exception.  And then we find an overpayment.  Another exception.  And then an underpayment.  Another exception.  After discussing overpayments and underpayments, they explain that they want debit and credit memos automatically created, and then applied to the customer's credit or debit balance.  But only if the difference is within a certain threshold, such as under $100.  If the difference is greater than $100, it should appear on an exception report requiring a review.  Makes total sense, and mimics their manual processes.  But eConnect is still only peripheral at this point.

The requirements and exception list goes on, and I now have a good idea of what needs to be done.  And you know what?  The LEAST of my concerns is eConnect.  It's dealing with the 150MB data file and the thousands of exceptions that the integration is going to need to handle during the import. 

And when an exception or error occurs, I'll need to create an exception file so that they can deal with just the 1,000 records with errors instead of swimming through the massive 600,000 line data file.  And don't forget reporting--they will need a report or log listing how the cash receipts were imported and the invoices to which they were applied.

It's a fantastic project, and I can't wait to help them turn a 3 day ordeal into a simple 15 minute process.  But roughly 90% of my work will be wrestling with the source data, while only 10% will be working directly with eConnect.

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: