List linked records not linked

[Lauren_Briskin]

I think this may not be possible based on Access fields from linked record in formula without creating Lookup fields - #2 by Kamille_Parks but I’m going to try anyway!

I have a pretty complex base based on some longterm needs and incoming records and trying to squeeze things into Airtable that aren’t quite possible. Right now, I’m dealing with two tables that are linked together and linking existing records together.

Table 1: Main table.
Table 2: Form submission.

Table 1: Over a years’ worth of information about people, including linked records of people who are in relationship with one another.
Table 2: People who exist in Table 1 one are submitting updated information about themselves AND other people that exist in Table 1. When someone submits information via a form, we link their record to their record in table 1 (usually by the VLookup automation, but since this breaks a lot, often manually.)

Table 2 has an important field called “Can you continue to pay for groceries?” that is filled out by someone submitting a form. When the the submission is linked back to Table 1, a lookup field is created so this information shows in that table.

I would like to create a formula that shows the information in that field (yes/no) NOT for the person who submitted the form, but for the linked record in Table 1.

So:

• Person A and Person B are linked because of a relationship in Table 1.
• Person A submits a form, which appears in Table 2.
• Person A’s record in Table 2 gets linked to their matching record in Table 1.
• A Lookup field displays {important field from Table 2} on Table 1 for Person A.
• I would like to display {important field from Table 2} on Table 1 for Person B on Table 1.

Is this possible? (I’ve tried to explain this clearly, but please let me know if you have questions!)

[kuovonne · Accepted solution]

It sounds like you have some same-table links. One of the issues is that a same-table link only works in one direction; you do not automatically get a matching linked record field in the opposite direction.

Depending on the direction of the link, and who has the information, you can have a lookup of a lookup. That is, personA has a lookup of the field from the second table. Then personB has a lookup of the lookup. Note that this only works of personB has a linked record field linking to personA. The the link only goes in the opposite direction it will not work. Also, for each person you will also have multiple fields where the information might exist (lookup from the form, or lookup from the lookup).

You may want to re-examine your base structure. Would it make sense to have a “household” record?

[Lauren_Briskin]

Thanks- I can’t redo the structure of my base at this point- too many folks using it, too far into a complex project with bearing on folks’ lives.

I think the lookup of the lookup worked- it seems in most of the records, people using the base have been good about linking both Person A and Person B in Table 1, so the lookup of the lookup (!) is pulling the information I need so that I can make a filter.

Thanks!