Update Dynamics GP Notes Using SQL

By Steve Endow

In the last few weeks I've had two integration projects that required me to update notes in Dynamics GP.

One customer needed to import note updates from another system into existing Service Call notes in Dynamics GP.  The external system was able to provide the data in a SQL staging table, and I had to import the note data into Service Calls via SQL, appending the information to existing notes, or creating new notes if they didn't already exist.

The second was an eConnect 2010 RM cash receipt import where I needed to attach a note to some of the cash receipts.  Imagine my surprise when I realized that the RM Cash Receipt eConnect transaction does not have a NOTETEXT field!  Why not???  eConnect inserts a cash receipt record with a NOTEINDX value of zero!  Who's the summer intern who cut corners?

I looked for a stored procedure that might handle the note inserts and updates, but a trace of the GP windows seemed to indicate that GP just used straight INSERT and UPDATE statements.  But the GP note windows always send the full note text in the SQL for their updates, which is straightforward.  I needed the ability to insert a new note as well as append text to an existing note--ideally without retrieving the existing text first.

So I searched for a note insert or update stored procedure in the GP databases but found no such thing.  After a few attempts to find a solution online, I gave up and decided to roll my own.

It has been years since I have had to deal directly with SQL Server text fields.  Although it isn't "hard", the syntax to update a text field is far from obvious.  But with minimal Google-fu skills, you will find plenty of examples online.

To make my life easier, I packaged up my script into a stored procedure that accepts the note index and the note text to append. Also for simplicity, I use a varchar(8000) parameter for the text update.  And finally, I include newline characters to add white space before note updates.

IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name = 'csspInsertUpdateNote')
    DROP PROCEDURE csspInsertUpdateNote
GO

CREATE PROCEDURE csspInsertUpdateNote
    @NOTEINDX AS int,
    @NOTETEXT varchar(8000)
AS
BEGIN
  
    SET NOCOUNT ON;
    DECLARE @TXTFIELD varbinary(16);
    DECLARE @LENGTH int;
    DECLARE @CRLF varchar(10);
    DECLARE @APPEND varchar(8000);
    DECLARE @GPVERSION int;
   
    --GP 2010 uses CRLF for notes, whereas GP 9 and 10 use CR only
    SELECT TOP 1 @GPVERSION = versionMajor FROM DYNAMICS..DU000020 WHERE COMPANYID = -32767 AND PRODID = 0
    IF @GPVERSION >= 11
        SET @CRLF = CHAR(13)+CHAR(10);
    ELSE
        SET @CRLF = CHAR(13);
       
    SET @APPEND = @CRLF + @NOTETEXT;

    --Check if a note record exists
    IF (SELECT COUNT(*) FROM SY03900 WHERE NOTEINDX = @NOTEINDX) = 0
        BEGIN
            --If not, insert a new note record with the update
            INSERT INTO SY03900 (NOTEINDX, DATE1, TIME1, TXTFIELD) VALUES (@NOTEINDX, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())), CONVERT(VARCHAR(8),GETDATE(),108), @NOTETEXT)
        END
    ELSE
        BEGIN
            --If so, update the existing note
            --Get the text pointer for the note
            SELECT @TXTFIELD = TEXTPTR(TXTFIELD), @LENGTH = DATALENGTH(TXTFIELD) FROM SY03900 WHERE NOTEINDX = @NOTEINDX;

            UPDATETEXT SY03900.TXTFIELD @TXTFIELD @LENGTH 0 @APPEND;
        END

END
GO

GRANT EXEC ON csspInsertUpdateNote TO DYNGRP
GO

And if you have to generate note indexes like I did for the RM Cash Receipt transactions, you can use the DYNAMICS.dbo.smGetNextNoteIndex stored procedure.


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

http://www.precipioservices.com