Check number of form submissions by email address

I want to automate this but I’m not sure on the setup, maybe this isn’t the best place to ask, but here we go…

I work in a university career center, we use Airtable to manage a student-alumni matching process. Alumni opt-in to be on the list, students can request a connection (OPEN the process), we facilitate via some automated emails, students (ideally) report on the outcome to CLOSE the process, rinse, repeat.

I would like to create a way to limit the number of OPEN requests that any one student has at a time. The unique ID for students is their email address, but we can only collect that via anonymous form because FERPA. So I don’t think we can have a linked table of students to use a count field, unless that can be dynamically created and updated behind the scenes.

So, how can I setup AT so that each time bucky.badger@wisc.edu submits the from, it will count how many existing requests with that same email address match certain conditions and then flag somehow so we pause the new request until the others are CLOSED i.e. completed? We already do some manual vetting so it’s OK if it’s not fully automated but just some way to indicate this student already has 3 OPEN connection requests, pause on this one for now.

Student form screenshot:

Hi @Michael_Kruse
You should be able to do this with automations. If you have a table of requests and a table of people, link the requests table to the people table. Then on the people table add a count field with condition on open requests only.

You can use another automation to email you if a person goes above 3 open requests.

Thanks - I think I could manage that part. Right now, we do not have a table of people (students) and I don’t want to create one unless that is the only option. I don’t want to have a linked table field visible to students on the form to select. But maybe I have to create a new linked students table just for this purpose, and then a potential solution is:

  1. Student submits the original form
  2. AT automations creates a new record in the new students table with data from the submitted form
  3. Then I can count as you suggested

Is that the best option?