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.
I quickly pulled together this SQL to check for a Note record and create one if one didn't already exist.
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.
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.