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
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.