SmartConnect, Variables & Quotes

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 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 you reference a variable in a task, you need to enclose it in single quotes (as if you were referencing a string value in a SQL query). In this example below, _Jrnlcode, _JrnlNo and _VendorID are all variables in my data source. As you can see below. all of them are enclosed in single quotes. It looks weird but if you don’t do this, you’ll get an error “Invalid column name” & whatever the value of that variable is. Example: “Invalid column name ‘RJ’”.

Tip #2: if you use the Insert Variable button (as opposed to just typing in the name of the variable/column), SmartConnect inserts a blank space before and after. For example, I would typically type 2 single quotes (’’) and then move my cursor back to between them and insert the variable. 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.

That’s it for today’s tip. Variables are super useful, especially in situations like this, but the quirk with the spaces (which really was only noticeable because you need to enclose them in quotes) is easily missed!

comments powered by Disqus
Built with Hugo
Theme Stack designed by Jimmy