Comparing records from different tables in same base

[RRH_Middle_Mana]

Here’s the scenario:

I have two tables I use for managing volunteer shifts:

Roster
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:

• Create a view that filters Vollies who haven’t completed their check in that then triggers a daily automation email reminder, or
• Skip the view altogether and create an automation that compares the respective Vollie field in each table for Today() and triggers the aforementioned automation.

I haven’t been able to find anything to compare records in respective tables in the community forum.

Thanks!

[TheTimeSavingCo]

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

[RRH_Middle_Mana]

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!

[TheTimeSavingCo]

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 Roster table?

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

[RRH_Middle_Mana]

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 Roster table.

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:

¯\_(ツ)_/¯

[RRH_Middle_Mana]

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)

[TheTimeSavingCo]

Hmm, could I check what property you’re using for your Vollie value? I managed to get this to work with it set to ID

[RRH_Middle_Mana]

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)

[TheTimeSavingCo]

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)

[RRH_Middle_Mana]

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: