Skip to main content
Solved

Time Study Data Scenario - Combine like records into 1 that can sum time spent

  • February 23, 2023
  • 6 replies
  • 87 views
K
Forum|alt.badge.img+19

Hello,

We are running a time study with our teams and we are bringing our data into Airtable from a time tracking tool.

We are running into a scenario where a designer could have multiple time stamp entries for the same task on a project. The problem is we need to be able to sum up like project task records to get the overall total time they spent doing the task, thus creating a new record with the summed time number.

Is there a way Airtable can do this? I started looking at rollups and formulas and I am not too sure where to start or if I am going down the right path. 

 

 

 

 

 

 

Thank you!

Best answer by TheTimeSavingCo

Yeah this is doable.  Try:
1. Creating a new table and called it "Summary" or something
2. Create a linked field in your original table to the "Summary" table
3. In your original table, copy the values of the "Name" field and paste them all into the linked field to the "Summary" table
4. In your "Summary" table, create a rollup field on the "Duration" field with the formula `SUM(values)`


You should end up with this:

And here's a link to the base

6 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Yeah this is doable.  Try:
1. Creating a new table and called it "Summary" or something
2. Create a linked field in your original table to the "Summary" table
3. In your original table, copy the values of the "Name" field and paste them all into the linked field to the "Summary" table
4. In your "Summary" table, create a rollup field on the "Duration" field with the formula `SUM(values)`


You should end up with this:

And here's a link to the base

Adam, Airtable Consultant - thetimesaving.company
K
K
Forum|alt.badge.img+19
  • Kiersten_Kollin
  • Author
  • Known Participant
  • February 24, 2023

Yeah this is doable.  Try:
1. Creating a new table and called it "Summary" or something
2. Create a linked field in your original table to the "Summary" table
3. In your original table, copy the values of the "Name" field and paste them all into the linked field to the "Summary" table
4. In your "Summary" table, create a rollup field on the "Duration" field with the formula `SUM(values)`


You should end up with this:

And here's a link to the base


Thank you! I will give this a try.

K
Forum|alt.badge.img+19
  • Kiersten_Kollin
  • Author
  • Known Participant
  • February 24, 2023

Yeah this is doable.  Try:
1. Creating a new table and called it "Summary" or something
2. Create a linked field in your original table to the "Summary" table
3. In your original table, copy the values of the "Name" field and paste them all into the linked field to the "Summary" table
4. In your "Summary" table, create a rollup field on the "Duration" field with the formula `SUM(values)`


You should end up with this:

And here's a link to the base


Thank you again for providing the example. 👍

I did re-create this base and got everything to work. I did add in one more field on table 1 for the project number. I am wondering if you happen to know of a way that can group the data by specific projects so the rollups are not clumping like tasks from different projects into one. 

Here is the base link.

I am not sure if is a super complex build in Airtable that may require a script to be able to separate the data out in that way.

 

TheTimeSavingCo
Forum|alt.badge.img+31

Thank you again for providing the example. 👍

I did re-create this base and got everything to work. I did add in one more field on table 1 for the project number. I am wondering if you happen to know of a way that can group the data by specific projects so the rollups are not clumping like tasks from different projects into one. 

Here is the base link.

I am not sure if is a super complex build in Airtable that may require a script to be able to separate the data out in that way.

 


Glad I could help!

The base link you provided doesn't work I'm afraid and I have requested access

Adam, Airtable Consultant - thetimesaving.company
TheTimeSavingCo
Forum|alt.badge.img+31

Thank you again for providing the example. 👍

I did re-create this base and got everything to work. I did add in one more field on table 1 for the project number. I am wondering if you happen to know of a way that can group the data by specific projects so the rollups are not clumping like tasks from different projects into one. 

Here is the base link.

I am not sure if is a super complex build in Airtable that may require a script to be able to separate the data out in that way.

 


Ah I see what you mean.  You'll need to create a formula field that combines the task and project names and use that as the linked field value like so:

 

I've updated the original base with said functionality

Adam, Airtable Consultant - thetimesaving.company
K
K
Forum|alt.badge.img+19
  • Kiersten_Kollin
  • Author
  • Known Participant
  • February 27, 2023

Ah I see what you mean.  You'll need to create a formula field that combines the task and project names and use that as the linked field value like so:

 

I've updated the original base with said functionality


Thank you! This worked for my use case.

TheTimeSavingCo
Terms & ConditionsAccessibility statement