Creating summary records by automation

Question

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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Name&nbsp;&nbsp; Job&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Hours&nbsp;&nbsp; Earned Hours&nbsp;&nbsp;&nbsp;&nbsp; WE-NameAug-19&nbsp;&nbsp; Jack&nbsp;&nbsp;&nbsp;&nbsp; Job-100 &nbsp; 2 hrs&nbsp;&nbsp;&nbsp;&nbsp; 1 hrs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2018-08-25-JackAug-19&nbsp;&nbsp; Jack&nbsp;&nbsp;&nbsp;&nbsp; Job-200 &nbsp; 2 hrs&nbsp;&nbsp;&nbsp;&nbsp; 3 hrs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2018-08-25-JackAug-19&nbsp;&nbsp; Jack&nbsp;&nbsp;&nbsp;&nbsp; Job-300 &nbsp; 4 hrs&nbsp;&nbsp;&nbsp;&nbsp; 5 hrs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2018-08-25-JackAug-20 &nbsp; Jack&nbsp;&nbsp;&nbsp;&nbsp; Job-100 &nbsp; 4 hrs&nbsp;&nbsp;&nbsp;&nbsp; 4 hrs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2018-08-25-JackAug-21 &nbsp; Jack&nbsp;&nbsp;&nbsp;&nbsp; Job-100 &nbsp; 3 hrs&nbsp;&nbsp;&nbsp;&nbsp; 3 hrs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2018-08-25-JackAug-27&nbsp;&nbsp; Jack&nbsp;&nbsp;&nbsp;&nbsp; Job-200&nbsp;&nbsp; 3 hrs&nbsp;&nbsp;&nbsp;&nbsp; 2 hrs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2018-09-01-JackAug-28 &nbsp; Jack&nbsp;&nbsp;&nbsp;&nbsp; Job-400&nbsp;&nbsp; 5 hrs&nbsp;&nbsp;&nbsp;&nbsp; 6 hrs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2018-09-01-JackI have another table called "Weekly Summary Table" and "Monthly Summary table". WE-Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Week Ending&nbsp;&nbsp;&nbsp; Name&nbsp;&nbsp; Hours&nbsp;&nbsp;&nbsp;&nbsp; Earned Hours2018-08-25-Jack &nbsp;&nbsp;&nbsp; 2018-08-25&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Jack&nbsp;&nbsp;&nbsp;&nbsp; 15 hrs&nbsp;&nbsp;&nbsp;&nbsp; 16 hrs2018-09-01-Jack&nbsp;&nbsp;&nbsp;&nbsp; 2018-09-01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Jack&nbsp;&nbsp;&nbsp;&nbsp; 8 hrs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8 hrsI 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.

quickbasecoachd · Answer

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.

suryaexpert · Answer

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.

quickbasecoachd · Answer

Surya,  Bit the OP said they needed to do math operations on the data, which is not possible on s summary report

eriksontsang · Answer

Thanks, It works like a charm.&nbsp;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.

amygosz2 · Answer

I am trying to do the same thing with the automation creating a 'bank deposit' record from numerous check records entered. However, I am unsure what the formula would be for the Formula Checkbox created. I did create that field on the 'Deposit" Table.&nbsp; Help please!

Forum Discussion

Creating summary records by automation

Related Content

Summary Reports

Maximum Summary field not working

AI Smart Builder for Creating Apps and Streamlining Pipelines

create pipeline to create new records based on current record

Add button to create record in different app