relate two tables...

[duncs]

In my head this seems easy... however once I sit down to make it work... I can't, then again, I'm a newbie!

I have a Contact Management database that I have created. Recently I added the mail.it plugin. I now have two tables with very similar fields. Because both tables are referenced in quite alot of different scripts and layouts I want to avoid deleting one or the other, instead (I think) I want to create a relationship between the two.

Both tables store contact info, but with different field names. They both have First Name, Last Name, Address, Email address etc. Both have a Client ID field which is where I think the relationship would be created.

How do I auto-populate the details in one table if I add a new contact in the other table? Presumably I need some sort of script that is trigged when a new record is added, but it would also need to work if I imported data.

Any help is greatly appreciated!

[AudioFreak]

Hi,

You are correct in that you want to use the Client Id field for your relationship. The fields you want to auto populate will need to be changed to be a lookup based on the relationship you set up. See the attached it should get you headed in the right direction.

Michael

Lookup.zip

[duncs]

Thanks for the prompt response AudioFreak!

I have set all of the fields in Contact (except ID) to lookup, and entered a new contact on the original table, but it hasn't appeared on the Contact table!

I have set the relationship as "=" with create and delete records set to on, and each field has indexing turned off (in the "lookup" table)

Am I missing something?

[AudioFreak]

No need to have create and delete turned on. When you enter the Contact Id is when the lookup fields should populate.

If you can could you post a copy of your file to take a look at?

Michael

[duncs]

Hmm, tried to upload the file but keep getting an error message saying my permission level does not allow it, or something... any suggestions?

[AudioFreak]

Make sure the file is zipped. You can't upload a FP7 file directly. The file types accepted are:

zip, txt, pdf, bmp, gif, jpg, png, sit, and mov.

That would be my guess as to why you are getting an error.

[duncs]

I think I've worked out why I can't upload, it says the maximum file size is 1.5mb, and my file is 24.5 mb I could email it to you if that would help!

[Lee Smith]

You might be able to reduce the size by saving as a No Record Clone, and the making a zip file out of it.

Lee

[duncs]

Unfortunately, even a zipped up No Record Clone is still 5.3 mb My DB must be a lumbering thing with too much junk!! If you have any other ideas, I'd be happy to hear them!

[AudioFreak]

If you like go ahead and email it to me, and I will take a look.

Michael

[duncs]

Ok, I think I have a solution! Click on the link below and the file is available there for download. It is the only file on the site, but to avoid any confusion, it is called CMDB.fp7.zip.

The two tables that are of concern are Client Info and Contacts. Basically if you add a record in the Client Info table, it should automatically update the Contact table.

Thanks for your time and advice!

http://homepage.mac.com/duncsandbecks/FileSharing10.html

[AudioFreak]

Ok took a look at what you have. You have quite a bit going on and you appear to have some work behind and ahead of you.

Your relationship seems ok but I would not have the delete records set up on both sides of the relationship.

If you put the related fields in a portal on the layout you have named "Database" and create the records from that portal all should be good.

I tried stripping the file down so I could add it here but it's still too big. If you want I can email it to you.

Michael

[duncs]

Thanks Michael, as I said, its a bit of a mess!

I'll try setting up the portals and see if that works.

[AudioFreak]

Here is a new file using your layouts. I removed all that really wasn't needed to help you along with this.

Michael

DisplayRelated.zip

[duncs]

Wow thanks Michael, you really have gone above and beyond - I shall let you know how it goes.