Skip to content
+1-888-319-3663

EONE BLOG

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

10 Comments

  1. scfan on April 16, 2015 at 9:09 am

    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.

  2. Asmith on June 24, 2015 at 10:35 pm

    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.

  3. Chris on June 29, 2015 at 4:28 pm

    Thanks for the suggestion. This is something we will look at for a future release.

  4. fgao on November 9, 2018 at 4:10 pm

    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!

  5. Patrick Roth on November 12, 2018 at 8:29 am

    “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

    • fgao on November 12, 2018 at 12:14 pm

      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?

    • fgao on November 12, 2018 at 4:14 pm

      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.

  6. Beat Bucher on March 7, 2019 at 8:39 pm

    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 .

  7. Boris on April 29, 2020 at 11:24 am

    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.

    • Boris on April 29, 2020 at 11:36 am

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

Leave a Comment





RECENT POSTS


Tech Tuesday: Building My First Matrix Report
Overcoming the Top Challenges of Zendesk Integration
Accessing Historical Dynamics GP Data in NetSuite: Using Popdock
Popdock's Top 10 New Features
New Webinar: Getting Started with Matrix Reporting in Popdock

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.