PDA

View Full Version : Renaming a key linking field over a dozen tables in a DB


ABC123

Ray in Capetown
07-04-2014, 10:02 AM
I have A FULLY functioning installed program that unfortunately has the the user brandname as main linking field name for all tables and sets (and menus).
There is another market for this system but I fear the name will surface somewhere if not changed.

I can probably globally change the fieldname where used in all tables and all stored code.
Is there any suggested way, like maybe to use a "Copy TO" -- to convert all forms, and/or form code and sets ?

and if possible change the original users tables too for ongoing support.

BTW - I have this system in use with v10 but will be using v12 for the future - there has appeared no difference to operations with preliminary testing on v12

MoGrace
07-04-2014, 12:27 PM
Cal's Aims App Analyzer should help you to find all the instances where this field is used. But I would start by adding a new field to your tables and then populate it with the same data so you can change your sets without recreating them. Once you have all the tables and sets using the new field, then you can search your scripts and do a global search and replace. Then do the reports and forms, saved operations, etc.

Ray in Capetown
07-04-2014, 01:59 PM
Good thinking Robin.
Keep the safety on while switching over to the new field before discarding the old.
Backup data at ea phase to enable rollback if forms get lost. Remove the field to test, detect errors and roll back and fix
Cals Aims had occurred, will need a bit of trial, but this will be one off, which if I program the sequence to use for conversion of the existing user's data... (just thinking aloud here)...
Foolishly, I didnt think ahead but that was the name they used in their previous system for the tracking number and I adopted that.

Ray in Capetown
07-04-2014, 05:17 PM
OK, changing sets and tables to the new field is quite straightforward .
If anyone ever needs to add a field to every table that contains another and populate that added field with the contents of the other, the code below does that.
Could use a couple of AS wizards I guess but this does all the checking in one pass, both steps in sequence with the one table
The next step of changing the sets link is simple too. Just edit the set, each linked table now has the new field in the linklist- just select it in place of existing.
After that.. the forms I'll do that manually re-assigning any object linked to the old field.

'this will add a new field "ordref" to every table containing field "JWA_ono" and assign its value to the new field
'Created By : Ray
'Updated By : Ray
'this will add a new field "ord_ref" to every table containing field "JWA_ono" and assign its value to the new field
debug(1)
adfield="ord_id,c,16"
dim tbl as P
table_name = :a5.table_enum("")
if (table_name <> "") then
while (table_name <> "")
fieldlist=TABLE.EXTERNAL_FIELD_NAME_GET(table_name , "n,t,W" )
if "jwa_ono"$fieldlist .and. adfield!$fieldlist
table.add_fields(table_name, adfield)
tbl = table.open(table_name)
if tbl.records_get()>0
tbl.change_begin()
tbl.ord_id=tbl.jwa_ono
tbl.change_end(.t.)
end if
tbl.close()
end if
table_name = :A5.table_enum(table_name)
end while
end if

MoGrace
07-04-2014, 07:50 PM
Hi Ray,
This can't be the whole script...how are you stepping thru the table_name list? Where is the loop?

Ray in Capetown
07-04-2014, 08:07 PM
Yes, sharp spotting there, I left that bit out. Realised while checking the results, too hurried,
Here it is included. The deepest IF condition replaces the "Change" lines with tbl_update - quicker than a loop.

adfield="ord_id,c,16"
dim tbl as P
table_name = :a5.table_enum("")
debug(1)
if (table_name <> "") then
while (table_name <> "")
fieldlist=TABLE.EXTERNAL_FIELD_NAME_GET(table_name , "n,t,W" )
if "jwa_ono"$fieldlist .and. adfield!$fieldlist
table.add_fields(table_name, adfield)
tbl = table.open(table_name)
if tbl.records_get()>0
update.fields = 1
update.field1 = "ORD_ID"
update.expr1 = "Jwa_Ono"
tbl.update()
end if
tbl.close()
end if
table_name = :A5.table_enum(table_name)
end while
end if

MoGrace
07-04-2014, 09:55 PM
Something I am not getting here. It looks to me like only the first table would be updated. Which part gets the next table_name?

MoGrace
07-04-2014, 10:01 PM
Whoops! Spoke before checking the Wiki.

a5.table_enum(TABLE_NAME) fetches the next table in the list, whereas a5.table_enum("") fetched the first. Sorry about that.

And thank you for that script! It is the real reason I butt into this forum ;)

Ray in Capetown
07-05-2014, 04:57 AM
Don't denegrate yourself you are a great contributor.
You are apparently involved in the sewing aspect relating to a manufacturing process, correct?
Interestingly, this program is a visual result oriented delivery chain control system, managing QC and scheduling delivery of every aspect like style, colour tests and samples etc to a schedule in "manufacture to order" of assorted apparel and garments that puts the manufacture under pressure.

MoGrace
07-05-2014, 01:15 PM
Actually we are Knitters only in the garment industry chain. Which involves 'assembling' one or more of a customer's yarns into unfinished griege goods. So we are not as complicated, but the inventory is still a bear.