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

[Santiago_Vera]

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.

[ScottWorld]

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

[Santiago_Vera]

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

[ScottWorld]

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.

[Kamille_Parks]

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]

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:

[Kamille_Parks]

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:

and [Table 2] would look like this:

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.