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
- trigger when a volunteer record matches this condition update_most_recent_signup = 1
- search for Volunteer Signups record matching those conditions
- is_most_recent_signup_date? = 1
- volunteer_record_id = record_id found in step 1
- 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!
@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.
@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.
My assumption was that a link field was specifically required
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.
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
- trigger when a volunteer record matches this condition update_most_recent_signup = 1
- search for Volunteer Signups record matching those conditions
- is_most_recent_signup_date? = 1
- volunteer_record_id = record_id found in step 1
- 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!
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.