Count and divide the hours for the past day

[2d78]

Hi, please help to understand whether it is possible to implement the following:
On the screenshot, there is a table in which data about the status change and its time are entered via a webhook. I calculate the duration between events. Now I need to make a report for the previous day, in which I indicate how many hours the status was online and how many hours the status was offline.

@kuovonne can you help?

[kuovonne]

Before I answer your question, do you mind letting me know why you tagged me? It seems that you only just joined the community.

Yes, it is possible. How to go about it depends on how you want to make the report. Do you want a daily email? Do you want to generate a PDF? Do you want to simply look at the screen?

If you just want to look at the screen and see numbers, create a filtered view that shows only yesterday's records, and group by {Status}. Set the summary bars to add up the durations to see the totals.

If you want an automated email, and there will be less than 100 status records of each status, you could use scheduled daily automation. Add a formula field that converts the duration to a decimal number of hours (versus formatting the formula result as a duration). In the automation, add a "Find Records" action to find the "offline" records for the previous day and then use one of my automation helper scripts to sum the decimal duration. Use another "Find Records" action for the "online" records. Then use the output from both scripts in the body of your email.

If you want a PDF report, you need a "Dates" table with one record for every date. Link each record to its proper date and use conditional rollups to calculate the total durations. Then you need to layer how to create the pdf itself. There is Page Designer, but it is not automatic. There are a variety of third party document generation services, some of which can be automated, but that is a huge topic and this community has many posts about how to generate pdf files.

[2d78]

Before I answer your question, do you mind letting me know why you tagged me? It seems that you only just joined the community.

---

I just registered, but before writing here I tried to solve the problem myself. I read a lot of threads here and saw that you answer a lot about functions.

Data comes via webhook to Integromat (make). Then, through the Integromat scripts, the data is entered into the Airtable table as a database.
I then send the same data through the Integromat. Therefore, I need information in such a form that it is created automatically every day about the previous day and I can send it further through the Integromat.

[kuovonne]

Since you want the data accessible to Integromat, you should go with a "Dates" table. When Integromat creates the new record, have Integromat Upsert the date record, and link the new record to the date record.  Use conditional rollups in the date table to calculate the totals. Finally, have a daily Integromat scenario that finds the date record for the previous date, gets the total durations, and converts the total durations to something more human readable.

[kuovonne]

Another option would be to not have a [Dates] table, and to have Integromat find yesterday's records and use an aggregator to sum the durations. However, that would probably take more operations overall and be a more complex scenario.

[2d78]

I'm new to this and tried to do as you wrote in this version, but I can't figure out how to do it. I made such a table.
I cannot count the number of hours in the online status and the number of hours in the offline status.
Moreover, there are two more unusual scenarios:
1. when the event starts on one day and ends on another.
2. when there are no events during the day, for example, the charter was online all day.
Can you please help?

[kuovonne]

The new format for these forums makes it very hard for me to tell when there is new information on a topic.

Your screen shot shows only a single table, but does not show the title of the table. I am going to call it the [Statuses] table. Notice in this table that your {Date} field has the same value for multiple records. I suggest that you create a new [Dates] table where each date appears only one time. Join the two tables with a linked record field. The two tables will have a one-to-many relationship. One record in the [Dates] field can be linked to many records in the [Statuses] table.

You will need to create records in the new [Dates] table for the existing [Statuses] records and link them.

Also create two conditional rollup fields in the [Dates] table. Both rollups should be based off the linked record field and have the formula SUM(values). One rollup should limited to records where the {status} is "offline" and the other rollup should limit records to where the {status} is "online".

You can also automate linking new records in the [Statuses] table to the [Dates] table. You can do this either in the original Integromat scenario or with a native Airtable automation. It is a little complicated, but it involves first searching/finding a record in the [Dates] table with a matching date. If a matching date record is found, link to it. If no matching date record is found, create a new date record and link it.

Finally, for your daily report, have Integromat search for yesterday's record in the [Dates] table, and then get the values from the rollup fields.