Home » Listing Details
Top Websites
  1. Dynamics GP Help
    Over 6100 resources listed.
  2. Mark Polino's DynamicAccounting.net
    Over 5100 resources listed.
  3. Rose Business Solutions Blog New
    Over 2200 resources listed.
  4. Developing for Dynamics GP - By David Musgrave and the MS GP Dev Support Team
    Over 1100 resources listed.
  5. Mariano Gomez at The Dynamics GP Blogster
    Over 1000 resources listed.
  6. Microsoft Dynamics Partner Community Blog
    Over 900 resources listed.
  7. Christina Phillips, Steve Endow & Lorren Zemke at Dynamics GP Land
    Over 700 resources listed.
  8. Mohammad Daoud's Dynamics GP Blog
    Over 600 resources listed.
  9. Vaidy Mohan at Dynamics GP - Learn & Discuss
    Over 500 resources listed.
  10. Inside Microsoft Dynamics GP Official Blog
    Over 500 resources listed.
  11. eOne Business Solutions Blog
    Over 400 resources listed.
  12. About Dynamics, Development and Life
    Over 300 resources listed.
  13. Frank Hamelly at GP2theMax
    Over 300 resources listed.
  14. Dynamics CPM
    Over 300 resources listed.
  15. BKD Dynamics GP Insights Blog
    Over 200 resources listed.
  16. Leslie Vail at Dynamics Confessor Blogspot
    Over 200 resources listed.
  17. Victoria Yudin's Dynamics GP Website
    Over 200 resources listed.
    Victoria Yudin
  18. Janakiram M.P. at DynamicsBlogger
    Over 100 resources listed.
  19. VS Tools Forum
    Over 100 resources listed.
    Your Resource for Visual Studio Tools for Dynamics GP
  20. Inside Microsoft Dynamics GP Official Blog
    Over 100 resources listed.
  21. US Dynamics GP Field Team Blog
    Over 100 resources listed.
  22. Catherine Eibner MBS Developer Evangelist
    Over 100 resources listed.
  23. Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
    Over 100 resources listed.
  24. Dynamics Small Business
    Over 100 resources listed.
  25. Belinda, The GP CSI
    Over 100 resources listed.

Title:Wrong PO Number!
Description: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 misshap 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.

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 plain vanilla GP v10, 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 results window to effect all the changes in the tables where the PONUMBER field is used.

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

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC.
Link Owner:
Date Added:June 17, 2010 06:14:06 PM
Number Hits:21
RatingsAverage rating: (0 votes)

No Reviews Yet.


Thank you for your support for GPWindow. It helps us cover part of the hosting costs for GPWindow.