Tech Tuesday: Integrating Record Notes in Dynamics GP with SmartConnect

Notes! Who doesn’t love record level notes in Dynamics GP? What’s not to love about having a freeform text field to enter 32k of information into?

Figure 1:Record level note in GP

Long time GP users know that the (almost) universal field for a note is the NOTEINDX field in the table for that GP record. For a RM Sales Transaction, the RM10301 table. The actual notes themselves in the “core” GP dictionary are stored in the SY03900 table using that same NOTEINDX field to link the two records together.

What isn’t so well known is that the “Note Text” for most destination types isn’t available in eConnect. Fortunately, some of the populate destinations such as Customer/Vendors and POP/SOP in eConnect DO have a “Note Text” available to create the record notes for those items. However outside of that, there are probably only a few other destinations that support this functionality.

So, what is a SmartConnect user to do if their GP destination doesn’t support this but we really want to integrate that note?

The common practice in SmartConnect to do “other stuff not supported by the destination” is to use an “After Document Success” task. Sometimes this is to update the source data to provide information about the success/fail of a record from a source SQL table. Or in our case, we are going to directly adjust data in the target SY03900 table.

To start off, we’ll create some simple data for two RM Transaction Invoices in Excel.

Figure 2:Sample data with a lurking problem highlighted.

We added a “Note” column and some data for each note. I also highlight another challenge we’re going to run into with the note on the transaction for ADAMPARK0001 as there is a single quote in the message. For those with a bit of SQL experience, they know that single quotes must be “escaped” in order to be inserted into the SQL table. In order to handle that, we’ll need a trick in our coding.

I won’t post all the details for the RM Trx since there isn’t anything special about creating the test RM Invoices, but I will note the GP Rolling Column that we are using for the “next document number” for our invoices.

Figure 3:GP Rolling Column value for use in script

Dynamics GP creates a NOTEINDX value for every record created whether there is an actual note created at that time. Because we’re getting that “next number” on the fly, we need to mark this checkbox so that we can get the value that SmartConnect is using for that document.

After doing the mapping, we create the two global variables GBL_DOCNUMBER & GBL_NOTE that the script is going to use. Refer to the SmartConnect Documentation on global variables if necessary.

Next, create a new “After Document Success” script task. I called mine SET_GLOBALS.

Figure 4: Script Task (before SQL Task)

Here is where I put in the “trick” that I said we’d need because of the single quote in “Jack O’ Lantern”.

I might have been tempted to just use the Replace() in my SQL script and not use a second script task– but that actually would fail. The reason is that SmartConnect replaces the field tokens directly so that means Replace(‘_NOTE’) in the SQL Script would be replaced with Replace(‘Jack O’ Lantern’) which would also be unbalanced and fail. So we do the Replace() in the .NET script instead.

Also notice that I used the VB “Chr” function instead of just using the single quote. The reason is that it is hard to see “’” in a screenshot or replace with “’’” and figuring out what characters are what. But you can do it either way.

Task SET_GLOBALS

vb.NET example

C# example

And then the SQL Task that will come after the script task above.

Figure 5: SQL Task to update/insert the actual note

Because it isn’t possible that we can update an existing RM Trx Invoice with eConnect, I don’t have to check for an existing record note using exists() in the query above. However, since we can re-use this node code above with any eConnect destination (by just adjusting the select @noteindex query and where clause to the correct table) I thought I’d add it here.

Task UPDATE_SY03900

Crossing my fingers that we don’t have a typo or logic mistake, I run the integration which says it was successful. But were the notes – especially the Jack ‘O Lantern one – ok?

Checking the second document created, we look at the note and it looks like success!

Figure 6:Score!

What we learned in this article:

• How to use ‘after document success’ tasks in order to update “other” data that doesn’t have a built-in destination using a SQL Task.
• How to use a .NET Script Task and a SmartConnect Global Variable in order to fix issues with single quotes in source/destination data in a SQL Task.
• Marking the “Use for Global variable” checkbox on the GP Rolling Column in order to use the GlobalRollingColumn variable in script.

You can download the map and source file HERE.

Best regards,
Patrick Roth
eOne