I've looked at a few posts that deal with updating an existing record based on a form submission, but haven't found anything that addresses my particular need.
I have a base to keep track of volunteers. It includes general contact info, which groups they are a part of, how they came to the organization, etc. I want to also use this base to keep track of which events they RSVP to. They RSVP to events via airtable form. I want to be able to update their existing record to add the event to the "Events" column (multi-select field).
I've found ways to do this that involve the person filling out the form looking up their record (e.g., https://community.airtable.com/t5/show-tell/updating-an-existing-record-via-airtable-form/td-p/132681). But my volunteer list needs to remain private—I can't have it so that anyone filling out the form can see the names of other volunteers. Instead, I want to have users fill out their email address each time they RSVP, and then use that as a unique ID to look up if they already have a record, and to update the record. If they don't already have a record, then I want to create a new record to start tracking the new volunteer.
So the path would be something like this:
User fills out form
--> IF email address matches email address in existing record THEN "Events" column is updated
--> IF email address doesn't email address in existing record THEN create new record
Any help would be very much appreciated! I've tried several automations but haven't been able to figure out how to look up existing records in this way. I'm not familiar with scripting but if this would be possible through scripting I'm down to figure it out.