Automation Repeated and summary table

[alhazen]

hi guys

i need your help, I have two table which are "Schedule" and "Calculate". "Schedule" is raw data table with column [year], [month], [department], [planned_date], [actual_date] and [adherence_to_schedule].

So When user do a task after [planned_date], the [adherence_to_schedule] column is filled to 0%. And if user do a task on time or before planned date, [adherence_to_schedule] column is filled to 100%.

"Calculate" table is a summary table used to count how much each department has 0% and 100% in each month. "Calculate" has some column which are [year], [month], [department], [on time], [overdue]. I am using spreadsheet formula (countifs) for column [on time] and [overdue].

is it possible to make automation for repeated data entry into "calculate" table filled every 1st of the new month?

Thank you very much for your help.

[Joseph_Seddik · Accepted solution]

1. Construct a data action, call it calculationUpdate with the following settings:
   • For a record of this table: Calculate
   • Do this: Data: set the values of some values in this row
   • Set these columns:
     
     • Ontime: 
          
       COUNT( FILTER(Schedule, AND(
          [Year] = [_ThisRow].[Year],
          [Month] = [_ThisRow].[Month],
          [user_department] = [_ThisRow].[user_department],
          [actual_date] <= [planned_date]
       )))
           
     • Overdue: the same as above but with: 
           
          [actual_date] > [planned_date]
            
2. Run your bot with an action of type Data: execute an action on set of rows, with the following settings:
   • Referenced Table: Calculate
   • Referenced Rows: Calculate[id]
   • Referenced Action: calculationUpdate 
      

[Joseph_Seddik · Accepted solution]

1. Construct a data action, call it calculationUpdate with the following settings:
   • For a record of this table: Calculate
   • Do this: Data: set the values of some values in this row
   • Set these columns:
     
     • Ontime: 
          
       COUNT( FILTER(Schedule, AND(
          [Year] = [_ThisRow].[Year],
          [Month] = [_ThisRow].[Month],
          [user_department] = [_ThisRow].[user_department],
          [actual_date] <= [planned_date]
       )))
           
     • Overdue: the same as above but with: 
           
          [actual_date] > [planned_date]
            
2. Run your bot with an action of type Data: execute an action on set of rows, with the following settings:
   • Referenced Table: Calculate
   • Referenced Rows: Calculate[id]
   • Referenced Action: calculationUpdate 
      

[alhazen]

Hey Joseph,

Thank you for the response.

do I need to enter [year], [month], and [user_department] in "Calculate"  table manually first?

[Joseph_Seddik · Accepted solution]

You can configure a Form Saved action for your Schedule table, that adds a new row with the year, month and user_department to Calculate table, only if this row doesn't already exist. For this, the condition to run the action would be:

COUNT( FILTER(Schedule, AND(
   [Year] = [_ThisRow].[Year],
   [Month] = [_ThisRow].[Month],
   [user_department] = [_ThisRow].[user_department],
))) = 0

[alhazen]

Thank you Joseph