First you have to have tables and relationships in place that work for what you need.
Am I correct that a contact can participate in many events and that an event needs to be linked to many contacts?
If so, you have a many to many relationship and you need something like this:
Contacts::__pkContactID = Contact_Event::_fkContactID
Events::__pkEventID = Contact_Event::_fkEventID
For an explanation of my notation, see the first post of: Common Forum Relationship and Field Notations Explained
If that is what you have, you may want to examine this demo file for ideas: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
If you are using FileMaker 12, use Open from the File menu to open this file and get a converted copy you can examine and test in FileMaker 12.
Thanks for such a quick reply! However I am quite baffled, hopefully I'm interpreting right:
So if I'm reading you correct...I'm required to create a third table to link the contacts and events tables?
WIthout the third table you won't be able to work with the data to link the same contact to differeent events and the same event to different contacts.
Say you have these two events:
1 House Party
And these two contacts:
23 John Smith
34 Fred Jones
To link John Smith to House Party, you would create a record in the join table with these values:
To link Fred to the Wedding, you would create:
The portals you see on the layouts in the demo file are portals to the join table. Selecting a value from the drop down list on a blank row in the portal creates a new record in the portal and enters the needed ID numbers into the two _fk fields to build a link between an event record and a contact record.
PhilMod, thank you very much for the help you had given me previously. I was not clear on how portals and relationships worked and so while your responses were extremely helpful, I was not properly equiped to understand them.
I recently went through a series of online training courses for FMP12 and have a better grasp of many of the FMP concepts. Because of this, I have managed to link contents from Table A into Table B using related fields and portals.
What I find my problem now to be is that using related fields to display a record from Table A in Table B only allows for one record to be displayed. That is, if I have two sets of "First Name, Last Name, Phone, Address, etc." fields in Table B, choosing a record from Table A in the first field of the first set will populate the second set with the exact same info.
Whereas if I use portals, I can display one, two or twenty records from Table A in Table B, however each record displayed does not retain a unique identifier. That is, I cannot have three clients shown in Table B and have each one as Client 1, Client 2 and Client 3. So for example, if I wanted to take the last names of the three clients I have shown in Table B and combine them in one field, there isn't a way for me to do so because each client's last name belongs to the exact same "::Table A Last Name" field.
Am I mistaken on something here? Is there a way to uniquely identify each record in the portal, or to have multiple sets of the exact same related fields showing different records in the related table?
Can you explain in more detail what you are trying to do and why?
You can certainly pull date from one field from multiple related records, but knowning why this is desirable is very important.
And while my example file uses portals as they are the simplest way for a new developer to set up something for working with groups of related records, there are also alternative methods that can be useful.
● Table CONTACTS holds multiple records
○ each record has fields like FIRST NAME, LAST NAME, ADDRESS, PHONE, etc.
● Table WEDDINGS is used to create records that contain info about one particular event/wedding
○ each record has fields containing the wedding info
○ each record also needs to display info about two contacts
■ i.e., WEDDINGS contains two sets of identical fields (CONTACT 1 FIRST NAME, CONTACT 1 LAST NAME...CONTACT 2 FIRST NAME, CONTACT 2 LAST NAME...) to display two separate records from CONTACTS
● In WEDDINGS, there are also three neccessary fields: BRIDE field which combines CONTACT 1 FIRST NAME and CONTACT 1 LAST NAME, GROOM field which combines CONTACT 2 FIRST NAME and CONTACT 2 LAST NAME, and EVENT NAME field which combines CONTACT 1 LAST NAME and CONTACT 2 LAST NAME
○ I need the BRIDE and GROOM fields because I used the full name of the two contacts to insert into a contract (in CONTRACTS table) and invoice (INVOICE table) which are both automatically created for each new record in WEDDINGS
○ I need the EVENT NAME field for identifying and sorting purposes used in other areas of the database
I hope that adequately explains my goals. Again, I appreciate your time and help with this.
Try this approach:
Add a field, named Role, to Contact_Event. Set it up with a value list. For the purpose of this example, it can have three values: Bride, Groom and Guest, but additional values for Role (Best Man, Matron of Honor, ... etc) are also possible.
You can then use either a filtered portal or an additional relationship to a different occurrence of Contact_Event to show the contact information for a contact with the specified role.