HELP ARTICLE

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.

 

Conclusion

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.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

RECENT POSTS


Deleting Source Files from Archive Folder
The Sales Header does not exist. Identification fields and values: Document Type=’Quote’No.=’’
SmartConnect - Extra Source Records
SmartConnect – Zip File as Source
RM Apply Detail SmartList Builder Template

POPULAR POSTS


Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))
Manufacturing Serial Trace SmartList Builder Template
The server process could not be started because the configured identity is incorrect.
How to update SmartConnect from 10.0.0.51 to SmartConnect 2014
Using WINSCP and a task to upload or download files from an SFTP site

CATEGORIES

ARCHIVES

open all | close all

TAGS

CRM Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight ERP Events Excel Excel Report Builder Extender Extender Enterprise Flexicoder GP GPUG integration integration manager Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Office Relationships partners Popdock release Reporting SalesForce SalesForce.com scripting SmartConnect SmartConnect.com SmartConnect Office Hours SmartList SmartList Builder SmartList Designer SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.