DynamicAccounting.net: SmartConnect

Last week we look at couple of options for using SmartConnect integrations with XLSM file. Specifically, the options were saving the file as an XLSX file and using an ODBC connector instead of an Excel connector. This week, We’ll look at another option, web services.

XLSM file are Excel files that use macros or VBA code. This is most commonly used to reformat or manipulate data to get it into the right format for integrating. SmartConnect doesn’t see XLSM files as Excel file for integration so we have to get creative.

This week’s option is using Web Services. The SmartConnect manual contains specific information about setting up web services. The basic steps are pretty easy:

Setup and test web services via the SmartConnect manual instructions.
Install the Excel Connector
In the XLSM file click the Run SmartConnect Map in the Add Ins menu. This creates a new Excel tab named SmartConnectConfig.
Fill in the first three items (at minimum) on that tab. They are: the name of map, the location of web services, and the tab to integrate.

Now when users push the Run SmartConnect Map button in the XLSM file the data is integrated via web services.

27 February 2012

Weekly Dynamic: SmartConnect and XLSM files Part 1

I’m going to do a couple of Weekly Dynamics on SmartConnect based on some work I did recently.

Let’s start with XLSM files. These are Excel files with macros included. It’s not unusual to get a CSV or Excel based file from a outside source. This could be a vendor, a customer, a partner (like ADP or bank Purchasing Card files) whatever. Often these files require manipulation prior to integration and a common way to deal with that is with an Excel macro. A macro provides a consistent way to manipulate what should be a consistent file.

When the time comes to integrate these files using SmartConnect there is a problem. SmartConnect doesn’t support XLSM file natively. If you pick that you want integrate and Excel file, you can’ t pick an XLSM file as the source. Integration Manager actually has this same issue. So what’s the answer? Well, there are few options. We’ll tackle two this week and one next week.

Option one is simply to save the file in an XLS or XLSX format and integrate it. Saving that way however destroys the macros and creates some additional steps for the user.

Another option is to point your data source to an ODBC connection in SmartConnect. Select an Excel connector and point it to the XSLM file. Because the ODBC connector reads the data out of the file the extension isn’t an issue. User can simply save their XLSM file and integrate normally using the SmartConnect interface.

Next week we’ll look at another option.

02 January 2012

Weekly Dynamic: SmartConnect Data Type

We’ve run into this issue a couple of times recently so I thought I would cover it in a Weekly Dynamic so I won’t forget. This is happening with SmartConnect 10 on GP 10 so I’m not sure if continues forward into future SmartConnect versions.

When setting up an Excel data source for a GL Import, we setup a template for what the Excel sheet should look like. We used a template with data from an actual file to ensure that we had the right headers. We kept getting errors that the transaction didn’t balance.

What was happening was that a notes field in the template had a number of blank records but the first filled record was a number. The integration was interpreting this as a numeric field. When the integration found text in this field it stopped reading lines resulting in the unbalanced record error.

The solution was to remove any numeric records from the template. Since it’s just a template it’s not a big deal to fix but this took a long time to troubleshoot because the unbalanced transaction error leads you in the wrong direction when you start troubleshooting.

27 December 2011

The eOne Dynamics GP and CRM Blog: Troubleshooting SmartConnect Data Issues

Alex Sugak from eOne provide help Troubleshooting SmartConnect Data Issues.