Skip to main content

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.

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

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.

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?

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 🙂

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 🙂


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.



oTable 1] would look like this:




and oTable 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 oTable 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.

Reply


Terms & Conditions