franklin1232 said:
My accouting manager wants to modify a bunch of part numbers. Some he wants to change to a dummy part number others he wants to consolidate mutilple part numbers into one. Our ERP does not allow us to archive or change a part number after it has history. So what I want to do is a find and replace on the entire database. I am worried that I would not be able to query all the tables that have part numbers in them especially since some of the areas with the part number are not relationed to other tables. How could I do this? Does progress have a tables collection that I could loop through? I don't really know where to start. Personally I think this is a bad idea all together, but I am just the IT guy.
Hi IT guy,
Yea it's probably a bad idea, but what can ya say. First thing, a good backup. Then you need to know all the different names for part number. I put in a couple example programs that might help.
DEFINE VARIABLE pt-alias AS CHARACTER NO-UNDO.
DEFINE VARIABLE v-int AS INTEGER NO-UNDO.
pt-alias = "part,item,pnbr".
define stream f-stream.
output stream f-stream to c:\progress\wrk\mass-chg.p.
put stream f-stream unformatted "def var v-old-part as char no-undo.".
put stream f-stream skip.
put stream f-stream unformatted "def var v-new-part as char no-undo.".
put stream f-stream skip(1).
put stream f-stream unformatted "input from part-list.txt.".
put stream f-stream skip(1).
put stream f-stream unformatted "repeat:".
put stream f-stream skip.
put stream f-stream space(4).
put stream f-stream unformatted "set v-old-part v-new-part.".
repeat v-int = 1 to num-entries(pt-alias):
find _field no-lock where _field-name = entry(v-int,pt-alias) no-error.
if not available _field
then
next.
find _file of _field no-lock.
put stream f-stream skip(1).
put stream f-stream space(4).
put stream f-stream unformatted "find " + _file-name + " where " + _field-name + " = v-old-part exclusive-lock no-error.".
put stream f-stream skip.
put stream f-stream space(4).
put stream f-stream unformatted "if available " + _file-name.
put stream f-stream skip space(4).
put stream f-stream unformatted "then".
put stream f-stream skip space(8).
put stream f-stream unformatted "assign " + _field-name + " = v-new-part.".
end.
put stream f-stream skip(1).
put stream f-stream unformatted "end.".
output close.
/********************************************/
output to c:\progress\wrk\mass-chg.p.
put unformatted "def var v-old-part as char no-undo.".
put skip.
put unformatted "def var v-new-part as char no-undo.".
put skip(1).
put unformatted "input from part-list.txt.".
put skip(1).
put unformatted "repeat:".
put skip.
put space(4).
put unformatted "set v-old-part v-new-part.".
for each _field no-lock where _field-name matches "*_part".
find _file of _field no-lock.
put skip(1).
put space(4).
put unformatted "find " + _file-name + " where " + _field-name + " = v-old-part exclusive-lock no-error.".
put skip.
put space(4).
put unformatted "if available " + _file-name.
put skip space(4).
put unformatted "then".
put skip space(8).
put unformatted "assign " + _field-name + " = v-new-part.".
end.
put skip(1).
put unformatted "end.".
output close.