How to show only the most recent registered event in a volunteer management table

[seanwhitcomb]

I have a base to manage volunteers and volunteer events. There's a Volunteers table to store volunteer information (name, email, phone, etc.). There's an Events table with details about volunteer events. There's a table called Volunteer Signups that shows which volunteers are signed up for which events.

I use a form to register new or returning volunteers. The form fills in the details on the Volunteer Signups table. An automation then creates a new record in the Volunteers table if the volunteer's email is not in the system yet. If the email is in the system, a new Volunteers record is not created.

Returning volunteers will then have multiple events listed on the Volunteer Signups table, one for each event they have signed up for over time. I want to have a linked record in the Volunteers table that shows only the most recent event they have signed up for. Right now I'm only able to show the first event they signed up for, not any subsequent events. What do I need to change to achieve this?

[xad9119]

Hi @seanwhitcomb, 

Rollups, formulas and automations should do the trick, from what I understand from your description here is what I would do: 
Create the following fields in your Volunteers table :

• most_recent_signup: link field to the Volunteers Signups table
• max_signup_date: rollup on your signups field (the one that links one volunteer to all of his/her signups), this rollup should be set to get the MAX(values) of a Created time field in the Volunteers Signups table
• most_recent_signup_date: same thing than the previous field, but based on the most_recent_signup field
• update_most_recent_signup: a simple formula field that will serve as the trigger to your automation (max_signup_date!=most_recent_signup_date) and will either render 1 or 0
• record_id: formula field (RECORD_ID())

Create the following fields in your Volunteers Signups table :

• signed_up_date: created time field used in the above field
• volunteer_most_recent_signup_date: rollup field collecting the max value of the max_signup_date
• volunteer_record_id: rollup field (use ARRAYJOIN(ARRAYUNIQUE(values))) on the Volunteer record_id field
• is_most_recent_signup_date?: formula field checking if (signed_up_date=volunteer_most_recent_signup_date)

Set up an automation that will

1. trigger when a volunteer record matches this condition update_most_recent_signup = 1
2. search for Volunteer Signups record matching those conditions
   1. is_most_recent_signup_date? = 1
   2. volunteer_record_id =  record_id found in step 1
3. Update the record in step 1 (most_recent_signup field) using the record_id of the result in step 2

And that should work for you. 

Hope it helps!

[ScottWorld]

@seanwhitcomb All you need is a lookup field if your linked records are displaying in the proper order. You can create a lookup field and then enable the option to show only the last 1 event.

[xad9119]

My assumption was that a link field was specifically required 

[ScottWorld]

Yes, a linked record field is required to have a lookup field. If the records are linked in the right order, lookup fields now offer the ability to display the last X linked records.

[seanwhitcomb]

This is pretty close to how I have things set up, but here's the problem I have:

Jack Smith signed up for the April 2023 volunteer event. There is one record in the Volunteer Signups table for "Jack Smith - April 2023". Jack Smith then signs up for the September 2023 event. There is now a separate record for "Jack Smith - September 2023".

I want the Volunteers table to list all events Jack has signed up for, but it only lists the first one. Any subsequent entries for a returning volunteer are ignored. This is because I wanted to eliminate duplicate entries for volunteers in the Volunteers table. But I still want to link to the events for each user.

I don't know how to tell Airtable that the same Jack Smith signed up for two different events. I assume I can use the email address to link the two Volunteer Signups records together, but I don't know how to do this.