Oct 11, 2022 12:32 AM
Here’s the scenario:
I have two tables I use for managing volunteer shifts:
Vollie Check In
A number of people forget to complete the Vollie Check In and I have to chase them up every day. If it was a paid job there’d be the incentive of getting paid only when you complete your check in, but as it’s voluntary I’m left herding cats every day.
What I want to do is either:
I haven’t been able to find anything to compare records in respective tables in the community forum.
Oct 11, 2022 07:22 AM
Hmm, this seems doable but implementing it would heavily depend on your base layout / workflow
If you could provide some screenshots of your data and how you identify Vollies who haven’t completed their check in that would be super helpful
Oct 12, 2022 03:34 AM
Thank you for offering to help!!
Here is the Vollie Check In table:
This is the Roster table:
And here’s a snippet of the Vollie Check In Form that everyone is supposed to fill in:
The only way I currently know who hasn’t completed their check in is if I don’t get an email notification alerting me to them submitting the form. This can have two potential outcomes, their either forgot to complete the Vollie Check In and I have to chase them for it (gets old real fast) or they didn’t even turn up and didn’t bother telling anyone (gets old even faster).
I don’t have time for herding cats so I need to automate as much stuff as I can. I just have no idea how I can compare records across tables.
Hope this is what you were looking for. Thanks!
Oct 12, 2022 05:56 AM
Heh roger that, and yes, the screenshots help massively
Would it be right to assume that there should be a check in record for each record in the
If so, I’m thinking that we might create an automation that triggers when a form gets submitted that would link the new record to the appropriate record in
Roster. This assumes that we have enough data from the submitted form to match it to the correct record in
Roster of course
After that, we’d create a view that would filter records based on whether the
Finish date was yesterday AND they did not have a linked check in record
And finally, we’d have an automation that would trigger whenever a record enters the view above, and its action would be to email the person assigned for that shift to ask them to update the form
Oct 13, 2022 01:56 AM
Thanks heaps Adam,
I reckon I’ve got a handle on most of that, but I gotta be doing something stupid when trying to find the respective record in the
I’ve set the automation to trigger when the form is submitted, then I have a Find Records action to find the record in the
Roster table (checking for a record with today’s date and the Vollie field matching the Vollie field on the submitted form) but it’s returning an error: Invalid filter on Vollie
Here’s how I’ve setup the automation:
Name is coming from the Vollie field:
Oct 13, 2022 02:08 AM
The Vollie field in the
Roster table is a linked record - is that my problem? (So is the Vollie field in the submitted form once the record is created in the
Vollie Check In table)
Oct 13, 2022 10:11 PM
Hmm, could I check what property you’re using for your
Vollie value? I managed to get this to work with it set to
Oct 14, 2022 07:06 AM
Airtable makes me want to :sob:
Why oh WHY does it work on ID and not Name? The user is inputting Name aren’t they? Or do I need to use ID because they’re linked records?
I realised both Vollie fields were actually linked records pointing at the
Vollie Registry table so I created a mashup of a couple of rollups and a nested IF statement to bastardise a solution - took a few hours to figure it out.
And then this…all I need to do was select ID instead of Name.
Thanks so much mate, but I’m bewildered as to why it’s gotta be ID (if you hadn’t guessed)
Oct 17, 2022 01:23 AM
Hahahah yeah, I think it’s cause they’re linked records
But hey, bright side! It’s done! You never have to think about this again (hopefully)
Oct 17, 2022 02:13 AM
Thanks for your patience mate. I ended up adding the PK formula from the Vollie Check In table as a field in the Roster table and used that as the referenced field.
Stupidly when trying to match a Date field automations won’t let you compare it against another field - just against a static date.
Anyway, it all works now. I created a dummy automation to retroactively link the check ins to past rostered shifts, created the Overdue Check Ins view - now just have to format the email and spam people until the end of time (or until they fill in their check in) :laughing:
Oct 17, 2022 06:31 AM
All good man; glad you got it all sorted!
Yeah, it’s a bit of a head scratcher sigh