Tech Tuesday: Understanding Restrictions inside of SmartConnect
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
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
Thanks, this was a very useful column – I was just looking to execute a node only if a value existed in a particular field.
This showed me how I can do that.
What if your Data Source needs to be Folder Data Source? There needs to be a way to specify a filter for the data in each file in the folder, similar to the way GP’s Integration Manager has for allowing filters in its source files.
Thanks for the suggestion. This is something we will look at for a future release.
Thanks, this seems exactly what I need to exclude some records in mapping. However it doesn\’t seem to work in my map. I have a simple map that originally worked, then I try to add a restriction by clicking \”Restrictions\” button on the mapping, then just cancel on each window without doing anything, now when I run the mapping, it gives a bunch of errors which I can\’t seem to get away with.
What I noticed is that all the error complain about columns that have non-numeric values, saying \”Error in calculation column \’column-name\’: End of statement expected.
So does clicking \”Restrictions\” button trigger some default actions behind the scene? If so, is there a way to manage/configure that? If not, do you know why this happens?
I have SmartConnect 20.17.0.17
Source: xml query
Destination: Microsoft Dynamics GP
Thank you in advance!
“does clicking Restrictions button trigger some default actions….”
Yes, I guess it does. It means that the code that you added now gets executed for every line in your mapping.
And note that this only happens on execution, not the data preview.
For this resolution, you’d have to look at the code you wrote and then against the datatype of the field of the source data to know if it correct for that column.
Troubleshooting scripts isn’t the easiest thing since there isn’t a debugger to work with. In the end, you’d have to either:
1. Use MessageBox.Show() function put messages of where you are in code. I do that a lot to see my variables:
MessageBox.Show(_MYFIELD)
2. instead of messagebox (which doesn’t work on the service), write to a text file or the windows event log.
patrick
Patrick, thank you.
That\’s what I suspected too. The confusing part of this behavior (\”clicking Restrictions button triggers some default actions\”) is that I didn\’t write any code, nor did I save anything on the Restrictions window. By any chance, are you aware of anything else that could have triggered this behavior?
We figured out the root cause of the issue, as following, in case others run into similar situations.
It turned out the source xml had nodes with dashes (“-“) in the name. By replacing the dashes (“-“) with underscores (“_”), the errors went away.
How do I get rid of the Validation Error : “Error Count: 1, Error in restriction: Expression expected” ? I too clicked on the Restrictions button to filter data out of the source (which is CRM D365 and doesn’t offer much source filtering), but I then cancelled and / or tried some very simple IF THEN ELSE rule like provided here, but it doesn’t work.. Can’t even run the map without hiring the restriction error .
I’m having the same issue as Beat, I added some code that validated successfully, then tried to remove it and just leave “return true”, but get an error “Error in restriction: ‘Trim’ is not a member of ‘Double'” and I can’t get rid of this error no matter what I try.
Ok apparently the Validate button checks across all restrictions, not just the currently open restrictions window. Note that whatever function you’re applying to the field must be applicable based on the source field data type, in my case the field was a number field and trim does not apply to numeric fields in C# (I think that’s the language used in SC).