Updating information about employee attendance in another table

[SpelaU]

Hi everyone! I'm trying to build monthly employee attendance with NFC tag. Basically when you "scan" nfc tag with your phone, it open up AT Form for submitting your entry or leave (start in the morning, start of the break, end of the break and end of work). In this form you can choose what type of entry or leave you're submitting. Name is preffiled (and linked to another table with personal information about

Of course, when submitting the form, every entry from the form is in a new record. Is it possible to combine all four records into one (for one day)? And after that, I want to calculate duration (how long was one working and how long was on break). I tried by updating the field in another table, but I don't know how to define where (to which record) should information update (so for the specific employee and for a specific date). 

I'm thinking to build specific table for every employee. 

Can anyone help me?

Thanks!

[TheTimeSavingCo · Accepted solution]

Hm, may I know why you want to have one table per employee?  Personally I'd just have a single table to deal with it instead

As you've got the date and the employee name, you could try:
1. Create a formula field that'll combine the date and the employee name
2. Create a new table called "Employee <> Day" and link it to your form submission table
3. Create an automation that'll paste the value from the formula field from step 1 into the linked field to the "Employee <> Day" table
  - This will link the 4 daily submissions to one record in the "Employee <> Day" table
4. Create lookup fields in the "Employee <> Day" table to display the times
5. Create formula fields to calculate the durations with the data from the lookup fields

[SpelaU]

Thanks! It's not necessary to have one table per employee, I was just thinking what is the best solution. Maybe one for all is better yeah.

Yeah, I was thinking similar thing. And it sounds super easy. I already did the first 2 steps, but the problem is that I just don't know how to automate the crucial step - how to bring all timestamps in one record in another base.

How automation know that it has to combine all this data into this one record? 

I did the first timestamp with automation. "Calculation" field is combined field (date and the employee name)

This is the entry in the submittion table:

And this is the entry (same record) in the employee table:

But now I'm lost on how to bring (update?) other timestamps in the same record. Can you help me with a few more instructions? I would be really grateful.

[TheTimeSavingCo]

Sure!  Check this out, and you can view the formula once you duplicate the base to your workspace
And this is how the automation's set up:

[SpelaU]

Wow, it's that simple. I was thinking way too complicated. 🙂 thank you so much!