How to save daily stats in a history table?

[dchernyshev]

Hi all,

I have a simple app which tracks members of a team: some of them are billable, and some of them are not. The data is stored in Register table with the following columns:

• Employee name
• Status (billable/non-billable)
• Role
• etc.

With the team growing, I need to track the stats daily/weekly/monthly: how many billable members did I have per date, and how many non-billable. So, I have created Stats table with the following columns:

• Date
• Status (billable/non-billable)
• Count

How can I run a simple query through my Register table, which would count people grouped by their status and put the record for every day into the Stats table?

[AleksiAlkio · Accepted solution]

I would create a scheduled Bot that creates a new row and counts that number. The idea is that it creates a new record to your Stats table. It would be better to have two count columns, one for each statuses so it would only need to add one record. Then the first count column would have a formula like..
COUNT(SELECT(Register[KeyColumn],[Status]="Billable"))

[dchernyshev]

Thanks Aleksi! The idea is great, though it seems "Run a data action" can be performed only with a "Data Change" event, it's unavailable on a schedule... I'll think something up though 🙂

[AleksiAlkio]

If you don't want to do it manually, at least you can do that with the combination of scheduled Bot and Webhook. Though then it needs the Core subscription.