Expand all | Collapse all

Creating summary records by automation

  • 1.  Creating summary records by automation

    Posted 09-04-2018 23:25
    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.

  • 2.  RE: Creating summary records by automation

    Posted 09-05-2018 04:35
    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.

  • 3.  RE: Creating summary records by automation

    Posted 09-05-2018 18:12
    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.

  • 4.  RE: Creating summary records by automation

    Posted 09-05-2018 11:13
    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.

  • 5.  RE: Creating summary records by automation

    Posted 09-05-2018 13:53
    Bit the OP said they needed to do math operations on the data, which is not possible on s summary report

  • 6.  RE: Creating summary records by automation

    Posted 01-18-2019 17:29
    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.  Help please!

  • 7.  RE: Creating summary records by automation

    Posted 01-18-2019 23:11
    I think you should post a new complete question. That way the whole community will see it.

  • 8.  RE: Creating summary records by automation

    Posted 02-10-2019 02:23
    actual code would be really helpful.  this discussion isn't that useful for a beginner unfortunately.

  • 9.  RE: Creating summary records by automation

    Posted 02-10-2019 02:25
    I suggest that you post a new question describing your need.