Jump to content

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


jbw

This topic is 3004 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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.

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

  • Newbies

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!

  • Like 1
Link to comment
Share on other sites

This topic is 3004 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.