SmartConnect - Top 5 Quick Tips & Integration Improvements

SmartConnect is the integration tool of choice for the ALaCarteGP team.  There are a few different options out there for integrating data into GP, an important piece for an ERP system and part of which makes GP so great, however, the eOne Solutions team has found the perfect balance of creating a solution that's both end-user friendly and powerful at the same time.

I could go on and on discussing the great benefits of adding SmartConnect to your company's toolkit but today, let's focus on a few of the quick and easy to implement tips & tricks to improve your integrations.

1. Pre-Map Validation Check

Much of developing integrations is getting clean data in and reducing the number of things that can go wrong during import.  With "Pre-Map Validation" tasks in SmartConnect, you can accomplish both.

Under Tasks>>Map Pre Tasks>>SQL Validation, you can add checks to the data import to ensure data exists or doesn't exist at the time of import.  If either condition is true, you can halt the entire map, display a report w/ your matches/mis-matches & clean things up before importing again.

In the example below, we've added data checks on a typical vendor import to 1.) Ensure the vendors that we're importing DO NOT already exist & 2.) Ensure that the payment terms that we're importing DO exist.


In the setup, we've essentially said, ensure the field 'Payment Terms' from our source matches an option from the payment terms setup table in GP.

2. ODBC Connection instead of a direct text or Excel connection

This is more of a integration principles tip but SmartConnect provides a beautiful interface for this option.

When setting up integrations from a source-file, say Excel, you want to go w/ the obvious of selecting 'Excel' right?  Well... Not really.

When you select the Excel option, that's it, you're done.  It's quick, simple & straight-forward but it's NEVER going to get any better or provide you with any flexibility past that point.  What we'd suggest is setting up an ODBC connection w/ the Excel as the source.


The ODBC option takes just a moment to figure out but once you do, it's very straight-forward.  With it, you can add things like...

  • Omit blanks lines from file

  • Select only from file that are between a certain range

  • Omit lines from file where debits <> credits

  • Sum fields from file

To set one up, select 'ODBC Connection' from the Data Source drop-down list, select Excel as your ODBC option & ultimately select your Excel file. 


(Tip! With this interface, it's sometimes easier to copy & paste your windows explorer folder path into the 'Database Name' path & select 'Enter' to navigate.)

3. Email Notifications

Roll an integration out to end-users or want to monitor a scheduled integration and want to monitor those integrations if they fail?  The best way to do so is with emailing!  With emailing, you can cc youself anytime an integration fails (or completes) so that you're on top of when things are awry and require attention.  Nothing makes you look like you know what's going on more than if/when an integration fails that you're already on the phone with the end-user.

Example template that would include successes, failures, &amp; detailed explanation of what causes the failure.

Example template that would include successes, failures, & detailed explanation of what causes the failure.

4. 'Fix' Option in Maps

How often does your integration have dozens of successes and then one failure... "Uuuugh" you say as you close the integration, open the source-file or GP, make the changes, re-open the map, re-run the integration, see if it works, rinse & repeat.

Well... The 'Fix' button is your savior.

With the fix button, if things fail during an integration, you can open the fix window, make the corrections right on the spot & then re-run JUST THE FAILURES at that time and without leaving SmartConnect. Magic!


5. Calculations

When developing integrations that end-users will be using on a routine basis, it goes back to the point made in our first listed tip... You want to setup the integration in a way that leaves the least amount of things that can go wrong as possible.

One way that you can do that is with 'Calculating' columns.

Example.) You create a journal entry integration and want the JE date to always be the date of the integration.  Instead of adding the field 'JE Date' to the Excel file that they populate, which they'll 100% put in the wrong date at some point in time, you can create a calculating column in SmartConnect & automatically pull the current date.

SmartConnect-Calculation-Data Format-VBScript.png

Bonus! Push to GP - File (.XML) to Troubleshoot

Oh don't we all love troubleshooting.  You’re on day 3 with developing an integration that should've only taken you an hour and you're about to explode as you keep receiving the error "Unexpected Error Occured".  To ease the woes of the unfortunate, SmartConnect provides us with the ability to push to a "File".  This option essentially surpasses the rules that eConnect would yield and pushes the data to an XML file.  We can use this XML file to view EXACTLY what SmartConnect is trying to push through.

With this, we can easily pinpoint what's going wrong when we see the value "Bill Gates Pension Accrual" being passed through the "JE Date" field.



SmartConnect is fantastic and these tips only touch the surface of what the tool has to offer.  If you'd like to learn more about the eOne Solutions SmartConnect application, we'd be happy to discuss or you can take a look for yourself on eOne's site.

SmartConnect and integrations are one of our main focuses here @ ALaCarteGP so please be sure to stay tuned for future #Integration related posts & also take a look at a very small sample of some integrations projects that we've worked on in the past.

Happy integration building!