Today's #TipTuesday title is a little obscure but it's 2 tips in 1! I've been building some integrations lately, as I alluded to in my last post. Today's tip, and next week's tip, are all coming out of some things I ran into today that gave me some great topics to share! Woohoo!

This tip is about eOne Solutions' SmartConnect again, and while I am integrating into Dynamics GP ultimately, this tip is purely about a quirk with SmartConnect that is easy to miss.

The Background

In my example, I'm using tasks within my SmartConnect maps to handle various tasks - data validation, sending alerts when there are issues, and updating records as I work on them for various reasons (to mark them as done or mark if they had an error, etc.).

One set of tasks I use regularly is the Document tasks - on the success or failure of a specific document (transaction) in my integrations. In my case, I'm using both and updating the source table to flag the records as either complete or with an error message and utilizing variables to only update the specific record(s) that succeeded or failed.

The Quirk(s)

I'm calling these quirks because it simply looks weird to me. The reality is for the first item, it's just how SmartConnect works. For the second item, ya, it's just a quirk in the UI (in my humble opinion). :)

Tip #1: when referencing a variable in a task, users need to enclose it in single quotes (as if you were referencing a string value in a SQL query). In the example below, _Jrnlcode, _JrnlNo and _VendorID are all variables in my data source. All of them are enclosed in single quotes. It looks weird (to me) but an error "Invalid column name" & whatever the value of that variable is will arise otherwise. Example: "Invalid column name 'RJ'".

Sample SQL Command shows variables in single quotes in red text.

Tip #2: when using the Insert Variable button (as opposed to just typing in the name of the variable/column), SmartConnect inserts a blank space before and after that variable that is barely visible. For example, I would end up with ' _myvariable ', and that would be enough that a "where" clause in a SQL query would not find a matching record to update.

I was convinced the Document Fail and Document Success tasks were not firing because my data wasn't getting updated with the proper Failed or Completed flags. When I went to copy my update script into SQL Server Management Studio to ensure I wasn't missing something simple on the syntax, I saw the issue: a space I missed updating before the "vendor ID" variable name. Ugh.

Same example with a zoom-in on the variable in single quotes showing a space before the variable name.

That's it for today's tip. Variables are super useful, especially in situations like this, but the quirk with the spaces (which was only noticeable because it needed to be enclosed in quotes) is easily missed!