How do I change match field to a unique ID in an existing database

[jbw]

Hi - OMG, I am at the end of my tether on this. Any help would be so much appreciated. It's probably an easy fix. Basically, I have a big database with a Company table and various linked tables (contacts, products etc.). It works well, and all the data is already populated, BUT I did a dumb thing on set up: the table relationship match field is the Company Name, which means if the company changes its name I cannot change the Company Name field as it is a match field (the portals on Company table layout just go blank as there is no match). I did a workaround (two name fields), but what I really need to do is have a unique ID (auto enter serial number) for the company table and just link it to the other tables.

So I created a unique Company ID in the Company table, and have popiulated this with an auto enter serial number - fine -  and then as per FMP's website I add a "Company ID" field to the Contacts table. But it does not populate or link. And I see in the forums/online that this works for NEW records I add. But I have 3000 existing companies in the database. I have tried several things but I just cannot get it to work. The Contacts portal on my Companies layout never displays anything. When I view Contacts in table layout, Company ID is always blank.

Thanks in advance for any help.

[Josh Ormond]

Since the relationship is already set up, go to the Contacts layout. You should be able to set the Contacts::CompanyID ( or whatever you have it named as ) field from the child record.

I would do 2 things:

1. Create a looping script that sets the Company ID field in the Contacts table ( or any other child table ). So it would look something like Set Field [ Contacts::Company ID ; Company::Company ID ]
2. Include in that script a test, to make sure there are not multiple matching Companies.

[jbw]

Fantastic. Thanks. It works one by one, but I am not that good at scripting - I am guessing I need something more than 1. loop, 2. set field as above and 3. end loop?

[webko]

Go to Record/Request Next [Exit After Last] perhaps?

[jbw]

Wow - that's just great. I went into the contacts layout as a table, clicked in the first record, did the loop adding the record/request line as above and just watched it fill them all in. Thanks Josh and webko - saved my day!

[LaRetta]

For future, or for others reading who may wish for detailed walkthrough with multiple child tables, this might help also:

Migration

[jbw]

Thanks LaRetta. Very useful reference file.