many to many?
I am trying to set up a databse in Pro 10. What I am trying to do is have a contact conected to one or more businesses & a business contected to one or more contacts. I have a join table with a fk from the contacts & buiness tables. I can have a business with multiple contacts, but when I give a contact multiple bussinesses it creates another file for that contact.
The portal in the contacts table is based on the business table, and the portal in the business table is based on the contacts table. So when I enter a new business with it's contacts on the layout in the business table, I get the contacts listed individually on the contacts table with the proper businesses. The problem is that if I go into the portal on the contacts table and enter a business that is already in the database, I get another duplicate record of the business.
My point exactly. These portals should be based on the Join table instead. Linking a record in contacts to a record in business requires creating a new record in the Join table not one of the other tables.
Take a look at this demo file for some ideas in how to work with a many to many relationship: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
If you are using FileMaker Pro 12, you can open this file from the File menu in FileMaker to get a copy converted to Filemaker 12 format.
When I set it up like the example I cannot create records in the protal on the business table unless I select "allow creation" in the edit relationships window. When I set the portal up as a adrop box it still creates a new record for an already existing contact.
Maybe I should try to explain what I am trying to do again. I am using FP10. I am trying to set up a database in which I have businesses and contacts. Each business could have multiple contacts and each contact could have multiple businesses, and they may share contacts and businesses. I now have a portal on the business layaout and the contacts layout showing the join table. When in the business layout I can enter a new business and enter contacts for that business in the portal. This contact and related business show up in the contact layout. How can I enter an existing contact into a new business without creating another record for that contact. This business may have contacts not already in the database as well as contacts that already exist in the database.
Please examine the demo file. It should open and funciton correctly in FileMaker 10.
The key is to use portals to the join table with "allow creation" specified for the join table.
If you are entering data into the blank "add row" of a portal to the join table while on the contacts layout, you create a new record in the join table linked to the current contacts layout. When you select a business from the drop down list or pop up menu listing businesses, you are copying that business record's ID number into the foreign key field of the join table. This then links the current contact to the selected business.
This will not create a new record in the business table and if you delete a record from this portal, you delete a record from the join table--which removes the link between the two records, but this should not delete any record from contacts nor businesses. If it does, a delete option has been specified in the relationship that should not be selected.
Exactly what method are you using to do that?
If you are using a portal, It sounds like the portal is based on Contacts instead of the Join table.