Extending SmartConnect Functionality with Custom .NET Code

Posted By
BDO

Problem

The SmartConnect product from eOne Solutions allows the user to take data from different data sources (text files, Excel spreadsheets, SQL databases, etc.) and import them into Microsoft Dynamics GP, while enforcing all of GP’s business logic. There are times, however, when the data sources can be quite complex, and the data needs to be “massaged” to get it into a format that SmartConnect can understand. For example, while SmartConnect can read text files, it can only understand tab-delimited and comma-delimited text files, and each line must correspond to a record in GP, or SmartConnect must be able to easily group multiple lines into a single record. If the text file is more complicated (for example, header lines followed by detail lines, followed by footer lines), then we need some pre-processing to convert the data to a format that SmartConnect can easily understand. This pre-processing can be done with custom .NET code.

Solution

We will discuss a case where we had to handle data from a text file with a somewhat complicated format, however, we can apply this idea to virtually any data source (for example, unstructured data retrieved from the cloud). As long as we can write VB.net or C# code to read the data and transform it into a format that SmartConnect can understand, we can import the data into Dynamics GP.

In our case, we had to read multiple text files that were in EDI 810 and EDI 812 file formats, cross-reference the data with an Excel spreadsheet, and then import the data into GP. To convert the EDI files into a format that SmartConnect could understand is where the custom .NET code came into play. Below is a visual representation of our data-flow (Figure 1).

Our custom DLL would convert the EDI files into a comma-separated-vales (CSV) text file, and, from there, the SmartConnect map would read the CSV file and Excel spreadsheet and import the data into Dynamics GP. In order to have SmartConnect call the custom DLL, the Visual Studio .net project needs to be built as a Class Library (Figure 2). This will produce a DLL file.

 

The .net code itself will depend on your specific requirements. In our case, we had to extract each record in each EDI file and create lines in the CSV file. What is important is that the end product is something SmartConnect can handle. The DLL must also have public methods that will convert the original data into data that SmartConnect can process. Here is an example of a public method that can be called from within SmartConnect:

namespace ClassLibrary1

{

public class Class1

{

public double DivideAndDisplay(int numerator, int denominator)

{

double quotient = 0;

try

{

quotient = numerator / denominator;

}

catch (DivideByZeroException ex)

{

MessageBox.Show(“Caught exception in DLL! ” + ex.Message);

throw new Exception(“throwing generic exception”, ex);

}

return quotient;

}

}

}

 

We will call these methods from a Map Pre-Task in SmartConnect (tasks that will run before the map). This way, each time the user tries to run the map, the custom DLL code will convert the data first. Afterword the SmartConnect app runs the task, taking the converted data and writing records into Dynamics GP. The map itself will simply point to the converted data as its input (Figure 3).

 

In order for SmartConnect to be able to see the DLL, we have to use the “Script Name Spaces” option under the Maintenance tab. Then choose “Load from File,” and select the custom DLL.  Use the “Validate” button to verify that your code can call the public methods of the DLL. If you have trouble, you may need to copy the DLL into the SmartConnect installation folder (Figure 4).

Summary

By letting your custom .NET code handle the complicated source data, your SmartConnect maps can stay simple and modular. This may also allow teams to work faster, since a technical resource can work on creating the DLL, while a functional resource is creating the SmartConnect map at the same time.

Till next time!


BDO eBook - The Cloud Changes the Game

There are times in the course of your business when you have the opportunity to dramatically accelerate growth and improve day-to-day efficiencies. Recognizing

Download