I’m using a simple Excel File source to integrate data with Microsoft Dynamic GP Customers & Addresses.
The data looks fine in the Excel file, and SmartConnect doesn’t seem to have any issues reading it. The preview shows as expected, but no data is exported.
If you’d like to see a video explaining why SmartConnect runs but exports no data and how to fix it, see below or click here.
The Problem
For testing, I switched the Destination to be “export to text file” and when the map runs, it says the destination is successful. However, when looking at the destination file, no data is actually exported.
In the screenshot, the output CSV file is overlaid against SmartConnect, showing it could read and had successful records, but the output file only shows the Header column data and not the actual output data expected.
Troubleshooting The Issue
Typically, the underlying issue is that the export node has a Restriction entered. Perhaps there is a mistake in the coding, therefore the restriction ends up restricting out ALL of the data which would cause this issue. However, this integration wasn’t using a Restriction and was still set to the default “return true”.
We might suspect a “before document” task is silently cancelling the record output, but there wasn’t one.
That SmartConnect can read the data, but not output the data, would lead to something being “odd” with the data in the selected Key Fields. On the integration, AddressCode & CustomerNumber are used.
Why would we suspect the Key Fields and not any of the fields in the file?
When SmartConnnect processes the source data, it needs to know what grouping of data creates one “document”. It could be that one record is one “document”, or it could be multiple records that would create one “Sales Invoice” document that has a Header & Lines and is all processed as one document.
To do this, we pick the “Key Fields” for the integration to tell SmartConnect how to group the output data as a “document”.
When the source is processed, SmartConnect creates a DataTable that defines each “document” according to the Key Fields.
In this example and data, choosing CustomerNumber/AddressCode would give us five “master” records to match against the data. Or if we just choose Customer Number, SmartConnect would create one “master” record and show us one document with five records processed with it.
In our testing, unmarking the AddressCode field and re-running the integration had the same issue: no output data.
Next, after unmarking CustomerNumber and switching to a different field (City) this was successful!
The output file now had the correct data exported, so this proved there was an issue with the CustomerNumber field somehow.
Why SmartConnect Runs but Exports No Data
Opening the file with Notepad++ reveals the underlying issue.
For the few rows of data exported, we’d expect each output line to be just “one line”, but from the results, Notepad++ shows there is an LF (Line Feed) character after the CustomerNumber, which was surely unplanned and unexpected in the source data.
What is happening is that SmartConnect is reading the data and creating the “master document” record, but that record does not contain the LF character. Then, when SmartConnect joins the master document records against the source data, it doesn’t match, so no data is sent for that “document”.
Going back to the original Excel file, now that we’ve identified the issue, we can see that the Customer Number column does look odd.
Fortunately, in this data, the customer is the same, so it was a simple matter of overtyping the value and then copying to each row.
Switching the map key fields back to AddressCode & CustomerNumber and re-running the integration produced the correct results.
This download contains the Integration being tested. Sheet1 contains the “broken” data, Sheet2 has the fixed column data.
If you have any other questions about why SmartConnect runs but exports no data, please reach out to support@eonesolutions.com for assistance.
