Let's say there's a calendar. Typically people enter dates there, which are rather long (from one to 10 hours, typically) but do not extend over more than a day. Every event has an additional field (custom column, multiple choice without possibility to enter free text) holding a selection of rooms occupied.
I already mastered reading and filtering calendar entries by different means (Flow filtering, ODATA filtering). Now I want to build a flow which evaluates the room occupancy. You may guess the purpose.
The output should be: another calendar with an entry for every hour of the day and for every room. This entries will have the column for the rooms again and an additional numeric column showing the number of people in that room at the same time. Entries with 0 people can be omitted. My goal is to visualise hourly the occupancy of all rooms respective to some limits.
I want my flow to evaluate that at least every day for the upcoming 60 days. My typical approach would be:
- initialise array for one day (24 elements = 0)
- for each of the upcoming 60 days
- for each of the rooms load all events (getItems filtered by day and room)
- reset the array with 24 elements = 0
- for each date from GetItems
- iterate over every hour from StartEvent hour to EndEvent hour
- add 1 to the element representing the hour in the array
- write the content of the array wherever I need it (other calender, excel etc.)
We see 4 nested loops, and I already have used ODATA-Filtering to reduce redundant test operations as good as possible. However, this method still involves a lot of loop entries and the number is dependend on the length of meetings in the calendar. I.e. A date needs for each hour of duration
- a loop entry
- a test
- a increase operation
If I want to improve granularity by testing for every 30 minutes, the number of actions will double.
Is there a better algorithm I can use or did I miss some nifty tricks within Sharepoint, ODATA or operations on JSON?
