I have a projects database and a contacts database in FM 8.

A single project may have several contacts from several companies participating.

How can I relate many contacts to my single projects database? One is simple the multiple has me stumped.

Once I accomplish this I want to create a portal in the contacts database that will show me all projects a specific contact is working on.

Edited May 11, 200619 yr by Guest

Sounds like you have a many-to-many relationship. One contact record can relate to many projects and one project record can relate to many contacts. To create this relationship, you need a third table called a join table. This is a fairly complex relationship setup that is best described in a resource like a book (try the Professional Foundation Training series available at the filemaker.com web site). Basically, you create a new table with at least two fields. These two fields are foreign keys for the primary keys from your contacts and projects. Likely, you will add a portal to a layout displaying records from your projects table. Make sure the create records option is checked between your projects table occurrence and your join table so you can add records through the portal. You'll also probably want a popup menu on the contacts foreign key from the join table that will be showing in the portal. When you add a contact to the portal, a new record will be added to the join table with the contact id you selected and the project id from the current record. This may seem silly since now you have a whole bunch of records with just two fields. But, this powerful technique has just joined your projects and contacts table. This will allow you to do some amazing things with reporting and sharing of fields. Anyhow, I think this is worth a file some I put a quick one together in 5 minutes and attached it to this response. Enjoy!

join.fp7.zip

Hi John, I'm looking for a similar solution. This really helped me. Do you know if there's a way to get it to include the redundant data when exporting? For instance, I have ITEMS and CATEGORIES. It's a many-to-many and so I have a join table with a concatenate primary key. Your file seems like what I need for my solution, however, my export needs are different. If I have an item record that has multiple categories, I need it to export as follows:

Sterling Silver Ring 19.99 all-rings

Sterling Silver Ring 19.99 stoneless-rings

Sterling Silver Ring 19.99 pave-rings

etc.... for all my products.

Thank you very much - your help is greatly appreciated!

Gordon