Help

Rollup Based on Unique Column

Topic Labels: Base design
717 2
cancel
Showing results for 
Search instead for 
Did you mean: 
sal
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello, 

I'm looking to make a table in Airtable that can sum a column based on the unique combination of other columns. Below is a sample of how I would do this in Excel using a pivot table. 

sal_0-1681947362097.png

 

The use-case is we are trying to create a labor tracking system for our manufacturing floor. Team members come in and work on different assemblies, parts and operations. Generally, there can be two records for the same combination of columns (a real example would be someone spends 4 hours on Thursday working an assy/part/operation, they submit their time tracking form for the day and then return on Friday and spends another 2 hours on that assy/part/operation to complete the job). 

How can I achieve this? I thought I could do it with the Uniquearray function but unforunately cannot seem to get the solution working properly. Thanks in advance! 

 

2 Replies 2
Dominic
6 - Interface Innovator
6 - Interface Innovator

Hi Sal,

One way I think you can achieve this would be to setup your base so that you have tables for Assays, Parts and Operations. You can then build a junction table with linked record fields to each of these tables. Once you have linked records you can start to add rollup fields. 

Hope that helps.

 

Screenshot 2023-04-20 at 09.37.58.png

Screenshot 2023-04-20 at 09.37.49.png

Dominic
6 - Interface Innovator
6 - Interface Innovator

Further to my last message, if you had a table called Time Tracking, and the Junction table was built out with a unique record for all the permutations of the factors you are tracking (Assay, Parts Operations), you can then link your Time Tracking table with the Junction table so that the operator can select the unique record that consists of the required combination of Assay, Part and Operation. There are scripts available to assist with automating the maintenance of junction tables.