Update Dynamics GP Notes from an eConnect Integration

By Steve Endow

I would have thought that one of the many eConnect integrations I have developed over the years would have required me to update an existing Dynamics GP Note record.  Perhaps I have done it before, but when this seemingly simple requirement came up recently, I couldn't remember having done it before, and Google didn't seem to turn up any results.

If there is an easier way to do this, please post a comment below, as I'm interested in knowing if I missed something obvious.

Let's say that  your .NET eConnect integration needs to update the address and phone number of an existing customer.  No problem.  But when you update the customer contact information, you also need to append an update message to the customer's Note field.  Okay, makes sense.

But if you try and set the eConnect taUpdateCreateCustomerRcd NOTETEXT field, that new value will wipe out any existing customer Note.  After poking around the documentation and searching for options to update an existing GP note programmatically, I didn't see any options.  I searched through the standard zDP stored procedures, as well as the eConnect "ta" stored procedures, but didn't see any options there either to update Notes.  While the SY03900 note table is pretty simple, it would be nice to have a simpler option for updating notes.

You could wrap all of this up into a single SQL stored procedure.  I didn't do that because I didn't want to deploy yet another custom stored procedure for this particular client.

You could also package this up as an eConnect Post procedure, but I find those to be a bit of a hassle because it's just like having a custom stored proc, which I didn't want, and the eConnect Post stored procedures are wiped out by most GP service packs and upgrades, so it is very easy to forget to recreate them after an update.

Anywho...

First, you need to make sure that the customer has a record in the SY03900 table.  Even though new customer records are automatically assigned a NOTEINDX value, by default, a record is not automatically created in SY03900.  So if you update customer ACME001 and attempt to update its note record in SY03900, your update will fail if the record doesn't yet exist.

I quickly pulled together this SQL to check for a Note record and create one if one didn't already exist.

IF NOT EXISTS (SELECT TOP 1 NOTEINDX FROM SY03900 WHERE NOTEINDX = (SELECT NOTEINDX FROM RM00101 WHERE CUSTNMBR = 'ACME001'))

BEGIN

INSERT INTO SY03900 (NOTEINDX, DATE1, TIME1, TXTFIELD)

VALUES  ((SELECT NOTEINDX FROM RM00101 WHERE CUSTNMBR = 'ACME001'), CONVERT(VARCHAR(10), GETDATE(), 101), CONVERT(VARCHAR(12), GETDATE(), 108), '')

END

I then borrowed and repurposed some nice SQL from the very smart Tim Wappat, who has posted a nice script for fixing line breaks in GP note records.

UPDATE SY03900 SET TXTFIELD = CAST(TXTFIELD AS varchar(MAX)) + CHAR(13) + 'Text to append'

WHERE NOTEINDX = (SELECT NOTEINDX FROM RM00101 WHERE CUSTNMBR = 'ACME001')

This update script adds a new line to the note and appends the new text to the bottom of the note.

With this script setup, after you use eConnect to update the existing customer contact info in GP, you have to have a separate method to perform the Note update.

Obviously this sample is only for updating customer notes, but you could repurpose for vendor and other notes, and if you got really fancy, you could probably abstract it to update notes for any record type.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com