Access rows of Linked Table in formula


#1

Why isn’t it easy and intuitive to access elements from other tables.

I could use a formula to show only elements of another table, that have certain attributes.

For example, when I have a table Students, and I want to generate a new table young students, I could simply type:

if(students.age<20, students, ””)

How is something like this possible right now?

Can it even be done since I really want to generate a table with elements that only fulfill certain criteria.

I think it’s really annoying that you always need to use a lookup table where the relevant information of a connected table is pulled before you can use this information in a formula. It would be much better if Airtable would adapt the writing I used above, so all rows of a table can be accessed as members of the table name from outside. tablename.row

By the way the formula field is really messy for a long, nested formula.


#2

You could create a View and Filter by Age.

I think you need that filter in other Table different that Students, but you haven’t explained it. Could you explain your base structure and what you want to achieve?


#3

Hi Elias,

Thanks for the idea with the view.

I’m trying to build a database for a volunteer private tutoring platform, to support underprivileged students.

Therefore we have to find fitting students and teachers. The students are in one table and the teachers are in another. I need to find a way, to display the teachers (that don’t have a student yet) next to the students (that don’t have a teacher yet).

We have a table with the different subjects (and all the students and teachers inside), but the filtering in the view doesn’t allow me to erase all the students and teacher that are already matched. (if teacher.student is empty would be a solution.)


#4

The easiest way to do something like that would be to define a field (call it {Lonely Teacher} in your [Teachers] table with the formula

IF(NOT({Student}),{Name})

and then roll up {Lonely Teacher}. Only teachers without matched students will be displayed. (Obviously, the inverse would work for {Lovely Student}.) Similar methods can be used to provide you with “pre-chewed” data values that can give you the effect of a filtered linked record…