Linking Contact To Multiple Households

[Marco]

I work as a financial planner and I’m absolutely loving the flexibility of Air Table. One thing, however, I’m struggling with is that I want the ability to link a contact record in the contacts table to more than 1 household in the households table and specify what the role of the contact is for that particular household.

What’s the best way of setting this up? I’d like the ability to have a Household View so that it lists all of the contact records associated with that household and what their role is in that household.

Currently, I have a linked record between HOUSEHOLDS and CONTACTS and I have a “Household Role” field in the contacts record. But I’m realizing this won’t work because it will show the same role for both households which may not be the case. For example, a contact might be “Primary Client” in one household and a “Power of Attorney” for another household.

Any ideas/suggestions how best to accomplish something like this? I’ve created a “Household View” in the Contacts table where I can group all the contacts by a household and that works flawlessly for contacts that are only members/associated with one Household. but the second they are associated with two households, this is where it sort of breakdowns and I’m unable to properly group them or identify their role in that household.

Would appreciate any comments/feedback.

Thanks

[W_Vann_Hall]

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.

[Marco]

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.

[W_Vann_Hall]

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.)

[Marco]

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”

[Justin_Barrett]

Marco:

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.

[Marco]

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.

[W_Vann_Hall]

Marco:

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.

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?

Airtable

Household Roles demo - Airtable

Explore the "Household Roles demo" base on Airtable.

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

[Marco]

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.

[W_Vann_Hall]

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.