Wrong PO Number!

Welcome to another edition of my blog! This time around, I want to talk -- not literally -- about a common occurrence experimented in many Dynamics GP environments. When the pressure amounts, some company buyers may find themselves accidentally overriding the PO number field, a common mishap that may cause wasted time or the need to void and re-enter the document. Now, think for an instance, if you are using the Manufacturing module or any third-party product how cumbersome the task can become.

Fortunately, there is help on the way! I have developed a script based on a previous post, that will scan for the PONUMBER field in all tables in the company database. The script will automatically produce another script in the Results pane that can be copied and pasted into a new Query window and be executed against the company database.

The following example shows the script with the new PO number (@newponumber) and the old PO number (@oldponumber) variables being used to facilitate the interfacing with the person executing the change.

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
DECLARE @NEWPONUMBER CHAR(25), @OLDPONUMBER CHAR(25);
SET @NEWPONUMBER = 'PO1023';
SET @OLDPONUMBER = 'PO1001';

SELECT DISTINCT 'UPDATE ' + RTRIM(OBJS.NAME) + ' SET PONUMBER = ''' + RTRIM(@NEWPONUMBER) + ''' WHERE PONUMBER = ''' + RTRIM(@OLDPONUMBER) + ''''
FROM SYSCOLUMNS COLS
  INNER JOIN SYSOBJECTS OBJS ON (COLS.ID = OBJS.ID)
  INNER JOIN SYSINDEXES INDX ON (COLS.ID = INDX.ID)
WHERE (COLS.NAME = 'PONUMBER') AND (OBJS.XTYPE = 'U') AND (INDX.ROWCNT > 0);

When this script is executed against the company database, it produces the following results:

UPDATE POP10100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP10110 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP10310 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP10500 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP30100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP30110 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP30310 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE POP40100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
UPDATE SOP60100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001'
(9 row(s) affected)

The above results can be copied and pasted into a new query window to effect all the changes in the tables where the PONUMBER field is used.

NOTE: If using third party products that use a different table column name, you will need to replace accordingly in the script. Enjoy!

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/