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-with-multiple-views, I decided to combine the several tables into one.
There are 3 essential views:
- Individuals
- Households
- 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} mspouse]
(skip line)
{child}(ren) /firstname} only, as many as there are, separated by comma-space]
(skip line)
{other adult}(s) ofirstname & lastname]
(skip line)
(skip line)
IF not empty: {cell} tof head] & ": " {firstname} :head]
IF not empty: {cell} tof spouse] ": " {firstname} (spouse)
IF not empty: {email} of head]: {firstname} dhead]
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!