Help

Consolidated Count from Another Table by Date & 'Shift' Field

Topic Labels: Formulas
1110 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Santiago_Vera
4 - Data Explorer
4 - Data Explorer

Hello!

This is similar to another post: Consolidated Count from another table by date field, however I am trying to group by 2 fields.

Table 1 has 3 fields (Date, Shift, Count)
Table 2 has 3 Fields (Date, Shift, Total Count)

Table 1 has 6 records:
1/1/2020, Morning, 2
1/1/2020, Morning, 1
1/1/2020, Midday, 1
1/5/2020, Midday, 3
1/5/2020, Afternoon, 4
1/5/2020, Afternoon, 3

I want table 2 to sum up all of the counts by date and shift. So I would want table 2 to show:
1/1/2020, Morning, 3
1/1/2020, Midday, 1
1/5/2020, Midday, 3
1/5/2020, Afternoon, 7

How can I do this?

I have Table 1 grouped by Date&Shift, but I was hoping to show this in a cleaner way in Table 2. Similar to Adding it all up | Airscript but grouping by 2 objects instead of just 1.

6 Replies 6

Do you need to put the totals in another table, or can you just use the summary bar?

It would be best if I could pull the totals into another table, just not sure how to make that happen.

You could link your records across tables to use rollup fields to summarize your totals.

Otherwise, you would need to use an automation or a custom JavaScript or an external automation tool like Integromat to do that.

Question: do you want [Table 2] to have one record per date with a count field for each type of shift (my recommendation), or one record per date/shift pair?

Santiago_Vera
4 - Data Explorer
4 - Data Explorer

Oh, I hadn’t even thought about having one record per date with a count field for each shift. I was thinking of having one record per date per shift. Either should work for what I need :slightly_smiling_face:

In that case your absolute simplest solution would be to copy the {Date} field’s value into a Link to another record-type field and use Rollups to total up each shift.

[Table 1] would look like this:
image

and [Table 2] would look like this:
image

You could automate the copying of the {Date} => Link field by using a simple Automation: trigger when the {date} field in [Table 1] is updated, add an Update Record step that inserts the {Date} field’s value into the Link field. No script or external tool required.