Complex Relationships

[Simon Smailus]

Hi, I’d like some advice about how to handle complex relationships. I’m trying to build a contact directory. I would like the directory to show the relationships between the contacts. Let me illustrate:

The contact directory lists 4 people,

Joe Bloggs
Jane Bloggs
Janet Bloggs
James Bloggs

Joe and Jane are married and have two children; Janet and James.

How do I connect these in a form? So that when I click on Joe, I see that his wife is Jane and he has two children Janet and James? Also when I select James that I see his parents are Joe and Jane and that he has a sister Janet. This could get more complex with grand children and grandparents, but I’ll be happy to sort the first level at the moment!

Any help would be gratefully received!

[Mike Schwartz]

Simon,

It might not be as clean as you would like, but you can accomplish this using two related forms.

Setup. I worked up a small proof-of-concept example with a form named “Master Contact List” and another form named “Contact Relationships”. The Master Contact List will contain records with all the basic info that you want to maintain about each person, except for the relationships. In my example, this form only has two fields: First Name and Last Name. In a “real” case, these records might contain twenty fields for various phone numbers, addresses, company, work title, etc. The second form, Contact Relationships, will contain a slew of Link to Form fields, one for each type of relationship of interest. In my example, there are Link to Form fields named Spouse, Parents, Children, and Siblings. Each one links to the Master Contact List with a many-to-many relationship. There is one additional Link to Form field, named Primary Link, and one plain text field, labeled “Name”.

How it works. You start by populating the Master Contact List with records. In my example, I entered 4 records for Joe, Jane, Janet, and James. Then, switching over to the Contact Relationships form, I created the first record and typed “Joe Bloggs” into the Name text field. The reason for this text field is to allow something to show in the Records List side panel. It will not display info from Link fields. Then I went to the Primary Link field, and linked it to the Joe Bloggs record. Then I added Jane as a link in the Spouse field, and both James and Janet as linked records in the Children field. I’ve attached a screen shot of this record, with the Forms inspector visible.

You can easily add more Link fields as needed to cover cousins, grandparents, grandchildren, etc. When everything is entered, you’ll have the same number of records in the Relationships form as you do in the Master Contacts List. As I said, not clean, but workable. Don’t be tempted to try this in a single form with self-referencing links — things will get hosed up in a heartbeat.

Hope that helps,
Mike

Attachments:

You must be logged in to view attached files.

[Simon Smailus]

Many thanks Mike!

This is really useful. One quick question: If I create a “link to form” field in the Master Contact List called “Contact Relationships” and link it to the Contact Relationship form also checking the “show inverse relationship…”, could I then dispense with the Primary Link and would that achieve the same thing? This would give me a link in the Master Contact to the Contact Relationship form and vice versa.

[Mike Schwartz]

Simon,

Yes, you can do exactly that. Add the two-way link on your Master Contact List and delete the “Primary Link” on the Relationships form. In fact, you could also go to the Relationships form and select “Show Inverse Relationship” on all the link fields.

Tip: Be careful to review the field names when you create linked fields. If you start off by entering a field name and then change the field type, your field name will be overwritten. And then when you specify the link-to form, the name will change again. So do all that first, and then edit the field name to something meaningful. Likewise, if you let Tap Forms create the inverse link, you’ll want to go to that form and edit the default field name.

Now, just to be clear, you could have just added simple Text fields labeled Spouse, Parent, etc. to your original contact directory and typed in the names of those relatives (similar to Apple’s Contacts program, or even used a pick-list to select a name from all the existing records. But I figured that would be obvious, and I was keying off your word “connect” when you said “How do I connect these in a form?” Using the linked forms allows you to traverse the family tree: Start with Joe, double-click on James in the Children Field to hop to James’s record, etc.

— Mike

[Author]

Posts