Linking Contact To Multiple Households

I’d use a third table, [Role], that would consist of

1. A link to [Household]
2. A link to [Contacts]
3. Your {Household Role} field.

When it came time to specify a household role, you would create a new linked [Role] record from {Household], link to the appropriate [Contact], and specify the role. That way one contact could be assigned roles for multiple households, aqnd a household could have multiple individuals with roles assigned.

Okay, so I’ve created a Roles table with the fields you suggested. The challenge I’m having is how do I group all the contacts by Household while also showing their role within that household. I’m assuming I would do this with a New View in the Contacts Table and call it “Household View”, but I’m having a hard time figuring out how to get all the data and fields I need to make this work.

Well, it depends on how you wish to view the data. If what you want is a separate row per contact role, you’ll need to create that view in the [Roles] table and use lookup/rollup fields as appropriate to retrieve whatever additional data you require from the applicable [Household] or [Contacts] tables.

If you want to do it from within [Contacts], I’d first create a formula field in [Roles] with the formula {Contact}&': '&{Household Role}; then, from [Contacts] I’d roll up that field using a rollup field with the aggregation function ARRAYJOIN(values,'\n'). Add that rolled-up field to your <Household View> view and set your line height to something other than ‘Short’. (The ‘\n’ in your aggregation function will cause a line break in the rollup field in any view with a line-height of ‘Medium’ or larger.)

That’s great feedback, thanks for this. I did manage to create the appropriate fields as per your message above. The challenge I’m having is I’m trying to create a view that groups a household and shows each contact’s role within that household. I can’t seem to quite get the view I’m looking for.

Here’s an example:

Household # 1: Smith, John & Jane

• John’s role within this household is “Primary”
• Jane’s role is “Spouse”
• Tina’s role is “Child”, I don’t include her name in the household name because she has her own household

Household #2: Smith, Tina

• Tina’s role within this household is “Primary”
• John’s role within this household is “Parent”
• Jane’s role within this household is “Parent”

I’m now trying to create a view that group’s all the data by “Household Name” and then each row below the household grouping shows each contact record associated with that unique household name and their role within the household.

I’m a little unclear as to where the best place to create this view is. From what I can tell, this view can only really effectively be created in the ROLES table.

Whenever I try to group contact records by Household Name, any time a contact is associated with more than 1 household, Airtable is combining the households in the group view such as follows:

“Smith, John & Jane”, “Smith, Tina”

This is because a record can only be in a single group. When the field used for grouping is a link field with multiple links, Airtable doesn’t clone the record and show it in multiple places (i.e. in a separate group for each linked item). It treats the link combo as a single item requiring its own group, as you saw, and that’s where the record is shown.

That aside (somewhat), I’m a little confused about your use of “household” to describe these groups you’re defining. It feels more like you’re defining different types of relationships, not actual household groupings. Are John, Jane, and Tina actually living under the same roof? If so, your listing of Household 1 is what I would assume would be the household setup. Because Tina is apparently an adult (otherwise why would she be a primary in another household?), your Household 2 list would technically also be valid to describe their relationships, but IMO using secondary definitions of their roles. If, however, Tina is not living under the same roof as John and Jane, then I would be inclined to remove her from their household and make hers a household of one. Then again, not knowing the full extent of your use case, perhaps the setup you’re describing makes some kind of sense that’s just not clicking for me yet.

I’m essentially trying to capture relationships of people that may reside at different addresses but are related. In the example above, Tina is an adult who is living at her own address/location. She is a client of mine as are her parents who reside at a separate address. Tina is not only their child but also their Power of Attorney.

I’m trying to find a way to capture all of the relevant and important relationships between these contacts within the household itself. So for the example above:

Household Name # 1 : Smith, John & Jane
John - Primary
Jane - Spouse
Tina - Child, Power of Attorney

Household Name # 2: Smith, Tina
Tina - Primary
John - Parent
Jane - Parent

In this manner, it doesn’t matter which household I’m currently looking at in Airtable, I get a quick look at all of the relevant people to the household. I plan on using this type of setup to also capture each household’s accountant, estate lawyer, executor etc.

Salesforce did a great job of allowing me not only capturing these relationships, but also creating a a nice household group view where every household name is listed and right under the household name would be all the contacts associated with that household and their role for that specific household.

Right. As @Justin_Barrett points out, a record can only be a member of a single group. Accordingly, you’re not looking for each contact record associated with that household, you’re looking for each role associated with that household: Hence, you need to create the view within [Roles].

Take a look at this, especially the grouped view in [Roles]. Is that what you’re trying to achieve?

I should add, this structure is a bit annoying to populate manually, but there are ways to accelerate or automate it in part.

That was the key I missed from your previous post, creating the view from within the Roles table. That worked out great and exactly what I was looking for. The one thing I wish I could do is hide the primary field. I used a Role ID field with auto number for the primary field and don’t really need to see it but Airtable doesn’t allow us to hide the primary field.

Question, with this new Roles table now in effect, is there any point in linking the contacts in the contacts table to the households in the households table? Trying to figure out if I still really need that link.

Also, how would you try to automate or simplify data entry for this? That’s the one caveat where I would first have to create the household, then the contact records, then create an association in the roles table. Wish there was a simpler way.

Your two questions are actually related.

I don’t know your work flow — and I’m about to step away from the PC for a while, so I can’t continue this conversation until later — but the way I’d streamline the process would be to find some naming convention that allowed me to create links and new linked records through pasting values into linked-record fields, as explained here. Again, it might not work at all for you, depending on your data sources and process, but one way might be to create contacts first, create roles (with assigned contacts) second, and then paste household values into your newly created [Roles] records, which would handle creating the [Households] :left_right_arrow: [Roles] links automatically, You could then copy from a lookup field in [Contacts] and paste into a linked record to create [Households] :left_right_arrow: [Contacts] links, if necessary.

Alternatively, you could possibly first create [Households] :left_right_arrow: [Contacts] links manually and then use the technique I discuss here and here to limit potential [Contacts] that could be selected as a linked record from [Roles].

In the latter instance, you would define a {Selected} checkbox in [Households] that would drive a dynamic view in [Contacts] — assuming you have define which contacts belong to which households — from which you could choose [Roles]. Since defining the [Households] :left_right_arrow: [Contacts] links is a matter of selecting the records to which to add a household link and pasting the value,* winnowing down the potential [Contacts] to be chosen as a link from [Roles] conceivably might simplify things. On the other hand, if you’re typically adding [Contacts] individually rather than en masse, the savings might be minimal.

* OK, so it’s not quite that easy, since a contact might support multiple households — but it’s still pretty easy.

I do have a follow up question related to households and interactions. I would like to be able to print some sort of a report that shows all the interactions I’ve had with a household. I like to print this report as a way to review my household notes before a meeting.

My interactions table currently captures the date, contacts related to that interaction, the type of interaction, subject, multi line notes field to capture my notes for that interaction.

Does airtable have any report generating functionality where I could say, print a report for this household showing in chronological order all my interactions and detailed notes? I’m assuming I would have to include a lookup or some roll up field in the interactions table to have the associated household available for the report as well? I’m not quite sure how to accomplish this nor whether I can even print such a report in airtable.

Any suggestions would be greatly appreciated.

Thanks

@Marco Do you still use Airtable in your practice? I am also pondering using airtable for this very thing. Collaborating would be greatly appreciated!