These days, family relationships get complicated. People divorce and remarry and they sometimes keep attending the same church. Kids then end up listed as "members" of multiple families. Only one will be "custodial" but you often want to document the other relationships and be able to notify all parents and relatives of upcoming events for their child whether or not they are the custodial parent.
One way to do this is like this:
Contacts::__pkContactID = Contact_Family::_fkContactID
Families::__pkFamilyID = Contact_Family::_fkFamilyID
(If this notation is new to you, see this link: Common Forum Relationship and Field Notations Explained )
You put a name, phone number, age, gender, email, social network site URL's etc in Contacts.
You put FamilyName, Mailing Address/physical Address, Home Phone in Families
Contact_Family is called a "join" table. It need have nothing but the two _fk fields, but you may find it useful to add a "role" field to it to document info such as Husband, Wife, Child, etc.
This is called a many to many relationship. You can put a portal to Contact_Family on the Contacts layout and use it to list all Families to which you have linked that contact. You can add fields from the Families table to the rows of this portal to display additional info about each family as needed. Put a portal to Contact_Family on a Families layout and you can use the same methods to get a list of all contacts that are a member of that family.
Here's a demo file that illustrates both that layout design and also some more sophisticated design options for working with many to many relationships: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
Thank you so much!
Someone else had a post similar to this and I created a sample app. I created the sample using fma12. You can download a trail copy of 12 and a copy of this sample app to give you an idea.
Download free trail of 12
I have been kind of stuck...
Contacts::__pkContactID = Contact_Family::_fkContactID (allow creation, delete related)
Families::__pkFamilyID = Contact_Family::_fkFamilyID (allow creation)
I have the p/f keys setup properly, the relationships linked (though I dont know for "Add" and "Delete" on the relationship settings page for the relationships above... they are probably wrong, so please advise on that).
I think my issue is that the Contact_Family has no records... how can I get it to list the __pkContactID in the _fkContactID field of Contact_Family, for every record in the Contact (and add a new one ,whenever i create a new Contact record).
Currently, the portal dropdown I have setup will only allow me to choose a family, if I have a record setup in it already, hence the question above.
how can I get it to list the __pkContactID in the _fkContactID field of Contact_Family, for every record in the Contact (and add a new one ,whenever i create a new Contact record)
You seem to be correctly set up to do this. If you have a portal to Contact_Family on your Contacts layout, you can select a family in the drop down and the info from the selected Families record can appear in that portal row if you add the fields you want. Such a portal and relationships is so that you can link a single contact to multiple families. (Divorce, remarriage, link to one family as a parent, to another as a child, etc.)
Currently, the portal dropdown I have setup will only allow me to choose a family, if I have a record setup in it already, hence the question above
That doesn't sound like you have a portal to contact_family. Either that, or the "allow creation" option for contact_family isn't enabled like you have described.
Yah, at some point the portal got deleted... i figured that out after I posted...
Bear with me please....
So, I have it working almost as intended....
When I choose from the dropdown, I have the values list setup so it shows the family names (what i want).
But, once the option is chosen, since the dropdown is actually yhr Contact_Family _fkContactID, it only shows that ID.
How can I get it to show the family name when it's not in the drop down mode... thank you much.
Answer To Above...
I changed it to a popout menu, and that worked great!
There are also ways to get this to work with the drop down option by placing a copy of the name field from Families on top of the ID field and setting behavior on the name field to prohibit user entry.
Thanks, i will definitely remember that if I ever have to use a drop down.
In the original reply, you said "Put a portal to Contact_Family on a Families layout and you can use the same methods to get a list of all contacts that are a member of that family."
Is there any possible way to do this while on a layout based on the Contact Table? I want to see all of the people who have the same family ID. Thanks.
The same basic method applies, but portal to Contact_Family on the contact table will list all families linked to that contact. It's possible to add more occurrences of the join and contacts tables on the far side of Families to get a list of all related contacts, but in cases where you link a contact to one family, the list of related contacts will be a combined list of all contacts from all linked families--so this may or may not be what you want:
Contacts-----<Contact_Family>------Families----<Contact_Family 2>------Contacts 2
A portal to Contacts 2 on the contacts table will list all contacts related to the current contact record. (Create the two occurrences with 2 in their names by selecting the original "box" of the same name and then click the duplicate button (two green plus signs) to make the new occurrence of the same table.)
Makes total sense... didn't even occur to me to put more occurences of the tables into the relationship.
Worked like a charm! This noob even figured out out how use that Contacts 2 table to setup some conditional formatting to highlight the current record's name in the portal listing all family members.
Plus, thank you for the "+" button on the events sample you provided above. I used the script as an example and had to make some tweaks based on my layout, but I have it add the family and update the current contact's record (the popout menu) with the new family's name.