Compare two fields and pull out matching content

[Tanguy_Auffret-]

Hi everyone !
I’m trying to build a tool to book classes between 1 student and 1 teacher.
each student fills a form to say which day of the week he / she is available.
the teacher does the same.
I then generate a record for each class and assign a student first.
once I have done that, via a lookup field, I am able to pull down the list of all the teachers that have the same day of availability.
I then link a “chosen teacher” field with one name that is in the list.
BUT
the teacher I have chosen might not be available every same day as the student.
is there a formula, or another table logic that I can use to tell me which days work for both the student and the teacher. That means comparing a list in two fields and pulling out the items in common ?
and because a base is worth a thousand word. here is a demo of what I mean :winking_face:
https://airtable.com/shrvIjXuL3EuquORO

Thank you in advance for your tips !
T

[Andy_Lin]

The short answer is it can’t be done right now, because a key part of the solution is being able to filter the list of linked records by certain criteria, which isn’t built into Airtable yet:
https://community.airtable.com/t/filter-data-for-linked-fields/193

The long answer is… you can do something truly convoluted and create a table for each day of the week and enter the teachers there according to their availability. You’d then link them (and the students) to a single “Classes” table. The upside is that you get a nice visual representation of what a week looks like for a student. The downside is that data entry is a bit weird.

Here’s a demo table, with the formulas in the field descriptions. Let me know if anything doesn’t make sense.

Airtable

Student/Teacher Demo - Airtable

Explore the "Student/Teacher Demo" base on Airtable.

I’ve added a bit of data validation in the form of the five-digit code at the end of the Student and Classes tables. Unfortunately, there’s no official data validation available.
Another weakness of this approach is that classes don’t display properly on the calendar. (But then again, you don’t really need a calendar.)

[Tanguy_Auffret-]

Hi @Andy_Lin

Thank you for your response and for the base !! I’ve started looking at it and I’m already learning stuff. :pray:
I’ll try to implement your solution to see where it leads me.
regarding the comparison between two fields, It’s disappointing that Airtable can’t handle something like that.
I’m going to push further and see if a combination of Search and nested IF formulas can do what I want.
@Katherine_Duh @W_Vann_Hall, Any ideas ?