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.

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.

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?

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

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!

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

Migration

Thanks LaRetta. Very useful reference file.