Creating summary records by automation

  • 1
  • 1
  • Question
  • Updated 2 months ago
  • In Progress
I have a table, called Daily Hour, which has all the time entry by person by day and I'd like to summarize the record by week . The Daily Hour records are like:

Date       Name   Job          Hours   Earned Hours     WE-Name
Aug-19   Jack     Job-100   2 hrs     1 hrs                   2018-08-25-Jack
Aug-19   Jack     Job-200   2 hrs     3 hrs                   2018-08-25-Jack
Aug-19   Jack     Job-300   4 hrs     5 hrs                   2018-08-25-Jack
Aug-20   Jack     Job-100   4 hrs     4 hrs                   2018-08-25-Jack
Aug-21   Jack     Job-100   3 hrs     3 hrs                   2018-08-25-Jack
Aug-27   Jack     Job-200   3 hrs     2 hrs                   2018-09-01-Jack
Aug-28   Jack     Job-400   5 hrs     6 hrs                   2018-09-01-Jack

I have another table called "Weekly Summary Table" and "Monthly Summary table".
WE-Name               Week Ending    Name   Hours     Earned Hours
2018-08-25-Jack     2018-08-25       Jack     15 hrs     16 hrs
2018-09-01-Jack     2018-09-01       Jack     8 hrs        8 hrs

I tried setting up an automation for I'd like whenever we add/modify a record in the Daily Hour table, it'll automatically create a record in the Weekly Summary Table and link "WE-Name" field between the two table. However, the automation would turn off by itself because I have many duplicated WE-Name in the Daily Hour to be created into Weekly Summary.

I'm not sure what's the most efficient way to create summary table via relationship automatically.

Note that I've tried using "summary report" within the daily report and it won't give me what I want especially when i want to calculate based on the performance of total earned hours divided by actual hours.

Photo of Erikson Tsang

Erikson Tsang

  • 100 Points 100 badge 2x thumb
  • non-automated

Posted 2 months ago

  • 1
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,316 Points 50k badge 2x thumb
You are on the right track.

I suggest creating a formula checkbox field called [weekly summary exists?] on the weekly summary table.

Look that field up down to the detail record.

Then set your automation to only fire when that field is not checked.

I’m assuming that you data us entered manually and not mass imported.
Photo of Erikson Tsang

Erikson Tsang

  • 100 Points 100 badge 2x thumb
Thanks, It works like a charm. 

Instead of a checkbox, I've added a total count of record in the summary (parent) table and then bring it back down into the child. If there's none created in the parent table, that count field brought down to the child would equal to blank. Therefore, I've used the same automation with a criteria of the count field equals blank.
(Edited)
Just to take things into perspective, you could also create a summary report based on the Daily Hour table and group by the date (by week, not equal values), without the necessity to create another table and automations.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,316 Points 50k badge 2x thumb
Surya,
Bit the OP said they needed to do math operations on the data, which is not possible on s summary report