Search all records in the database

[franklin1232]

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.

 

[cecsno]

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.

 

[franklin1232]

Thanks

Thanks much cecsno. I will be running this against test data many times before using any live info.

 

[franklin1232]

I have been looking at your code and I am not sure I understand. Does you code pull all the tables and fields that I want to search from the database. Are you using the _field just as a place holder. My biggest problem is I don't know all the field names or table that I need to update and it would take me a while to compile that list. Could you maybe shed some light on the code you gave me.

Thanks again.

 

[cecsno]

I have been looking at your code and I am not sure I understand. Does you code pull all the tables and fields that I want to search from the database. Are you using the _field just as a place holder. My biggest problem is I don't know all the field names or table that I need to update and it would take me a while to compile that list. Could you maybe shed some light on the code you gave me.

Thanks again.

I gave you 2 examples, both will create a program called mass-chg.p. In the first, you have to provide a list of aliases of item (pt-alias 3rd line). I have seen systems where part, part-number, item and item-number have been used interchangeably.

The second example looks through _field for a _field-name that matches *_part. MFG/PRO prepends it's field-names with the first 2 or 3 characters of it's table name and + "_", so the item field in the Part Master table (pt_mstr) is pt_part, in the sales order detail (sod_det) it's sod_part.

I haven't worked with Vantage, but if your lucky and they use "item" in all the relevant tables, you can use the 2nd program and change the line

for each _field no-lock where _field-name matches "*_part".
to
for each _field no-lock where _field-name = "item".

 

[franklin1232]

I see what you are doing and it makes sense now, but Vantage is not as consistant as MFG PRO. The fields do not all have the same naming convention. Plus I don't see how you are finding all the tables with the part number field.

For instance if I create a job for part number 12345. Records are created in the Job_Head table Job_Detail table and a bunch of acounting tables that I don't know the names of off the top of my head. Then I non-conform parts from job 12345 and more records are created in the Non_Conformance table and DMR_Head table. The tables I listed are only a small number of the tables that contain a part number field and that field does not always have the same name even though it probally should. On top of that part numbers have been entered into user-defined fields and are used in some reports and must also be changed. With all that in mind my idea was to create a much more generic search. Opening up table by table and searching all of it's contents for any reference to the part number. Then changing it using a provided list of aliases. Is that possible?

In other database such as SQL or Oracle there a functions that provide a table collection object or array that can be looped through. Do progress have this? If not I was thinking about exporting a report and feeding it back in as a text stream.

 

[cecsno]

I see what you are doing and it makes sense now, but Vantage is not as consistant as MFG PRO. The fields do not all have the same naming convention. Plus I don't see how you are finding all the tables with the part number field.

For instance if I create a job for part number 12345. Records are created in the Job_Head table Job_Detail table and a bunch of acounting tables that I don't know the names of off the top of my head. Then I non-conform parts from job 12345 and more records are created in the Non_Conformance table and DMR_Head table. The tables I listed are only a small number of the tables that contain a part number field and that field does not always have the same name even though it probally should. On top of that part numbers have been entered into user-defined fields and are used in some reports and must also be changed. With all that in mind my idea was to create a much more generic search. Opening up table by table and searching all of it's contents for any reference to the part number. Then changing it using a provided list of aliases. Is that possible?

In other database such as SQL or Oracle there a functions that provide a table collection object or array that can be looped through. Do progress have this? If not I was thinking about exporting a report and feeding it back in as a text stream.

Hi,

Progress relates table by field-name, if Vantage does a good job with table relationships, you can go to the data dictionary reports and do a table relationship report on the part master table (whatever it's name is). But if it does not do a good job you have to supply all the possible names Vantage uses for part number.

So you set the value of pt-alias in the program below and run it. It will not update your database but will write a program to do the update.

/******* start of program *****************/

DEFINE VARIABLE pt-alias AS CHARACTER NO-UNDO.
DEFINE VARIABLE v-int AS INTEGER NO-UNDO.

pt-alias = "part,item,pnbr". /* <<<<< your alias list */

define stream f-stream.
output stream f-stream to c:\progress\wrk\mass-chg.p.
/******* mass-chg.p is produced by this program to do
the actual part number change ***********/

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.

/*********** end of program ***********/


If you need so help identifying part number fields, Epicor tech support should be able to provide you with a list. If they can't, send be a copy of you data definitions file (databasename.df).