Have employees' hours added up

[user2100]

I have a form where every month the team leader enters the hours of the employees as follows: Employee 1: "35 hours"
Employee 2: “48 hours”. up to 15 employees per team leader. Table A shows me all the hours entered by each employee in each month. All employees are listed in “Table B”. In this table I would like to add up all the hours worked so far. how do I get this done. Employee A can be listed under "Employee 1" in month 1 and under "Employee 4" in month 2. Theoretically, every employee could be entered in the 1st - 15th position. So I would have to add up all the hours of each employee, regardless of whether, for example: the employee is under “Miztarbeiter 1” or “Employee 12” and so on. you would have to tie the hours to the names or something.

[TheTimeSavingCo]

Once you link "Employee 1" and "Employee 4" to the "Employee A" record in Table B you'll be able to create a rollup field for that

[user2100]

Could you show me this with an example.

[user2100]

I'm trying to solve the whole thing through automation. I search for the respective employee and their hours and enter these values ​​in a separate table. Then I add up all the hours from all the months and then I have the total value. In case I can't manage this, I would be happy to receive ideas on how to implement it

[TheTimeSavingCo]

Yeap here

And for the hours per month you'd create a new table where each table represented a single employee for a single month and link it up:

I usually use an automation to do this bit of the linking; you can see the "Employee Month Year" is a formula field that outputs the employee's name with the month year, and I use an automation to paste that value into the linked field to the "Hours per month" table

  

[user2100]

I've now taken a screenshot of my table. the people are not arranged one below the other but rather next to each other. A data set always refers to 1 month. with several employees. I just can't manage to arrange the data correctly in a separate table so that I can add up the hours. This arrangement is because you can select the number of employees in the associated form and then e.g. 4 or 15 groups "Employee 1" to "Employee 15" are displayed. That's why I did it this way so that all associated employees can be sent with one form without, for example, having to fill out and send the form up to 15 times.

[TheTimeSavingCo · Accepted solution]

Ah thanks for the additional details!  You're going to need a new table to conslidate this data, call it "Conslidated" or something

Try creating a formula field that will concatenate all the "Employee" fields with the month year value and hours, and so the output would be "Employee A - Jan 24 - 5, Employee B - Jan 24 - 15" etc

Then have an automation trigger on the form submission and paste the value of that formula field into the linked field to the "Conslidated" table

In the "Conslidated" table, use formula fields to extract the individual details into separate columns of "Employee Name", "Month Year" and "Hours".  You'd then link stuff up to the "Hours per month" and "Employees" tables accordingly and use rollups to sum the hours

[user2100]

Can you show me this with an example? I'm not very experienced with Airtable yet.

[TheTimeSavingCo]

Ahh, apologies, building an example for this will be quite time-intensive.  I’ve enjoyed helping out so far, but for something this involved, I’d need to consider it as a part of a consultation.  If you're interested in discussing this as a paid project, let me know, and we can set something up!

Perhaps someone else has a more elegant solution and can help out too!

[user2100]

thanks for the helpful answers. I have now solved the whole thing using a script. I created an automation with a script that is executed as soon as a new entry is created in "Table A". The script transfers the data record with all employees and sorts them into different groups. This means I can summarize the working hours better.