Skip to main content

Hello, I help run a nonprofit that has a mentoring program where both mentors and mentees apply and then we make matches between them, and this is the first year we’re using Airtable to help power this.

I have two tables within the same base: one for mentors, who have filled out a form and indicated topics that they are interested in mentoring, and then another for mentees, who filled out a different form and indicated topics they would like to be mentored on. Both “topics” fields have the same list that fills in a multiselect field.

I have a junction table (“Match”) setup to be able to link mentor+mentee, but I’m running into a lot of difficulties automating the process.

Ideally, here’s what I’d like:

  1. An automation that goes through every entry in the Mentor list, and then for each Mentor, adds every potential Mentee match to the junction table if any topic matches, and
  2. A field for each match in the junction table that tells us how many topics match, so for instance we can manually review the list and see which potential Mentor/Mentee matches have the most topics in common

I thought this would be a simple task when I pitched my org on it but I’m running into a lot of difficulties. For instance the multiselect fields are not acting like arrays that I can use formulas to compare. I’m also having trouble with the initial automation, since I can get a mentor linked to multiple mentees at once but can’t do them all as separate entries in the junction table.

Any pointers would be appreciated!

Hi there!

I like the table structure you have set up for your base, and the Match table is key to handle the relationships between Mentors and Mentees.

And I agree: an automation has to run to create the matches for new Mentors/Mentees. I would just change a couple of things to get it working:

  1. The comparisson between Mentor and Mentee topics could be done with a simple Scritp block within the automation, because it is true that a simple formula cannot get it done, unless we determine a fixed amount of topics and hard-code them into the formula. But this would require you to update the formula, going forward, every time you add a new possible topic.
  2. And yes, a Match record should be created for every combination of Mentor to Mentee, containing only one Mentor and Mentee. This can be done by using the Loop (Repeating) functionality in the Automation.

If you have additional issues please do share.

And additionally the amount of Mentors and Mentees has to be considered becuase if you run every Mentee against every Mentor, this basically creates a huge table, that could cause you to reach your Airtable record limits. Plus the automation Find Records block can only return 1000 records, so if you have more than 1000 Mentors or Mentees, the Automation will have to be re-thinked (transition to use more scripting blocks instead of traditional blocks).

Regarding the total amount of blocks there are optimizations that can be applied:

  1. Only create Matches for “Available” Mentors and Mentees. This can be implemented with filters in the Find Records automation block.
  2. Delete old Matches records for Mentees and Mentors that become Unavailable. This will require another automation.

Additionally the automation that creates the Matches records should run both when new Mentors are created, and when new Mentees are created. Due to how Airtable triggers work, this would require you to have 2 automations (1 for each triggering case) that then create the multiple Match records.

Its a nice use case you have here, hope you can get Airtable running to help the ONG’s impact!

Best regards,

Matt Nixon

 

Hey there, thanks for the thoughts. 

 

 

  1. The comparisson between Mentor and Mentee topics could be done with a simple Scritp block within the automation, because it is true that a simple formula cannot get it done, unless we determine a fixed amount of topics and hard-code them into the formula. But this would require you to update the formula, going forward, every time you add a new possible topic.

 

Hard-coding is not a big deal. The matching happens once a year, and there is a fixed amount of topics that both mentors and mentees select from the same list. I am running into issues even there with hard-coding because even though they are multi-select fields, they are not operating like arrays, and so every time I try to reference one specific topic it outputs the entire list of what they selected.

  1. And yes, a Match record should be created for every combination of Mentor to Mentee, containing only one Mentor and Mentee. This can be done by using the Loop (Repeating) functionality in the Automation.

 

I had this setup but can’t figure out how to get it to make each match its own row. Here was the automation I setup:

 

The automation would go through every entry in the Mentor table, find all matches in the Mentee table, and then attach all possible Mentees to one entry in the match table. So it would output these matches:

  • Mentor A + Mentee A Mentee B Mentee D
  • Mentor B + Mentee A Mentee D Mentee H

When I want the results to be:

  • Mentor A + Mentee A
  • Mentor A + Mentee B
  • Mentor A + Mentee D
  • Mentor B + Mentee A
  • Mentor B + Mentee D
  • Mentor B + Mentee H

With that list of results we could then compare the best matches (especially if we’re able to write a formula to show us how many matches each one has, and go down the list of mentors.)

 

Appreciate the help,

Dave

 

Hi,
You can have less beautiful but more functional base and easily achieve your goal, if you create the table of Topics and use links to it instead of multiselect.In my example I just duplicated multiselect and turned into linked field
Table of Topics will have links to both fields, so you can add Rollup of Mentors in the table of Mentees 
in a following way: (ignore topic values, I just took anything existing in my test base)
 


Rollup formula 

Name&'-'&SUBSTITUTE(ARRAYJOIN(values,', '), ', ' , ', '&Name&'-')


But the main advantage that if you create lookup of this rollup field in ‘Topics’ and copy it 
to a new linked field (to a new table)
 

 

you will get junction table of pairs with a number of common topics (and their list)


Most of this config will be up to date, according to current Mentees and Mentors table
To refresh, you just need:
Clear data of new table(removes all records)
 

Copy lookup to Link field in Topics table:  select field, ctrl+C, Ctrl+V
 

 

Hi, please disregard plevious comment, as I just notices a mistake starting from lookup, but the forum does not allow edit after some time. Anyway, I don’t think you consider removal of multi-select.

Reply


Terms & Conditions