Renaming a key linking field over a dozen tables in a DB

[Ray in Capetown]

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]

Re: Renaming a key linking field over a dozen tables in a DB

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]

Re: Renaming a key linking field over a dozen tables in a DB

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]

Re: Renaming a key linking field over a dozen tables in a DB

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]

Re: Renaming a key linking field over a dozen tables in a DB

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]

Re: Renaming a key linking field over a dozen tables in a DB

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]

Re: Renaming a key linking field over a dozen tables in a DB

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]

Re: Renaming a key linking field over a dozen tables in a DB

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]

Re: Renaming a key linking field over a dozen tables in a DB

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]

Re: Renaming a key linking field over a dozen tables in a DB

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.