Monday, April 18, 2016

Just One Example Why Dynamics GP System Integrations Are Not Always Easy

By Steve Endow

I recently developed a custom Dynamics GP eConnect system integration for a customer.  The customer did not want users to have to run the integration manually, so it needed to be fully automated, and they also wanted to ensure that users could not view, access, or manipulate any of the source data being imported into Dynamics GP.

Not a problem, it is fairly easy to address all of these requirements.  So I developed the integration, and asked the third party vendor who is providing the source data for some sample data files. I then waited for the sample data files.  2 months later, they produced a sample data file.  It had several issues, like a missing field, blank values, and some incorrect data in the fields.  The customer asked that they fix those issues.  Great.

But then I reviewed the CSV file.  It looked like this.

So, what do you notice about that sample data file?  Yes, for some reason it has a bunch of dashes in the second row.  They were nice enough to comma delimit the dashes, but they are dashes nonetheless.

When you create a data import or system integration, one of the most important tasks is to properly design your source data, whether it will be sent in files or stored in a database.  It is the lifeblood of any system integration.  Normally it's relatively straightforward, but occasionally it gets complicated or messy.  Or in this case, just odd.

I asked the third party to remove the dashes, since we obviously have no need for them.

Their response:  We can't.

They explained that the only way to remove the dashes is to remove the column headers as well.

Yes, you read that right.  In the year 2016, a fancy "cloud based" system is unable to produce a simple CSV export file with column headers and data, but without a bunch of useless dashes.

So you might say, "No big deal, just remove the dashes in your import or use a file without headers".

And that is exactly what needs to be done.  But remember that this integration needs to be fully automated and the customer does not want users to have access to the source data files.

So by introducing this seemingly small requirement, the third party has just increased the complexity of this integration to the point where it would be beyond the knowledge and skill of many end users.

If a customer were to create this integration on their own, does the customer know how to filter a data source?  Do they understand ordinal references?  Do they know to map field names to unlabeled columns?  If they use ordinal references, do they understand the increased maintenance that approach requires, such as when they need to add a new field?  This is a trivial example, and it isn't hard to work around this issue, particularly with experience or some assistance, but it does require some skill that may not be obvious to a non-technical user.

In this case, I'm developing the integration for the customer, so I will handle this annoying issue with the third party data file, but it's just one of a hundred seemingly trivial details that collectively can make System Integrations technically challenging.  I see these types of issues every day, and some can cause production integrations to stop working.

If you need a simple Data Import, such as GL accounts, customers, or vendors, I would encourage customers to try a Dynamics GP import using one of the common tools.  But just be aware that when you move into transaction imports and true System Integrations, you should expect to encounter several complications that collectively may require some skill and experience to address.

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

You can also find him on Google+ and Twitter


Hila DG said...

Hi Steve,

The headline made me & my colleague laugh so hard! only one example?

Anyway, we are now working on a Dynamics GP System Integration, and we found out that it is never easy. our question is:
We want the integration to pick up which database the data should be going to. We are using Scribe, and we want to add a check on top of the Connection. Let's say, we add a column to the Excel source which states which database the data should go to. If the connection is to a different database, we want it to exit with an error.
Is that possible?

Thanks a lot for your help!

Steve Endow said...

Hi Hila,

With the .NET eConnect integrations that I develop, my customers can have a column in the data indicating which database each record is for. eConnect doesn't require a persistent connection to a database, just the SQL Server--it just imports each record into whichever database you tell it to.

Unfortunately I haven't used Scribe, but I would assume that you could so something similar--have it read the database column and import into the correct DB. It should only need a connection to the SQL Server, and allow you to switch databases for each record.

I guess I'm assuming that is what you want, but I suspect you could have it error out if it detects a change in the database, but I don't know how that would be done in Scribe.

I would recommend checking with your GP partner or Scribe to figure out how that is done.