Formula for displaying members of a household

[Vlad_Morosan]

I’m working to develop a directory in Airtable that will also export pages to PDF, and I need some help with a “conditional formula” (don’t know if that’s the correct terminology) that will pull in certain records in what I think is a self-joining relationship (but I’m not sure).

I started by converting a Filemaker Pro solution to Airtable, but then for various reasons set forth in this article https://support.airtable.com/hc/en-us/articles/360007520454-Combining-multiple-tables-into-one-table..., I decided to combine the several tables into one.

There are 3 essential views:

1. Individuals
2. Households
3. Directory display (grid and gallery views)

Each household has a unique number and {householdname}, which identifies to which household an individual belongs. In the household, there are “positions” - head, spouse, child, and other adult(s).

In the Directory display view I would like the following fields:

Photo

Household occupants:
({lastname} {, " {firstname} [head] & "& " & {firstname} [spouse]
(skip line)
{child}(ren) [firstname} only, as many as there are, separated by comma-space]
(skip line)
{other adult}(s) [firstname & lastname]
(skip line)
(skip line)
IF not empty: {cell} [of head] & ": " {firstname} [head]
IF not empty: {cell} [of spouse] ": " {firstname} (spouse)

IF not empty: {email} [of head]: {firstname} [head]
IF not empty: {email} [of spouse]: {firstname} (spouse)

So my logic for populating the “Household occupants” field is this: IF {position} is “head,” pull in {lastname} and {firstname} of head, separated by a ", ";
next: IF {spouse} > 0, pull in {firstname} of spouse after head, separated by " & "; however, IF {lastname} of spouse ≠ {household}, display {firstname} and {lastname} of spouse.

Seems simple enough when I say it in words, but how to make this formula work in Airtable has eluded me for a number of frustrating hours. Any help would be greatly appreciated.

================

For the field {children}, pull in {firstname} only of child 1, child 2… etc.
In the same field or next to it, display {firstname} {lastname} of {other adult}s

Same with cell phone no and email. But if I get the logic of the first formula correct, I think I can extrapolate from that.

Again, thanks for any insights!

[Vivid-Squid]

Hi @Vlad_Morosan
I can get you started on the first one and you should see how to do the rest.

“Household occupants” field is this: IF {position} is “head,” pull in {lastname} and {firstname} of head, separated by a ", ";

IF needs to be inside of () execute the logic first, if true, else. Each item separated by a comma so the way you are saying it is just how it goes into the formula. Concatenate() brings the values of the fields into a single text value.

IF(position='head',CONCATENATE(lastname,",",firstname),"")

Now you can start to nest your statements inside each other: https://support.airtable.com/hc/en-us/articles/360041812413

Give it a try and see where you can get. I can help you more if you need.

[Vlad_Morosan]

@Vivid-Squid
Thanks for the reply to my query. And thanks for the encouragement to proceed on my own, but I’m afraid I didn’t get very far beyond the first step. The formula you provided did exactly what it’s supposed to do, but beyond that, I’m wondering if the logic I verbally described in my original post was faulty.
Step 1 concatenates the lastname and the firstname of the ‘head’ of household.
Step 2, however, needs to bring in the firstname of the ‘spouse’ – a different record belonging to the same household.
How does the database know which ‘spouse’ to bring in?
The ‘_pk homeid’ field (which happens to the primary field) has to match the ‘_pk homeid’ of the ‘head’ brought in Step 1.
I know there’s some way to do that, but it seems to require going beyond simply nested IF statements. We have to tell the formula to set a certain ‘_pk homeid’ as a variable, and then relate its operations to that number, that is to say, that household.

Similarly, with children: first we have to tell the database which ‘household’ (_pk homeid) we’re looking at, then tell it to concatenate all the children’s names from that household.

Airtable does a very nice job of organizing the individuals by household using the “Group by” function. Now we just need to pull the names in the group into a calculated fields, one for adults and another for children. Attached is screen shot of how the grouped records look.

[Vlad_Morosan]

@Kamille_Parks

I posted this question about a week ago and haven’t gotten any further responses beyond the first one, which only partially addressed my dilemma.
I found your thread on the community forum: Pull multiple values into one cell - #2 by Kamille_Parks, which seems to resemble what I am trying to do. You were about to give a step-by-step instruction, but then said “Never mind” and instead put the reply into a private video. Could you possibly look at my question and see if you can give it a detailed response. Thank you.

[Kamille_Parks]

Vlad_Morosan:

How does the database know which ‘spouse’ to bring in?

You’re not going to be able to assemble your intended result until your records are linked together. Simplest way to do that would be to convert the {household} field into LinkToRecord field pointing at a (new) table for Households. The {pk homeid} field should live in the Household table since its unique per household, not unique per individual.

Your Households table will have a series of either Lookup or Rollup fields to pull in the {first}, {last}, and {ph position} fields. For your particular use case, that would mean one Lookup field pointed at the {last} field with the condition applied for “Position = head” and another Lookup also pointed at {last} with the condition “Position = spouse”. Now a formula field can determine if those two values are the same. You’d also want one Rollup with the ARRAY_JOIN(values, ", ") aggregation pointed at the {first} field with the condition applied for “Position = child”. That will give you a comma separated value of the children’s first names.

The formula you’re after would generally follow this format, and would have to be in the Household table, not the Individuals table (LU stands for lookup, RU stands for rollup):

{LU Head Last Name} & ", " & {LU Head First Name} & " & " & 
IF(
   {LU Head Last Name} != {LU of Spouse Last Name},
   {LU of Spouse Last Name} & ", "
) & {LU of Spouse's Name} & 
"\n" & {RU of Children names}

[Vlad_Morosan]

@Kamille_Parks
Thank you for the speedy and informative reply. I sort of follow, but am actually quite a newbie when it comes to Airtable formulas. Most of my formula attempts bring up the the dreaded red “invalid formula” message, so I hope you can offer some deciphering of your shorthand once I set up the tables and fields as you suggest.

For example, how would the expression {LU Head Last Name} actually look in a formula, with parentheses, braces, etc?

[Kamille_Parks]

In airtable formulas, {this} means you’re telling Airtable to insert the value of a field named “this”. Assuming your field is literally named “LU Head Last Name”, then you would insert the formula I wrote, exactly as I wrote it.

[Vlad_Morosan]

@Kamille_Parks

So far so good! Table “Households” created and linked via the _pk homeid (I think that’s what happened). LU fields created for Head Last and Head First, and Spouse Last and Spouse FIrst. When I tried creating a RU field for Children, however, I hit a snag: it told me “Sorry, there was a problem creating this field. The options are not valid.” But then just for the heck of it I tried creating another LU field for Child First, and it gave me exactly the results I wanted – a list of the children’s names separated by commas. Not quite sure why the RU didn’t work. Perhaps it had to do with how I created the related field: basically what I have is a field with a string of all the names in each household, separated by spaces.

When I applied your formula for the Household occupants, it basically worked but with two small problems: 1) for single Heads of households, there was a trailing " & ,", and 2) the children’s names (from my renamed {LU Children names} field) were NOT separated by commas, whereas in the table field they are.

[Vlad_Morosan]

@Kamille_Parks

Hi, I got the Rollup field to work. Now if would just help me get rid of the extraneous " & ," after the names of single heads of households, this issue will be solved! (see Nos. 3 and 4 below)