Building a base to match individuals for 1-on-1 meetings based on career interest (2 meetings per person)
Hello! I’m new to this community and hoping someone can point me in the right direction. I need to create a base that can match college students with alumni for 1-on-1 meetings, based on career interests.
Each person will have up to 3 career interests, but meeting pairs only need to match on one of those interests.
Each student must be matched with only 2 alums
Each alum should not have more than 2 students (but can have fewer)
I created a base with 3 tables - Students, Alums, Career Interests - and linked the Student table to the Alumni table so I can see all of the matching alums for each student. I just cannot figure out how to limit the matches to just 2 for each person. Is this possible in Airtable or do I have a lot of manual work ahead of me?
Thank you for any help you can offer.
Page 1 / 1
Hey @area418,
First thing I would say is that you will probably want to have a junction table for handling matches and tracking additional detail for each match. So I would suggest you create a new table called “Matches”. -see video on Junction Tables below.
Additionally you will want to have a Count field at a student level, for counting the amount of Matches.
You will now want to build an automation that will Find Records from your Students table where Matches count is less than 2.
Then you’ll have a repeating group. Make sure to use the list of found records on the previous step as input for the repeating group. Within the repeating group you’ll want to have a Find Records action, to find records on the Alums table which (i) have any of the interests that the student record being iterated has (make sure to map values from Current Item!!) and (ii) lookup of students from Matches at alums table does not contain the student being iterated on the loop. You’ll want to limit this to output only 1 record even if multiple could be found. As next step within the repeating group you will want to create a new match record linking to the Student and the Alum.
You can run this multiple times (or two times) and you should be able to get the 2 (and only 2) matches per student!!!
FYI, completely different use case, but on min 1:40 of this other video I show how to set (and map values within) a repeating group. I think that will be helpful as well.
@Mike_AutomaticN - You are my hero! Thank you so much for this detailed explanation. I’ll work on this tomorrow and let you know if I run into any issues. I appreciate you!
Great! I’ll see if I can build a mockup for you tomorrow!
Amazing! Thank you so much.
Hi @area418,
Just wanted to share a few more thoughts when doing these sort of matching solutions.
Changes to students/alumni - If your student and alumni matches need to change in the future, either because a student graduates or an alumni no longer can get involved. You can make things easier by giving a status to your student and alumni like “active” and “inactive”. You can update your automation that @Mike_AutomaticN suggested to account for only matching “active” students and alumni. You can also have automations that will unlink students and alumni from their matches if their status changes from active to inactive. This will clear those linked record fields(lowering the matches count field) which should then trigger your automation to run through the matching algorithm.
Edge cases - If you just simply dont have enough students and/or alumni for some students to have 2 matches, you will have some students that only have 1 match or even zero matches. You will probably want to flag these records by your automation, that way you can be certain that the automation did run and tried to find matches but there was just no matches available.
Matching priority - If you have a student where all 3 of their interests are also the same interests of an alumni, it could be beneficial to match them. This might not necessarily be what happens in the matching automation from Mike since if it happens to find someone else that just had 1 interest it will match with them. This is definitely more complex since you would essentially would have to check for best possible match (3 out of 3), then if still not fully matched, try 2 out of 3, and then again 1 out of 3. This might not be something you need right now, but in the future if you realize you need it, there is definitely a way to do it.
Automation Triggers - Lastly just want to stress the importance of your automation triggers to make sure you always match students to alumni whenever there is an opportunity and the data has been entered fully. For example, depending how you enter new students and or alumni into your database, you want to trigger your matching automation only once all the interests have been entered. If these are coming from a form then that shouldn’t be an issue, but if you are entering them from an interface or in the base layer then just keep an eye for that. You don’t want your automation to run when you only had one interest selected.
Hope this helps as you work on your system. Should be a fun build.
Does this look right? If so I’ve set it up here for you to check out
And this is what the data looks like:
And here’s the automation:
The automation looks for any Alums that share any of the same Interests and have less than 2 students linked and then links them to the Student, and the conditionals there are updates for you to see what’s missing and update stuff manually if needed
The main issue with running this that you’ll have to manually click the checkbox for each student and wait for it to run as there isn’t a way to run things sequentially via an automation right now. If you checked all the boxes at once they’d all run at the same time and potentially link Alums to more than 2 students, just because at the point at which the automation ran that Alum had <2 links, does that make sense? (You could of course write a script to do things sequentially, but that’s a whole other can of worms)
@TheTimeSavingCo to get around automating the sequential automation triggering without a script, you could have an automation do this for you utilizing a recursive approach. You start by manually checking one of the checkboxes, your automation runs, once that record has the checkbox checked and the match status is not empty, your recursive automation would trigger which will find for a record that doesn’t have a checkbox and also has an empty match status. You should have the find block only return 1 record, then you could just update that record to have the checkbox checked, and that will trigger your matching automation. This would again trigger another recursive automation and it would continue until there isn’t any more records without a matching status. If you have hundreds or thousands of records this could come in handy.
Thank you all so much for your help. I got pulled into another project today, but will prioritize this next week and let you know how it goes. You are all fabulous! Thank you so much.
@airvues Oooh yeah, that’s neat! I’m definitely going to try this next time, thanks for that!
