Husband & Wife Relationship (newbie)

[Stu in Plymouth]

I have looked through the forum and unfortunately cannot find the answer to this (maybe it is too basic).

I have built a Contact Manager which will track not only names, address etc, but also tasks, opportunities and product sales for each contact.

However what do I do for husband and wife contacts where they may have joint products (or children!!)? In the past I included all of the 'partner' information in the main table (Clients) by simply duplicting all of the Client fields and re-naming them parnerxxxx. The problem here is that if there is no partner, there are a lot of empty fields on the layout.

Then I would mark a product/task etc as owned by either client or partner (and show the correct name on the layout). However this time I want to have each individual person as a distinct client (which I guess is the right way to do it).

Therefore, when viwing a client, I want to be able to relate them to their partner, and see these details on the same page. So far I have copied the Filemaker 10 template, and have a portal showing similar records by name, company, town, which is OK but not really what I want to do - I want to just link one record to another (an perhaps use the 'similar' portal thing in another part of the solution).

Lastly, if I want to send a letter, or produce a report, I need to be able to generate a 'joint' one, with the same address details, then show individual products.

Any help would be very welcome, as I do not understand how to set this up.

[Vaughan]

There is no single right way to do this.

What you might be referring to is a "family" or a "household" as a separate entity. The family or household can contain one or more contacts, who can also be members of other families or households.

This is a m any-to-many relationship so it will need a join file. From your post it sounds as though you're not familiar with join files; technically they are not difficult to implement but the initial concept can be hard to grasp, as well as all of their implications in a design.

Joins are used in many solutions like invoicing systems.

[dansmith65]

I'm glad you brought this up! I have been considering the "ideal" contact list setup for a while now. I'm not sure if my thoughts apply to your specific application, but here they go...

The "customer" should always be the highest level of legal entity (i.e. the company the person works for), then, in a related table, hold the "contacts" information (i.e. the people working for the company). Related to the "contacts" table, is a "ContactInfo" table containing unlimited email addresses, phone numbers, etc. for each contact. Related to the "Customer" table, is an "Addresses" table containing unlimited addresses for the company (a default billing and shipping address must be specified among these addresses).

That was a quick overview of what I currently consider to be the "ideal setup". I'm curious to hear what other peoples thoughts are on the subject. Also, I'm hoping this might have helped to answer your question

[comment]

The join table might be unnecessary in this case, if (as it seems) the membership of an individual in another "household" is insignificant for the purposes of the solution.

So it could be just a table of Households (or Partnerships), with a child table for not much more than the associated individuals' names.

[Stu in Plymouth]

Thanks for your comments so far. Perhaps if I am more specific it might help.

My solution is designed to manage my clients records. Therefore a client may be single or married (etc). If they are married, it is likely that the spouse will also be a client of mine. They may have their own policies, or share a joint policy.

In my older solution, I simply created a new record in the client table, and had partner fields in the table (whether they would be used or not). As such the layout showing the main information would have lots of empty fields if there was no partner. It seemed to me this was an inefficient way to do things, and also did not allow me to treat a husband/wife as 2 distinct clients in their own right.

Now I am setting each individual up as a seperate record, but want to be able to link 2 records together, as husband & wife. Then I would find a record, and if there was a linked partner, their details would be shown on the layout, under a tab I have called Related. The way this is working he at the moment is via a portal which finds records with similar name/company/town (taken from one of the templates). This is ok, but not really what I want, other than to see all contacts who work for the same company, or belong to a group (which is not really what I use the solution for)

Assuming I can link 2 records from the same table together, and see both sets of details on my layout/report, I then need to be able to create a jointly owned record - where the clients may have a joint policy.

I am sure this is quite simple for someone who knows what they are doing, but I am a bit of an amateur Im afraid; but I think I know enough to keep up!!!

[comment]

Let me suggest another possible approach for your consideration:

Suppose we track Policies as our "main" table. A policy can have a number of individuals associated with it, in different capacities (holders, beneficiaries...). So we would have:

Policies -< Roles >- Contacts

and the association between contacts would be only through policies in which they happen to co-participate.

IOW, when viewing a Contact record I would be able to see (1) a portal of all the policies in which this contact plays a role, and (2) a portal of all other contacts that also play a role in any one of these policies.