Skip to content


How to Exclude Data Source Rows

One question that often comes up in SmartConnect is, “How do I exclude certain rows from my data source?”

There are 3 main ways to exclude data from your data source. These include,

  • Excluding blank keys/lines on the Map Options Tab
  • Creating a restriction on the data in the destination
  • Using an OBDC data source query

This article will explain each of these options in more detail.


Excluding Blank Keys/Lines on the Map Options Tab

This option can be used for 2 different scenarios,

  • If the data sources had empty rows
  • If the data source is missing a value in one or more of the key fields set in the map

Empty rows

We can get rid of these empty rows by navigating to the options tab and selecting exclude blank lines.

This will exclude any data source rows that are empty.


Missing Key Field

If we have data in our source that is missing key field values, then we can do the same option as above. Instead of excluding blank lines, we can select exclude blank keys.

This option will exclude any data from our source that is missing a key field value.

Below we have an example.

Suppose we were trying to create new vendors in GP.

We can see that row 3 has a missing Vendor ID. By default SmartConnect cannot process blank key values and throws an error if there are blank keys.

This is where we can select exclude blank keys in our options tab, since Vendor ID is our key field in the map, SmartConnect will not give us an error for that row and will skip the processing step for it.

When we preview the data, we will still see the row that we don’t want mapped.

SmartConnect doesn’t exclude that data until we run the map.

When running the map, we can see that only 4 records were added to Vendors in GP, because one of the Vendors had an empty key field.


Creating a Restriction on the Data in the Destination

Another option to exclude data from our source is to restrict the data being map. We can do this by going to the Restrictions tab under the SmartConnect Mapping window.


This is where we will be able to write a script to restrict our data.

Let’s suppose we are trying to create vendors in GP again. We are using the same data source as before.

We don’t want any Vendor to be mapped that doesn’t have a Vendor ID. We can create the calculation below to restrict any data that has

an empty Vendor ID.


When running the map, we see there are 5 successful records.

One question we will receive is, “Why does the data source still map the row we wanted to exclude?” This happens because

SmartConnect had to compile every data source row, then it decides whether to send them based on the restriction criteria.

This means that even though that map says there were 5 successful records. SmartConnect only mapped 4 records after

going through the restriction. Using the ODBC Data Source Query will make it easier to see what data we are mapping.



ODBC Data Source Query

The last option to exclude our data is to use an OBDC connection. We can select the same file through the ODBC Connection that we were using with an Excel data source.

We can write a select query to map only the rows that we want.

Assume we have our same data source again. Typing the query below will exclude any data that has an empty Vendor ID.

When we preview the data, we can see that our row was removed and now there are only 4 rows to be mapped.



As you can see, there are many different options to exclude data from our data source. If there are missing key field values or empty rows then use the exclude blank keys/lines option.

Otherwise, we will have to use one of the other options. People tend to use the ODBC Data Source Query more, because you can preview the data that is going to be mapped.
They’re all great options to exclude any unwanted data from our source.



















Creating an Integration in
eConnect error - Login failed for user
32 bit Excel Add-in for SmartConnect 21 does not appear after installing.
Connection could not be validated when using Excel data source
Salesforce Error: Unable to create/update fields check security settings


SSL Security error using OLEDB Connection
This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms
Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))
The server process could not be started because the configured identity is incorrect.
Using WINSCP and a task to upload or download files from an SFTP site



Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SmartConnect SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.