I'm working to adapt Calendar Pro Complete for use by a local rural social services and support office. The contacts they have to track range from medical professionals and counselors to office suppliers to the men, women and children who make use of thier services.

The Contacts module works well but breaks down when I need to show a relationship between a minor and his or her guardian(s). I thought I would just create a family "Company" and associate the family members but that doesn'r really handle the familial responsibility relationships.

I'm a bit of a butcher at this so I'm looking for some advice. Do I pirate the Company-Contact relationship as a starting point or create a whole new family-relationships Table and go from there i.e. ContactA - Son - ContactB, ContactA - Brother - ContactC etc.

Thanks in advance.

th

Right: you want a whole new relationship concept, and "companies" have nothing to do with it. (You may have companies in your database, but those records won't have this kind of familial relationship with people.)

One way to do this is to create a join table between another instance of the contacts table. This join table would have two contact ID fields along with a field for the "type" of relationship. Think of a relationship as being FROM one person TO another. So looking from a Father to his Son, you'd see the join record like this:

FromContactID: father's contact ID
ToContactID: son's contact ID
Type: "Son"

A second join record would describe the same relationship from the Son's point of view:

FromContactID: son's contact ID
ToContactID: father's contact ID
Type: "Father"

So you'll see that you need two records to describe every relationship. And the "related contacts" portal on each contact's record only show's those records that match from the Contact's ID to the FromID in the join table. That is, on a father's record you'd only see the join record for "Son".

Now making the two join records is what gets tricky and I'd suggest not trying to be clever about it but rather to script it, perhaps with a couple Custom Dialogs like this:

Script in pseudo code...

Select the record you'd like to link to the current record.


Call our "Select Company/Contact ( Operation , Action )..." script with the following parameter: "Operation = Begin ; Action = Choose"
That will drop the selected contact's ID into Filters::FilterChooseContactIDGlob

Show dialog: "This newly selected record is the current record's (son, brother, sister, etc.)" Show a global field for relationship "type".

Make the first join record.

Show dialog: "And the current record is this newly selected record's (father, brother, mother, etc.)" Show a global field for relationship "type".

Make the second join record.

Kind of elaborate, but worth it. FWIW, we use this in counseling settings where we have lots of relationships between patients, lawyers, case workers, siblings, etc.

Thanks John,

I think I follow and will report back on how it works. I think its a great tool and priced right for the not-for-profit groups. Hopefully I'll be looking for a Developers licence in the near future : )

th