Welcome to the Airtable Community!
I’m not completely sure how you’ve structured your base. I’m guessing that the employees are completing the form and that a records are being created when the employee submits the form. So your problem is, you need to count the number of records that did NOT get created. Little like that old joke used so often these days by people testing their video conference connections: “If you can’t hear me, please raise your hand!” :slightly_smiling_face:
.
Create an Employees table
So you’re right: you need a complete list of employees against which you can do a count. Start by creating a separate, linked Employees table. Make sure you have 1 record in there for every employee. Link records in that table to the Timesheets table on the employee name.
Switch back to Timesheets. The Employee field in Timesheets is a link-to-record field, which can’t be the table’s primary (name) field, so, to populate that primary field, make it a formula field that combines Employee name with the DateSubmitted, using a formula like this:
DATETIME_FORMAT( DateSubmitted, 'YYYY-MM-DD' ) & " " & Employee_
Then back to Employees once more. You’ve already got the linked field that shows Timesheets (that was created automatically when you created the link-to-rec field in Timesheets). What you need to add to the Employees table is a Rollup field that will show you the max date of the employee’s linked forms. Do it like this:
The key thing there is that “aggregation formula” at the end: MAX( values ).
.
Auditing by reference to most recent date
Now you’re now just about home. To see who didn’t submit a form today, go to Employees and edit the Filters like this:
As you can see there, I have in my test base just 1 employee (Carol) whose most recent submission was before 4/7/2020 (which is today as I’m writing this).
.
Auditing by any date
I’m assuming you’ll be checking daily or weekly as appropriate for your schedule. But what if you want to jump back a month or two and see who did not submit forms on some specific date, say, last month? That’s a little more complicated, but also doable. One way to do it would be to create a rollup field in Employees called DatesSubmitted. Have it rollup the DateSubmitted field in Timesheets and aggregate using this formula:
ARRAYJOIN(values)
The output will be a little funky: the values will look like this: “2020-03-31T00:00:00.000Z”. But that’s okay. Now if you want to know who didn’t submit a timesheet on 3/24/2020, you’d edit the filter to look like this:
Be sure to type the date in YYYY-MM-DD format for this filter. As you can see, in my tiny test data set, 2 employees didn’t submit timesheets on that date.
.
Make sense? Will that work?
William
