Determining if a value exists in one table from another

[nwdemvolusia]

I've been reading through several posts about similar topics, but I'm just missing something here. I imagine some of this is due to some problematic base design, as I've been building it as we go but here's where I'm at - 

I have a table of club members. We are in the yearly renewal time, so, since I didn't have a way to allow submissions from our membership form to update existing records, I have redirected submissions to a new table. As it is possible folks have different emails, phones, etc, and they aren't aware of the formulated member ID I've assigned them in our main table, this seemed the easiest way to collect new and updated form submissions. 

So, my plan was to do a comparison between are All Members table and the 2024 Membership table - 

1. In All Members table, If email exists in 2024 Membership table, update a field to indicate member has renewed
2. In All Members table, if a record in 2024 Membership has the same email, compare other fields for any updates
3. If a record comes into 2024 Membership and the email is not the same, flag it to be reviewed as a potential new member or a returning member with a different email

I'm already using a number of lookups to additional tables to track things like:
Committee Memberships
Payments Received
Precinct Location

But trying to create the first simply look up that would just create a Y/N field in All Members if the email in that record matched a record in the 2024 Membership table is eluding me. 

Any guidance is appreciated 🙂 

[ScottWorld · Accepted solution]

Sorry, I don’t have the direct answers to your questions, but I wanted to try to save you a bunch of time by letting you know that you can use Fillout’s advanced forms for Airtable to update existing records in Airtable. Every member would receive their own unique link, and when they click on that link, they get taken to their own record to update. You can even accept payments through the form as well, and dozens of other things that Airtable’s native forms don’t offer. You can even display lookup fields, rollup fields, and formulas on your form! And so much more! This could likely save you a ton of time. Best of all? It’s free!

[nwdemvolusia]

Thanks for that hint, I hadn't come across that yet. I did try to generate my own form links to a specific record and then figured I'd mail merge them out to the members, but that was getting too wonky 🙂 

We manage payments through a specific portal, so that part is handled. But this looks promising, thanks! 

[ScottWorld]

You’re welcome!

[nwdemvolusia]

Okay, so I think I've got things figured out now. 

@ScottWorld I have Fillable all setup and running, this is BRILLIANT, I can't believe in all my searching of being able to update records I never had this come up. Super simple and effective, so this will be our go forward for sure. 

However, since we've already started the process, I also figured out how to accomplish mostly what I was looking for within AirTable itself, so for what it's worth for others:

We have our All Members table with our current members and our membership form is now feeding into our 2024 Membership table. So, the challenge was to connect our 2024 registrations to our current members when they use the same email we need to do the following:
1. Change the All Members table to use the email as the primary key
2. Convert the email field of the 2024 Membership table to a look up pointing to the email of the All Members table
3. Create a view for the Renewed/New Members in the All Members table
4. Add a look up field to this view that returns a value from the 2024 Membership table, we'll use email as that is always going to have a value from our registration form. 
5. Hide this field from the new view, as we don't need to see it, we're just using it to check whether the email exists in the 2024 Membership table
6. Filter the new view to where 2024 Email is not empty

We now have a view that lists only members from the All Members table that have submitted a renewal form with the same email or a record for a new registration. 

The challenge now is that the new registrations (or those that use a different email) has no other values filled in. So, I think I need to identify our key fields from the registration form and turn them all into formulas to use the data from the new form just in case anything has changed. 

Now of course, one could say that I should just use the new table as our main table, but I have setup connections to several other tables to track payments, committees, interests, and roles. So I don't want to set all that up again with the new table.

Once we reconcile everything that changes, we'll be using Fillable moving forward to update our main table, this is the first year we've had a database, so baby steps.