Building a Time-Based Calendar in Power Apps
By Craig White
In the past two articles, we’ve built a very simple menu component and a weekly calendar. For every date selected from the calendar, the wife now needs the app to show scheduled appointments for that day and any subsequent available time. Should be easy, right?
We were using Microsoft Teams as inspiration again; I thought, or hoped that the wife would want something like this:
I’m sitting there, literally building this view in my head.
“Flexible height gallery with dates, bring the appointments table in and put in a nested gallery, filter nested gallery where date equals ThisItem.Date. Easy“.
I even mocked it up to prepare for this article, it took me less than 5 minutes to POC it. We’re not talking Kristine Kolodziejski-level design at this stage, but purely for a functional model:
But no, the wife doesn’t want that, she wants the more traditional view with times down the side:
A bit more of a head scratcher; plotting the appointments shouldn’t be too hard, but the size of the appointment blocks will be dictated by duration. Also, how do you even ‘do’ a time-based calendar? Whilst considering that, I was pondering the other requirements:
> Needs to be in 15 minute time blocks from 08:00-20:00; these 15 minute blocks will allow for buffer time between one treatment and the next, in case one overruns. 08:00-20:00 is the range for opening hours.
> Match times to existing appointments; if a 15 minute block is taken, block it out visually to indicate something is scheduled.
> Styling for an appointment; with galleries, you’ll get a matching row per time block, but we’ll only want the client/treatment mentioned once per block.
> I don’t want nested galleries or lookups to other data sources; nested galleries aren’t great for app performance, so I avoid them unless absolutely necessary. Even then, I’ll try to work around it. Endless lookups from galleries to other data sources can also be a performance drain. So I’ll need a way to build a decent table/collection to show results per day, so that everything can be visualised in a single gallery.
I didn’t find anything amazingly useful via Google for building a time-based calendar, if that’s even the right thing to call it? Of course, now I’ve said that someone is going to drop a blog article in the comments. So the rest of this article details the logic & steps I followed; I’m sure there’s a better way, if you know of one then please reach out.
The brainstorm
If you saw my recent post about the Sequence function, you’ll know there’s a couple of time-based examples in there. This was my base point to start with. The sequence number took a few tweaks whilst I worked out the number of data points between 08:00-20:00, at 15 minute intervals:
ForAll(
Sequence(49),
Time(8, 15 * (Value - 1), 0)
)
Ok, so that works as a starting point for our logic:
However, I’d also like a column in there for the integer value of the relevant time.
I’ve really enjoyed working with integers for matching & filtering data. This goes back to my SQL days; I found significant query performance when matching with integer values instead of text, dates or times. I’ve stuck to that where possible, since.
We have a few options to create integer values relating to our time blocks. In a DateDiff function the unit of time can be represented as days, hours, milliseconds, minutes, months or quarters. I opted for minutes, and we’d expect this value to increase as the 15 minute blocks progressed through the day.
We can calculate this value by taking a time value away from 12:00 AM. I could in theory start this at 08:00 AM to reflect the wife’s starting hours every day, but she might change that in future. So long as the time value is before our daily starting time, we’re golden.
We can test this logic by adding a label to the gallery and adding the following Power Fx:
DateDiff(
TimeValue("12:00 AM"),
ThisItem.Value,
TimeUnit.Minutes
)
Getting there. But these values aren’t in a collection yet, they’re calculated on the fly. The logic is there though so good progress so far:
But, I want a row number as well. We need a way to stop duplicating information for a single appointment, as will be the default gallery behaviour:
I figured if I can compare the current row to the previous row, or the current row to the next row, I can manipulate the border radius properties of the button and make info only appear on the top line:
This is leaning on my SQL experience again; the LAG & LEAD functions were introduced as of SQL Server 2012. I used those a lot in my NHS days for various patient or treatment comparison logic. We could do similar here but would need an incremental row number to make the Power Fx easier.
The build - part 1
Right, so we need the time, the DateDiff output and a row number. I covered row numbers in the same Sequence function article I linked earlier, so I knew I had that method in the bank.
After trying a few different ways, I settled on the following logic:
> Using a With statement to define a temporary table (again, another SQL-ism).
> Use this temporary table to store my time-based entries at 15 minute increments, plus add a column for the relevant integer value from the DateDiff.
> Loop through every record in the temporary table to make a collection, adding a row number at the same time.
Here is the Power Fx in full, building a collection called colCalendarDraft:
// Build colCalendarDraft - structure for times by 15 minutes and row numbers
With(
{
tvBlankSchedule: AddColumns(
// Initial 15 minute Time output
ForAll(
Sequence(49),Time(8, 15 * (Value - 1), 0)),
// Add column with Minute value for current Time less Midnight
"ValueInt",
DateDiff(
TimeValue("12:00 AM"),
Value,
TimeUnit.Minutes
)
)
},
// Build collection based off output from With statement, add Row No logic
ClearCollect(colCalendarDraft,
ForAll(
Sequence(CountRows(tvBlankSchedule)),
Patch(
Index(tvBlankSchedule, Value),
{RowNo: Value}
)
))
);
Here is the output:
I called this ‘draft’ because it’s just the base structure. It doesn’t matter what date is selected, this is the base 3 columns I’ll need, all the time. So all that Power Fx logic to create colCalendarDraft has been added to the OnStart of the app, as we only need to generate it once per session – it’s static after that.
But, we can use this collection to make a ‘final’ collection – and so to the next part of the build.
The build - part 2
In my previous post I walked through building a weekly calendar. When a date is selected from that calendar, we need to take the output of colCalendarDraft to match to scheduled appointments. Appointments information is housed in a separate table.
Here is a sample of the data we’re working with. As per explanations in the previous section, you can see that Date, StartText & EndText all have their respective integer counterparts:
| Client | Date | DateInt | Duration | StartText | StartInt | EndText | EndInt |
|---|---|---|---|---|---|---|---|
|
Ivor Biggun |
7/12/2023 |
20230712 |
30 |
8:00 AM |
480 |
8:30 AM |
510 |
|
Phil McCrevis |
7/12/2023 |
20230712 |
30 |
10:30 AM |
630 |
11:00 AM |
660 |
|
Hugh Jass |
7/14/2023 |
20230714 |
30 |
3:45 PM |
945 |
4:15 PM |
975 |
Our goal is to filter the data by a date column, then plot the relevant entries against colCollectionDraft. We’re going to do this by rebuilding a collection each time, targeting the data we need. However, it’s not as simple as matching StartInt and EndInt, we have to match any potential numbers in-between that range too.
Example – an 8:00 AM – 8:30 AM appointment will have a StartInt of 480 & EndInt of 510. With 15 minute increments, there’s also 8:15 AM in that range – 495. So we need to make sure our matching logic will actually cover 3 blocks in our calendar:
After a few attempts, I settled on an approach to:
> Filter the appointments table by the selected date.
> Create a ‘Final’ collection using ‘Draft’ as the basis.
> Add a column in transit to store the appointment data.
> Additional filtering logic to match any slots in between the appointment StartInt and EndInt values.
With(
{
// Build a temp tbl; appointments for selected date
tvAppointments: Filter(
'Scheduled Appointments',
appDate = gvDateSelect
)
},
ClearCollect(
// Create finalised collection
colCalendarFinal,
AddColumns(
colCalendarDraft,
// Add appointment column, using output of With()
"Appointment",
Filter(
tvAppointments,
ValueInt >= appStartInt && ValueInt <= appEndInt
)
)
)
)
I then remembered our calendar; this Power Fx needs to be called when a date is selected, or when the calendar icon is selected to return to ‘Today’:
We obviously don’t want the same Power Fx being applied to 2 different OnSelect actions, that’s not a good practice. Here’s the way around it:
> Add a button to the screen. In this example, I’ve renamed the button to btnRunMyPowerFx.
> Add the above Power Fx to the OnSelect of the button.
> For the OnSelect of the button in the calendar, and the OnSelect of the calendar icon, add the following Power Fx:
Select(btnRunMyPowerFx)
Finally, set the Visible property of btnRunMyPowerFx to false.
This is a really useful trick to know, meaning your Power Fx is only defined once – therefore only 1 place where changes & maintenance need to be done. Please note: you can only ‘select’ buttons that are on the same screen as the controls you’re selecting it from. The technique does not work if the button is on a different screen.
We need to test the logic just implemented. Firstly, I’ve updated the Items property of the gallery to colCalendarFinal, then added a label to show the client name. For the purposes of the .gif below, I’ve left the button visible so you can see the interaction with it from the weekly calendar and the calendar icon:
Great, it’s working! As predicted though, the name is present for each 15 minute time block relating to an appointment. We need to add some logic to style it a bit better and avoid the repetition.
Row number, you’re up.
The styling
As per the previous posts in this series, some of our styling & config is stored in a global variable. This is created in the OnStart property of the app:
Set(
gvConfig,
{
Background: ColorValue("#FFFCF7"),
Primary: ColorValue("#2B6B31"),
LogoBase: ColorValue("#C4EBD4"),
Negative: ColorValue("#CD5C5C"),
FontText: Font.Lato,
FontHeader: Font.'Lato Black',
FontSizeDefault: 16,
FontSizeHeader: 20
}
);
I’ll start by removing all the labels from the gallery except the one showing the time. Then, I’ll add a button control:
To get our base styling, the button needs some updates to certain properties:
| Property | Value |
|---|---|
|
BorderColor |
Self.Fill |
|
Color |
Color.Black |
|
DisplayMode |
DisplayMode.View |
|
Font |
gvConfig.FontText |
|
Font size |
gvConfig.FontSizeDefault |
|
Font weight |
Semibold |
|
Fill |
gvConfig.LogoBase |
|
Text |
First(ThisItem.Appointment).ClientName |
|
Text alignment |
Align left |
Those changes yield something like this:
Next, we only want the button to show if there’s a matched appointment block. We need to update the Visible property of the button with the following Power Fx:
!IsBlank(First(ThisItem.Appointment))
The next step is to remove the repeating text for each appointment. We also want to manipulate the four radius properties of the button, to give the impression of 1 seamless block. The focus is on the top left hand corner – the RadiusTopLeft property of the button. The following logic is used:
> If the current row doesn’t match the row above, it must be a different appointment so can set RadiusTopLeft to 10.
> If the current row does match the row above, it must be the same appointment so can set RadiusTopLeft to 0.
The Power Fx for the RadiusTopLeft property of the button is therefore:
With(
{
// Get row above current row: row number minus 1
tvPreviousRow:
First(
LookUp(
colCalendarFinal,RowNo = ThisItem.RowNo - 1).Appointment
).ClientName,
// Get current row
tvCurrentRow:
First(ThisItem.Appointment).ClientName
},
// Set RadiusTopLeft based on conditions
If(
IsBlank(tvCurrentRow),0,
tvCurrentRow <> tvPreviousRow,10,0
)
)
Similarly we can set the value of the buttons RadiusBottomLeft property. However, this time we are comparing the current row with the row below. The Power Fx is slightly different for RadiusBottomLeft:
With(
{
// Get current row
tvCurrentRow: First(ThisItem.Appointment).ClientName,
// Get row below current row: row number plus 1
tvNextRow: First(
LookUp(
colCalendarFinal,RowNo = ThisItem.RowNo + 1).Appointment
)ClientName
},
// Set RadiusBottomLeft based on conditions
If(
IsBlank(tvCurrentRow),0,
tvCurrentRow <> tvNextRow,10,0
)
)
To handle the right hand side of the button, we are updating the following properties:
| Property | Value |
|---|---|
|
RadiusTopRight |
Self.RadiusTopLeft |
|
RadiusBottomRight |
Self.RadiusBottomLeft |
Almost there. The relevant corners are rounded or smoothed out, giving the effect of 1 single ‘block’:
Finally, we need to remove the duplication of name. This can be achieved by editing the Text property of the button, with the following Power Fx:
If(
Self.RadiusTopLeft = 10,
First(ThisItem.Appointment).ClientName
)
So that means, if the top left of the button has a radius of 10, show the designated (client name), else don’t show anything. We deliberately leave the ‘false’ part of the If statement above, as we don’t want to show anything if the condition isn’t met.
This is how the wife wants it to look. Happy wife, happy life:
Winner!
It’s worth repeating that yes, our requirements for this article (and indeed, this whole blog series) could be done with an existing 3rd party app. The wife trialed a few but either wasn’t happy with functionality or didn’t want to pay a monthly sub. That’s why we’re travelling down this Power Platform route.
This part of the build was the most challenging. But those unique scenario’s are what help you to learn & stretch the art of the possible. Sometimes just throwing a method out there can spark discussion for making a better one, so hope you’ve enjoyed my stab at it.
In next weeks article, we’ll be tackling adding new appointments and using the JSON / ParseJSON method to save data.
