This week’s Tech Tuesday is from our Director of Product Management, Chris Dew:
Recently we have had several questions surrounding the restrictions feature inside of SmartConnect and there is a bit of confusion on how they work and when to use them. Today we will dive into the following:
1) How to use Restrictions?
2) When to use Restrictions?
3) Alternatives to using Restrictions?
What we see many times is that integration data inside of an Excel sheet will have blank rows giving you a data set like below with 3 legitimate records and 1 blank record.
From the image below we can see that the Key Fields are TransactionDate and BATCHID.
If we look at the Header mapping in this inventory transaction we can see that because of the Group Data button (that matches our Key Fields) we now get two records when we hit the preview button like the image below:
We really don’t want the blank record so many people will look to adding a Restriction to eliminate the record like the image below:
In this script (in text form below), we are essentially looking to see if we have a blank record and if we do rejecting the record.
VB.Net Code Snippet
—————————————————-
if _BATCHID.ToString().length > 0 then
return true
else
return false
end if
—————————————————-
If we run our map now, we will get the following result in the image below:
Ok, why do we still have two records when we tried to restrict the blank one? I will answer that question by helping you understand how SmartConnect uses your data source.
When we choose our Key Fields in the initial data source (TransactionDate and BATCHID), this determines how many documents SmartConnect should create by grouping based on these fields. In our case the three lines that have the same TransactionDate and BATCHID are one document and the blank row becomes a second document.
The next question we get asked is, why didn’t the Restriction work? The answer, it actually did! Maybe not in the way you thought but let’s dive in a bit more to find out how it worked. The Restriction we wrote earlier was just applied to the Create Inventory Transaction node.
So in this case it did eliminate the blank record from being mapped to this node but the overall Key Fields didn’t get this restriction, only the node with the Restriction on it. If we want to actually get rid of the blank document we must do so in our data source. In this case, we need to switch from the simple Microsoft Excel 2013 data source to Microsoft’s ODBC driver for Excel 2013 so we can right the query to eliminate the blank rows.
The image below demonstrates selecting all rows from the InventoryTransactions worksheet and then restricting where the BATCHID is blank.
This will now give you only one document with 3 associated lines. I hope this helps clear up a few things on how restrictions work in SmartConnect and my advice to you would be to always restrict as much as you can in your data source!
Thanks,
Chris Dew
Director of Product Management