SmartConnect Restriction Script
Today, I created a very simple employee master map in SmartConnect that imports just the two employee records from the comma-delimited source file.
After the import, I decided to remove the phone number from one of the two employee records in the source file and add a restriction to the map to not pull in any record if that record does not contain a phone number.
Simply put, I just wanted to test the map's Restriction "capabilities".
So, I used the following script in the Node Creation Restriction window of the mapping:
If String.IsNullOrEmpty(_PHONE1) then
Granted, the PHONE1 field in my source file was a "General" formatted cell, according to Excel, but I would think that "String" would have been appropriate for the restriction. Perhaps, I'm wrong.
Anyway, I then deleted in GP the record that according to my source file does not have a phone number. Also, I modified the map to say "Update Existing" for the Update/Create Employee Node".
But, when I re-ran the map, both records were imported. I was only expecting one to be imported, based on the restriction that I created.
An example of this is on page 28 of the SmartConnect 2012 Training Manual. So, why did the restriction not work, in this case?
When reading data from a csv file the odbc driver checks the first few rows of the file to try to determine the data type of the column. In the case you are describing, and in my testing the phone numbers are probably being read as integers rather than strings. If you use the create schema file option on the datasource, and set the column to string then your restriction would work.
Alternatively when populating the data values for scripting if the value is DBNull then the value is set to the minimum value of the data type. In this instance it is being set to Int32.MinValue. The script below should restrict out the records with no phone number.
if(_PHONE1 = Int32.MinValue) then
if(_PHONE == Int32.MinValue)
A question on Kevin's response: Where is the create schema option located. I don't see it, but I am running an older version which could be the reason.
Nevermind…I got it…located next to the source file.