Skip to content
+1-888-319-3663

EONE BLOG

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


GBL_DOCNUMBER = GlobalRollingColumn
GBL_NOTE = _NOTE.Replace(Microsoft.VisualBasic.Chr(39),
 Microsoft.VisualBasic.Chr(39) + Microsoft.VisualBasic.Chr(39)))
return true

C# example


char singleQuote = (char)39;
GBL_DOCNUMBER = GlobalRollingColumn;
GBL_NOTE = _NOTE.Replace(singleQuote.ToString(), singleQuote.ToString() + singleQuote.ToString());
return true;


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

declare @noteindex int = 0

select @noteindex = NOTEINDX from RM10301 where RMDTYPAL = 1 and
 DOCNUMBR = ‘GBL_DOCNUMBER’

if @noteindex > 0
begin
if exists(select 1 from SY03900 where NOTEINDX = @noteindex)
 begin
  update SY03900 set TXTFIELD = ‘GBL_NOTE’ where NOTEINDX = @noteindex
 end
 else
 begin
  insert SY03900 (NOTEINDX,DATE1,TIME1, TXTFIELD) values
   (@noteindex,CONVERT(VARCHAR(10), GETDATE(), 101),
   CONVERT(VARCHAR(12), GETDATE(), 108) ,’GBL_NOTE’)
 end
end


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

2 Comments

  1. Slava Gorbunov on April 26, 2021 at 2:42 am

    Hi! Do you know how to integrate attachments with Transactions? E.g. I have PDF files that are required to be upload to GP with the creation of a corresponding Payables transaction record. I have got the metadata for the transaction and PDF blobs in SQL Server database. Any idea would be greatly appreciated.

  2. Patrick Roth on November 3, 2021 at 4:04 pm

    Slava,

    Out of the box, there isn’t an eConnect node that does GP document attachments.
    However our services team has created such a node for SmartConnect/eConnect that does this.

    https://www.eonesolutions.com/blog/tech-tuesday-what-data-can-smartconnect-integrate-in-dynamics-gp/

    The above blog post gives contact information to the services team if you want to inquire what that involves.

Leave a Comment





RECENT POSTS


Happy Thanksgiving from eOne! - US Office Closed November 25 & 26
Popdock: What is your Locale?
Q4 2021 Partner All Hands Call - Recording Now Available
An Announcement regarding SmartList Builder and SmartView from eOne Solutions
Join us for the Q4 2021 Partner All Hands Call!

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 Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.