Skip to main content

Base is registration and records system for small private school that’s been operating for 60 years. The two tables I’m working on now are PARENTS and CHILDREN.


In another thread here, I was trying to figure out how to copy addresses from linked records, but I think I’ve solved that question by using a formula field that calculates an address for each parent or child based on what records its linked to. In other words, if parent1 has an address, and parent1’s children and spouse do not have addresses, those records will all pull an address from Parent1. Minimizes duplicate entry and seems to work okay.



The question


My question is, in the PARENTS table, how can I identify marital pairs? I’d like to group them.



One thing I have tried (that doesn’t work well)…


For some reason (mystery to me), grouping by the Address formula field does not seem to work. Say Mary and John are married and reside together. Mary’s record, which was created first, stores the address at which she and her husband reside; this value is stored in a field that at the moment is called AddrThisRec. John’s record, created second, is linked to Mary’s record (on the field named “Spouse”) and so John’s calculated Address field displays the address it’s pulling from Mary’s record. So they’re identical. But when I group on that formula field, Mary and John are NOT grouped together.


And in any case, this isn’t a fool-proof system, because in some cases, happy married couples these days don’t reside together all the time (usually because one spouse has to be somewhere else for work). If one spouse is living locally and the other spouse has, say, a military address this year, we’d like to be able to send both of them mail.

how i would identify marital pairs: concatenate a pair of parents’ names (using their name + linked spouse’s name) into a new field (make sure it’s in alphabetical order–i was left with no choice but to use scripting to accomplish this part), and then group by this new field.


example base here:

grouping parents by marital pair


i made sure it held up to Big Little Lies-esque (HBO) scenario where John divorces Mary and marries Sally, and he has children Maria and Sarah from these 2 marriages, and they all go to the same school.


if we assume that that parents’ lives aren’t complicated; instead of creating a maritalPairName, i would simply do a isMaritalPair field that checks if a parent’s linked spouse name is their name (i.e. for John’s record, his spouse Mary also has John listed as her spouse). i would filter by isMaritalPair, and then group by children. this assumes that marital pairs share the exact same children.


i agree that grouping by address sucks. i would not do that. it makes the assumption that people who live together are married, which is untrue.


note: if there are two or more pairs of parents who share identical names, the groupings will be f**ked. i wanna say that it’s rare, but it isn’t impossible. we can solve this by making the maritalPairName unique with the use of record IDs.

how i would identify marital pairs: concatenate a pair of parents’ names (using their name + linked spouse’s name) into a new field (make sure it’s in alphabetical order–i was left with no choice but to use scripting to accomplish this part), and then group by this new field.


example base here:

grouping parents by marital pair


i made sure it held up to Big Little Lies-esque (HBO) scenario where John divorces Mary and marries Sally, and he has children Maria and Sarah from these 2 marriages, and they all go to the same school.


if we assume that that parents’ lives aren’t complicated; instead of creating a maritalPairName, i would simply do a isMaritalPair field that checks if a parent’s linked spouse name is their name (i.e. for John’s record, his spouse Mary also has John listed as her spouse). i would filter by isMaritalPair, and then group by children. this assumes that marital pairs share the exact same children.


i agree that grouping by address sucks. i would not do that. it makes the assumption that people who live together are married, which is untrue.


note: if there are two or more pairs of parents who share identical names, the groupings will be f**ked. i wanna say that it’s rare, but it isn’t impossible. we can solve this by making the maritalPairName unique with the use of record IDs.



Welcome to the Airtable Community, @voble! And thanks for your response.


So your field «alphabetizedMaritalPairName» is a data-entry, single-line text field, right? Not a calculation? I’d like to make this more or less automatic, so users don’t have to enter another item — and so I don’t have to worry about that additional data entry being incorrect.


Thanks.


Welcome to the Airtable Community, @voble! And thanks for your response.


So your field «alphabetizedMaritalPairName» is a data-entry, single-line text field, right? Not a calculation? I’d like to make this more or less automatic, so users don’t have to enter another item — and so I don’t have to worry about that additional data entry being incorrect.


Thanks.



no. it is computed.



no choice but to use scripting to accomplish this part



i mentioned that i got it with scripting. this means that i went to Extensions on my base, added a Script, then added JavaScript that takes my maritalPairName (a formula field that concatenates John’s and Sally’s Name) and sorts them in alphabetical order, and into alphabetizedMaritalPairName so that they can be grouped meaningfully.


no. it is computed.



no choice but to use scripting to accomplish this part



i mentioned that i got it with scripting. this means that i went to Extensions on my base, added a Script, then added JavaScript that takes my maritalPairName (a formula field that concatenates John’s and Sally’s Name) and sorts them in alphabetical order, and into alphabetizedMaritalPairName so that they can be grouped meaningfully.


Ah, yes, I saw that but it didn’t register. Thanks!

Ah, yes, I saw that but it didn’t register. Thanks!


Reading @voble’s solution made me realize that the main thing we need to do here is to get a unique value per marital pair?


If so, assuming I understand your base setup correctly, you could potentially do this via lookups and formulas like so as well:


Base link

Reading @voble’s solution made me realize that the main thing we need to do here is to get a unique value per marital pair?


If so, assuming I understand your base setup correctly, you could potentially do this via lookups and formulas like so as well:


Base link


Thanks for the response. Yes, you’ve hit the nail on the head — and your suggestion is just about identical to the solution I’ve devised. I’m still banging on it but I think it’s going to work.

Reply


Terms & Conditions