Attendance tracking at work

[Kevin_Moreau]

Hello everyone,

I found a way to get a nice view for my attendance tracking for my workers. However the process to enter the data is tedious. In the end, I want to see a view groupe by dates, then by attended / absent, and then by type of workers (foreman, worker, project manager, sub contractor etc.) Here is the view I came up with:

However, the way I came up with this view is to add a new field for every worker on a specific day and this is a lot of work if I have 100+ workers on site.

Is there a way to make this easier? I have a second table where I can toggle a worker to be either active or layed-off. Is it possible to automate a daily copy / paste of the active workers and then I can just go through the list and specify if this worker is present on site on that day or not?

I tried to use one date with a link to multiple records, however there is not much I can do when it comes to grouping. See what I mean here:

Any tips on how I can optimise my current way of getting that nice view would be appreciated.

Thanks,

[Kevin_Moreau]

If there isn’t an easy way to do this, is it possible to do the following:

1. when I create a new date field (let’s say today January 7th)
   then automatically do:
2. Pull from another table using the “link from another table” all the active workers and paste each of them to their own field for the same date.

Thanks,

[Databaser]

Hi @Kevin_Moreau

If you have a paid Airtable version, you could auto create the different lines in your first table, based on the present day and the active/inactive status from your second table. Eg, when, on the second table, the date you fill in is equal to “today()” and the status is “active”, then create a record in table 1 with today’s date, name of the worker and his function.

But it all starts with a paid account imo.

[Kevin_Moreau]

Hi @Databaser, thank you for the reply.

I do have a paid account but have trouble understanding how the line will “auto create”. Could you elaborate on how this can be done?

I am currently using only one table with two different views.

[Databaser]

Well, it could be something in the lines of…

Table 1 > fields:

• name worker (single line text)
• type (single select field): foreman, worker, …
• status (singe select field): attended, absent
• date (date field) > to be filled in manually for those who have worked that day

Table 2 > fields:

• date (single line text or maybe date field if that works with the automation, not sure)
• name worker (linked field to table 1)
• status (single line text)
• type (single line text)

Create an automation

• trigger: table 1 > “when a record matches conditions” > when “date” is today
• action: table 2 > “create record” > choose fields and input
  . “date” = “date” from table 1
  . “name worker” = “name worker” from table 1
  . “status” = “status” from table 1
  . “type” = “type” from table 1

The manually work in this solution is to add the dates and statuses to table 1. After that the automation should create the records in table 2. By using “single line text” fields and not “linked records”, you will have a log of previous days that won’t change anymore.

See if that works? Didn’t test it, so there could be some flaws in this.

Edit: since the “name” field is a linked record, you will be able to “rollup” data from table 2 for each worker (eg sum of # of worked days)

[Kevin_Moreau]

Thank you for taking the time to help me, this is not perfect but it is getting me closer to a solution.

1. This method will copy the same date from table 1 to table 2.
   But what I want to do is to choose the date myself, then pull the workers that are “active” in table 1 and push the data in table 2. For exemple

Table 1
Name - Status
Kevin - Active
Rafael - Active
Marc - Inactive

Table 2
Date - Name - Attendance
Jan.9 - Kevin - Present
Jan.9 - Rafael - Present

Today is January 9, but I want to prepare the list of attendees for Monday January 11th

I want to trigger the action that will take Kevin and Rafael (active workers from table 1, paste in in table 2 with the date January 11th

Then I can specify if those workers are present or absent during that day:

Table 2
Date - Name - Attendance
Jan.9 - Kevin - Present
Jan.9 - Rafael - Present
Jan.11 - Kevin -
Jan.11 - Rafael -

I will try to use automation like you proposed, maybe I can get it to do what I want with some practice.

If I have trouble figuring it out I will write back here.

Thanks again

[Databaser]

Hmm, another idea to add to the exercise :slightly_smiling_face:

In table 1, create a formula field (eg “today”) with TODAY() (gives the current date) and another formula field (eg “check today”) with IF(AND(Today=TODAY(),Status="active"),"check",BLANK()) (gives a daily “check” when worker is active) .

Create an automation where “when a record matches conditions” > “when ‘check today’ contains ‘check’”, and the action is “create record” > in table 2, with the primary date field = “today” field of table 1 and the “worker” field (linked field with table 1) = “name” field of table 1.

That way, you should get a daily list in table 2, of workers that are active on that day.

To do:

• exclude non working days
• figure out how to create those records in advance

Just an idea :man_shrugging: