Updating Dynamics GP Comment ID in Sales Order Entry

A library of pre-written comments can be defined in Dynamics GP and then stored for later quick recall. A name known as the Comment ID is used for quick recall of the stored comments. The lookup table that stores the library of pre-written comments is SY04200. This table stores comments for many parts of GP, so SOP comments will have a the CMTSRIES field equal to 3. When an sop document is entered and a comment placed on it, by selection or typing the comment id. Then that order will be stamped with the pre-written comment corresponding to that comment id in the library. Think of this process as being like using the old rubber ink stampers for stamping previously prepared comments onto documents using an ink pad. A COPY of the comment is put against the order in the SOP10106 table under the field CMMTEXT (and the other comment fields too, more on them later).

If the source comment is changed at a later point in time, then any future documents will be stamped with the new texts, but the documents that have already been stamped will not have the comment changed as it is a COPY of the comment as it was when it was attached to the order. See later for ideas on “fixing” this.

It is also possible and normal to go in and edit the comment text, adding or changing the text that was added from the comment id. The comment ID name will remain in this case so that you may still see the comment has been applied at some point, and a pencil edit icon will be added next to the comment ID to show it has been manually updated.

If the comment ID name is removed from the field then the whole comment is erased, including any manual edits.

For the developers out there, note that the comment is added to the SOP10106 table immediately on entering the comment ID, before the order is saved. Also note that the (text type) CMMTEXT field is also broken up into chunks of 51 characters over a number of comment fields. This is to support report outputs that cannot handle the massive text type field.

I want to update the exiting comments from comment IDs

So something has changed about your standard comment so the comment in the comment ID is updated, that deals with future documents, but what about the exiting sales documents that have been stamped with the previous comment?

There are two obvious solutions:

• Use a mail merge Dynamics GP macro to, for each order document of concern, update the comments, but opening each order, removing the comment ID and replacing it again, then finally saving the order.
• Use SQL to update the database directly.

The first method of macro will work if you have no in house SQL skills. Be aware though that any edits to the comments will be also lost, check with the users that they are not in the habit of editing the text that has been stamped into the document.

The second method is ok if you are confident with SQL, or ask your dynamics GP partner to help if you are at all concerned.

UPDATE sc  
SET CMMTTEXT=REPLACE(CAST( CMMTTEXT AS VARCHAR(MAX)),'Old Comment Text', 'New comment text')  
FROM   
SOP10100 so  
JOIN   
SOP10106 sc  
ON so.SOPTYPE=sc.SOPTYPE AND so.SOPNUMBE=sc.SOPNUMBE   
WHERE   
CHARINDEX(CAST( CMMTTEXT AS VARCHAR(MAX)),'Old Comment Text') >0  
AND so.COMMNTID='CommentIDName'

The above SQL will update only for orders stamped with the comment ID in question. It will then replace the old text with the new, leaving any extra text, so it is low risk in terms of losing data. However if someone has edited the text from the template, then this will not work. You may need to be careful if you have comments that span multiple lines, as the line returns will need to be taken care of, you could always source the text from an existing record for ease, using a sub query.

DECLARE @oldCommentText VARCHAR(MAX)  
SELECT @oldCommentText = CAST( CMMTTEXT AS VARCHAR(MAX)) FROM SOP10106   
WHERE SOPNUMBE='{sourcedoc}' and soptype={sourcetype}  
  
UPDATE sc  
SET CMMTTEXT=REPLACE(CAST( CMMTTEXT AS VARCHAR(MAX)),@oldCommentText, 'New comment text')  
FROM   
SOP10100 so  
JOIN   
SOP10106 sc  
ON so.SOPTYPE=sc.SOPTYPE AND so.SOPNUMBE=sc.SOPNUMBE   
WHERE   
CHARINDEX(CAST( CMMTTEXT AS VARCHAR(MAX)),@oldCommentText) >0  
AND so.COMMNTID='CommentIDName'

Replace CommentIDName with the comment id to target. And replace ‘Old comment text’ and ‘New comment text’ as appropriate.  Please check this SQL before trying it and run it on a copy of your data in your test system first. These scripts are for ideas only and not claiming to be a tested solution.

Hopefully this post will help someone, let me know with a comment if it did.