Employee shift attendance tracking

Hi, I’m fairly new to airtable and having a hard time setting up what I think should be a relatively simple base.

I have a staff of about 40 individuals. They only have access to the share views of two of my bases, if that is relevant (so anything they do on airtable has to go through forms).

The 40 staff are each assigned between 1 and 4 shifts a week. Each day is divided into 4 shifts of 6 hours, so we have 28 shifts per week. The number of staff members assigned to a given shift varies.

Right now I just have a base with two tables: staff names and shifts (listed as mondayshift1, mondayshift 2, etc.). Staff members fill out a form and select their shifts so that the resulting tables fill in.

What I would like is a way to track attendance and then later, set up automations (for emails) if someone misses a shift. I was thinking I would create a form for them to fill out when they clock out, with name, shift, and then one optional question (long text).

Shifts do not change from week to week. I’m not picky about whether all the staff attendance is tracked on one table or if each staff member has their own table. I just need to be able to look at airtable and see “tom, barbara, and alex did their shifts today. Harry missed his.”

Any advice? Seems like it should be simple and it’s just a matter of my lack of familiarity with the platform.

thank you!

Hmm, I take it you have a table that’s called “Shift Selection” or some such that contains all the form submissions of your staff selecting their shifts?

If so, I think I would create another table called “Clock Ins” or some such, and it would have:

  1. A linked field to Shift Selection
  2. A long text field for the optional question you mentioned
  3. A linked field to the Staff table (optional)

Your staff would then select the shift they’re clocking in for via this form

Automations could then be created to trigger if the starting time of the shift has past and the linked field to the Clock Ins table is empty