Help

Comparing records from different tables in same base

150 10
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

10 Replies 10

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

Thank you for offering to help!!

Here is the Vollie Check In table:
Vollie Check In

This is the Roster table:
Roster

And here’s a snippet of the Vollie Check In Form that everyone is supposed to fill in:
Vollie Check In Form

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!

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

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
Automation error

Here’s how I’ve setup the automation:
Automation setup

Name is coming from the Vollie field:
Vollie field

¯\_(ツ)_/¯