Help

Timesheet-like function for multiple submissions - How to rollup data for each matching field?

Topic Labels: Formulas
Solved
Jump to Solution
888 4
cancel
Showing results for 
Search instead for 
Did you mean: 
caitlingubert
4 - Data Explorer
4 - Data Explorer

I have a form that users submit each week to log hours. I have a table that captures each submission in a new row along with how many hours they have left in total (not per week). I have fields that capture the hours logged that was submitted, the total available hours, and how many hours are left, but I am unable to get it to add up all of the hours for a single user and then provide me with the remaining hours. 

As an example, 

Jane K. (1st week)

Hours logged this week = 4. Total available hours = 20. Remaining hours = 16.

Jane K. (2nd week)

Hours logged this week = 10. Total available hours = 20. Remaining hours = 10. 

How do I get the 'Remaining hours' total to be 6 (4 + 10 = 14 minus 20)? I would like to base it off of the first field in my table which is a concatenation of two values. 

 

Thanks!

1 Solution

Accepted Solutions

In your synced table that the 'person' field is coming from, make sure you have also added a lookup field to the field you have for 'hours logged' in the table you've shown. 

Emily_Montgomer_0-1692904922135.png

Then, you just need  add a rollup field that sums the 'hours logged' field from the table with the list of users.

Emily_Montgomer_1-1692905006358.png

Emily_Montgomer_2-1692905020968.png

 

See Solution in Thread

4 Replies 4
Emily_Montgomer
5 - Automation Enthusiast
5 - Automation Enthusiast

If you have the Users in a separate table, you can use a rollup field to accomplish this. In the table where their form responses are logged, you have the hours logged and the available hours fields. If you haven't already, add a linked record to the table where you have the users stored. In the Users table, add a lookup field that pulls the hours logged values to that table. Then, back in the first table, add a rollup field like below. After that, you can just add a final formula field that gives you their total available hours-rollup

Emily_Montgomer_0-1692836881002.png

 

Hey Emily! Thanks for the help; however, my setup is a bit different. 

My "Person" field is a lookup from a synced table. 
My "Available Hours" is also a lookup from the same synced table but a different field. 

These synced fields that I'm pulling from get updated by other teams, and I have everything pulling into one table. 

This is the way it is currently setup: 

caitlingubert_1-1692904214620.png

 

In your synced table that the 'person' field is coming from, make sure you have also added a lookup field to the field you have for 'hours logged' in the table you've shown. 

Emily_Montgomer_0-1692904922135.png

Then, you just need  add a rollup field that sums the 'hours logged' field from the table with the list of users.

Emily_Montgomer_1-1692905006358.png

Emily_Montgomer_2-1692905020968.png

 

Great, thank you! I got it to work by adding the lookup field to the other synced table as you demonstrated.