This chapter demonstrates how to use SharePoint and Power Apps together to schedule appointments when there is a fixed capacity. I originally created this solution in InfoPath for handling classroom-based training events with only a specific number of seats in a classroom or auditorium. Once Power Apps came out, I switched to it instead. The gist of the approach is one SharePoint list that holds the classes or other training events (including the capacity of the event) and another list that contains the attendees as well as what event(s) they are signed up for. Views on the list (or Power BI) provide a training roster. A user or an owner deleting a registration frees up a spot for someone else. This could apply to human resources briefings, imaging appointments for updating computers, vaccinations, tax return appointments, and much more.
The SharePoint Lists
Our first step is to create a custom list
that I will name Classes. In addition to the default Title column, we add Class Date, Location, and Capacity columns. As shown in Figure 12-1, we set the format to Date & Time and the display to Standard.1
A box has Date and Time selected in the date and time format and Standard selected in the display format.
Figure 12-1
Setting the date format to Date & Time and Standard
Looking ahead to when we display a list of available classes, we need a column that will show both the title and the date/time.2 So we add a Calculated column we will call Expanded Title. One challenge is that our drop-down list in Power Apps will have a default value. To avoid users being defaulted to the wrong class (and instead force them to make a selection), we create a class with a dash as the Title.3 Our formula4 is
This formula takes the title of the class, add a left parenthesis, formats the Class Date with a three-letter month, two-digit day, four-digit year, and then the hours and minutes with AM or PM.
Figure 12-2 shows the resulting list with some test data
, including the bottom class that is in the past as of the time of this writing.
A table with the label Classes has 6 columns and 5 rows. The column headings are I D, title, class date, location, capacity, and expanded title.
Figure 12-2
The Classes list with some default data, a default value, and an event in the past for testing
This past event should NOT show up as available in our sign-up form. It will be helpful for making the class roster views to have the ID of the class, which is why we added that column to the default view. We also sort by Class Date in descending order so the oldest classes are at the bottom.
Next, we add an Attendees custom list. We don’t need the default Title column, so we will rename it to Phone and make it optional. If all our attendees have Microsoft 365 accounts, we can make an Attendee column of type Person.5 Otherwise, we can add individual columns for Name and Organization. Most importantly, we link to the Classes list by creating a Lookup column, as shown in Figure 12-3. Power Apps struggles a bit with Lookup columns, so we grab all the columns that we need up front. It also turns out that working with any column other than the primary lookup column (Expanded Title in this case), causes delegation issues. So choose carefully and be ready to rework if you run into any issues.
A screenshot has options to get information from and a drop-down menu for columns. Below them is a list of additional fields.
Figure 12-3
Lookup column linking to the Expanded Title
and other columns of the Classes list
Our next step is to go to the Advanced Settings for the list (Figure 12-4) and choose Read all items and Create items and edit items that were created by the user.
A screenshot has the radio buttons read all items under read access and create items and edit items that were created by the user under create and edit access which are highlighted.
Figure 12-4
Setting proper Read and Edit
SharePoint List Permissions
The first is needed so we can figure out the capacity from Power Apps. The second prevents issues just in case users found the SharePoint list directly and tried to edit someone else’s registration.
Figure 12-5 shows the default New item SharePoint form
. It is inadequate as it shows the past classes and does not have any logic to check the capacity of the class.
A screenshot of a new item form. It has options for phone and attendee. Microsoft 365 training is selected in the drop-down menu for class.
Figure 12-5
Default SharePoint New item form
While we could customize this form with Power Apps, that would require us to send users to the SharePoint list URL directly. I prefer to use SharePoint as a backup data source and send the user to either the Power Apps URL or, better, embed the form as a tab in the appropriate team.
Creating the Power Apps Form
As we have seen in past examples, we create a new Canvas application in Power Apps with the Tablet format (though Phone would work too here as we don’t need much screen real estate). We connect both our SharePoint lists as data sources, as shown in Figure 12-6.
A screenshot of a box with the label data. It has a search bar. Below is the option to add data. Below them are 2 files for attendees and classes.
Figure 12-6
Connecting to both the Classes and Attendees data sources
The Home Screen
For our design, we will have a Home screen that allows us to create a registration and, if existing registrations
exist, delete them. Figure 12-7 shows the result with existing registrations.
A screenshot of a home screen for class signup. It has 2 options. They are to create registration and delete existing registration.
Figure 12-7
Home Screen with the Delete Existing Registration
button visible
Notice how we customize the top label by including the user’s name in its Text property:
"Welcome " & User().FullName & " to the Class Signup Application"
Deciding whether to show the Delete Existing Registration button is not trivial, largely due to delegation constraints. We handle the screen’s OnVisible event so that our code runs each time the user returns to this screen. Our first attempt is shown in Listing 12-1.
ClearCollect(
colUserTraining,
Filter(
Attendees,
Attendee.Email = User().Email,
DateTimeValue('Class:Class Date'.Value) >= Now()
)
)
Listing 12-1
Building a collection to determine if there are future registrations for the current user
Starting in the middle, we Filter() the Attendees list to see if the current user has any registrations. That part is fine, but it is the next filter (DateTimeValue('Class:Class Date'.Value) >= Now()) that gives us delegation warnings
. This comes from the fact that ‘Class:Class Date’ comes from the lookup column. Since it is entirely likely that there will be more than 500 or 2000 registrations, we need to find another solution. This involves splitting the logic, as shown in Listing 12-2.
ClearCollect(
colUserTraining,
Filter(
Attendees,
Attendee.Email = User().Email
)
);
// This has every user training but some of those can be in the past. Need to create new collection with just future registrations
ClearCollect(
colUserFutureTraining,
Blank()
);
If(
Count(colUserTraining.ID) > 0,
ClearCollect(
colUserFutureTraining,
ForAll(
colUserTraining,
If(
DateTimeValue('Class:Class Date'.Value) >= Now(),
{
ID: ID,
Phone: Phone,
Attendee: Attendee,
Class: Class,
'Class:Title': 'Class:Title',
'Class:Location': 'Class:Location'
}
)
)
)
)
Listing 12-2
Splitting our logic to avoid delegation warnings
We return to just the initial logic so that colUserTraining contains all registrations (past and future) for the current user. We then create colUserFutureTraining to be Blank(). We check the count of colUserTraining as there is no point proceeding if there are no registrations for the user. If there are registrations, we use the ForAll() loop once again to operation on colUserTraining. Recall that the return value of ForAll() is a table defined by the second parameter, the formula. We check to see if the Class Date is in the future (>= Now()). If so, we create a new record in colUserFutureTraining with the columns we want: ID, Phone, Attendee, Class, Class:Title, and Class:Location. We will use the colUserFutureTraining collection when we get to the Delete Registration screen later in the chapter, but for now note how it controls the Delete Existing Registration button’s Visible property:
Count(colUserFutureTraining.ID) > 0
Our only other logic is navigating to the corresponding screens in response to the OnSelect event for our Create button:
Navigate(NewScreen)
as well as our Delete button:
Navigate(DeleteScreen)
The New Screen
We jump to this screen to create a registration. The Class defaults to – so the user has to make a selection as discussed earlier in the chapter. The key functionality is then to read the Attendees list and determine 1) if any slots are available
and 2) if the user has already registered? If neither of these, then we show the Save button and allow the user to register. This is shown in Figure 12-8.
A screenshot of the class signup application. It has options for phone, class, capacity, available slots, class title, class location, and class date.
Figure 12-8
Selecting a class with available slots
If there are no more slots or the user is already registered, we show the appropriate message and leave the Save button hidden. These are shown in Figure 12-9 and Figure 12-10, respectively.
A screenshot of the class signup application. The options of capacity, available slots, and current number of attendees are highlighted.
Figure 12-9
No Slots Available and Unable to Save
A screenshot of the Class signup application. The option for Class is highlighted which reads you are already registered for this class.
Figure 12-10
Already Registered and Unable to Save
Let’s look at each of the key controls in turn. For the Class drop-down, the key setting is for the Items property:
Sort(
Filter(
Classes,
'Class Date' > Now()
),
Title,
Ascending
)
We filter out past classes and then sort by the Title.6 We choose Ascending so our – is the first item, since Default = “1”. It will be helpful to store our selected class as a variable and thus handle the OnChange event:
Set(varSelectedClass, ClassesBox.Selected)
While there are other properties of the drop-down list like SelectedText, we want Selected in this case as we want our entire record. We will use this extensively in the following text.
Next is our Already Registeredlabel
. Its Visible property is
!IsBlank(varSelectedClass) && Count(
Filter(
Attendees,
Class.Value = varSelectedClass.'Expanded Title',
Attendee.Email = User().Email
).ID
) > 0
Since we don’t want to show this label if there is no selection yet, we check if varSelectedClass is blank. We then Filter() our Attendees list looking for both the selected class AND our current user.7 Note how we compare Attendee/User Email properties as that is simpler than other options.
For Capacity, we set the Text to be “” if we haven’t yet selected a class and otherwise read the Capacity value from our SharePoint list.
Again, we check to see if we have selected a class. If so, we Filter() the Attendees list for the currently selected class. The Count of this filtered list is our number of attendees.
We are now ready to do a little math to get to our Available Slots. Here is its Textproperty
:
We again check to make sure we have selected a class. If so, we convert our text to a number with the Value() function and subtract the number of attendees from the capacity.
We use our calculation to determine whether to show the “No Slots Available” message. Here is its Visibleproperty
:
If(
IsBlank(AvailableSlotsLabel.Text),
false,
Value(AvailableSlotsLabel.Text) <= 0
)
The value should never be less than zero, but just to be on the safe side, we show the label if the available slots is less than or equal to zero.
While the Class Title and Class Locationlabels
are similar as we mentioned previously, the Class Date has some interesting Text to format the value correctly:
If(
varSelectedClass.Title = "-",
"",
Text(
varSelectedClass.'Class Date',
"mmm dd, yyyy, hh:mm AM/PM"
)
)
We use the Text()method
and then pass a similar syntax to what we used for the SharePoint calculated column earlier in the chapter.
Last but not least is our Save button. Rather than repeat all the previous logic related to whether there are slots available or the attendee is already registered, we link its Visible property to whether those labels themselves are showing (as well as checking for whether the title is -):
As we have seen previously, we use the Patch()method
with the first parameter being the data source (Attendees list) and the second being the record. For a new record, we use the Default(Attendees) syntax to write the default values of any columns we do not specify in the third parameter. We have previously seen how to write a SharePoint Person column, which is what we do for Attendee. What we haven’t seen yet is how to write a Lookup column. Here is that part again:
This is similar to the Person column except that we need to pass the Id and Value. The Value needs to be the column we selected in Figure 12-3, which was Expanded Title. We then write the Phone value. So that we are ready to select another class, we Reset() the PhoneBox control, set the varSelectedClass variable back to Blank(), and then return to the Home screen.
Finally, we look at the OnSelect code
for the Home button at the upper right:
Set(
varSelectedClass,
Blank()
);
Navigate(HomeScreen)
We again clear the varSelectedClass variable and then return to the Home screen.
The Delete Screen
As we saw earlier, we can only get to this screen if the user has at least one future class registration. Figure 12-11 shows how it looks with two registrations.
A screenshot of a class signup application. The planner training with location is highlighted and at the bottom is a button to delete registration.
Figure 12-11
Delete Registration Screen
For our gallery, we take advantage of the colUserFutureTraining variable we configured in the OnVisible event for the Home screen (see Listing 12-2). Here is the gallery’s Items property
:
Filter(
colUserFutureTraining,
SearchClassesBox.Text in Class.Value
)
We Filter() the collection by whatever we type into the SearchClassesBox input control. We don’t have to worry about delegation warnings since we are filtering a collection and not a data source. We also handle the OnSelect event and set a new varSelectedItemToDeletevariable
:
Rather than our normal global variable, I used a context variable to try to ensure that it would not survive once I return to the Home screen. This is to avoid the possible situation where the user leaves the screen, comes back, and then clicks the Delete button without making a new selection. To be on the safe side, I clear the variable when leaving the screen as well.
To give a visual indication of which item is selected, we set the Fill property of our Title to be:
We check the IsSelectedproperty
(which is true immediately for the first item in the gallery) AND the varSelectedItemToDelete variable (which is not set until the user clicks on the > in the gallery) and then set the color to yellow when both are true. This reminds the user to actually select the class registration they want.
We reuse this same variable to control the Visibleproperty
of our Delete button:
!IsBlank(varSelectedItemToDelete)
So that button won’t show up until the user selects a class registration. Listing 12-4 shows the OnSelect code for the button.
We use the helpful Remove()method
. Its first parameter is the data source (our Attendees list) and its second parameter is the record to remove. For that, we use the LookUp() method and find the record with the ID that matches up to what we selected in the gallery. We then clear our varSelectedItemToDelete context variable, just to be safe. From there we navigate back to our Home screen. Note that it will free up capacity for other students.
The OnSelect code for our Home button is identical to the end part of the preceding script, obviously not deleting the record first:
In this chapter, we learned how to use two SharePoint lists and Power Apps to create a solution for only allowing users to sign up for a class or other type of event/appointment with available slots. When the user or an owner deletes a registration, that frees up a slot for someone else. Logic within Power Apps allows us to calculate availability, display a message when there are no slots, and more. Views in SharePoint or, even better, Power BI reports, allow us to easily generate a roster of registered attendees per event. We will explore that in the next chapter.
