Capturing Appointment Data in Power Apps

There are a few ways to capture and save data in Power Apps, with methods ranging in complexity and caveats. In this article, we’ll build a data capturing experience for my wife’s business solution.

Just a quick heads up before we dive in; this article is a continuation of a wider series, so there may be references to galleries or functionality detailed in previous weeks.

Table of Contents

The requirement

The wife needs to book an appointment when requested by a client. An appointment can have one or multiple treatments, as clients can tailor-make each visit to the salon (aka glorified shed in the garden).

Typically, we might attack this by saving the data in 2 tables;

> Appointment data (otherwise known as the ‘master’ record) would be saved in Table 1, one line per unique appointment.
> Treatments data relating to an appointment (otherwise known as the ‘child’ records) would be saved in Table 2, with a unique identifier repeated to relate the records back to its master appointment.

This is a common method and one I’ve done personally & professionally lots of times. It comes with some level of complexity though, notably around sequential Patch statements and their error handling. Is this going to be a good ‘entry’ point for my wife, who’s new to the ways of Low Code sorcery?

Later this year, I’ll be covering a very in-depth mini-series on different types of data capture, pro’s & con’s etc. But for the short term an easy entry point for beginners is Form controls. This will cover the ‘master’ record part. We’re going to get creative with the ‘child’ records and use the JSON/ParseJSON technique I blogged about previously. This will reduce some complexity and keep all information tied to the single master record.

Let’s dive into the build, where hopefully all of that will make more sense.

Data tables

For reference, we have 3 tables that are underpinning our business solution:

> Scheduled Appointments; to house all data relating to the appointment.
> Client Database; to store clients and related information, such as preferences and consultation history.
> Treatments; list of available treatments, their duration and cost.

Foundations

There’s a couple of things we can do in our business app to lay foundations for capturing appointment data.

Adding a new screen

First up, we need to add a new screen to our app We can do this from the top menu:

On this new screen, we’ll need to add a Form control.

Adding a form control

We can find this by clicking on Insert, then selecting the Edit Form option:

With the form control selected, my first two things to update in the right-hand panel are:

> Name; it’s good practice to rename controls in Power Apps. I’m renaming ours to frmNewAppnt.
> Datasource; form controls need a data source to communicate with. I’m wiring ours up to our ‘Scheduled Appointments’ table.

By default, when adding a Form control to a Power App it’ll default to Edit mode. For the purposes of capturing information about a new appointment, we need to create a record, not edit one.

With the Form control still selected, we need to change the Default mode property from Edit to New:

We now need to add some fields, else we won’t be capturing much data! Once again, this can be done with the Form control selected and interacting with the right-hand panel:

For the purposes of this build, we’ve got a few fields added in. Seems like a long list but most will be hidden & take care of themselves:

Date and start time

In my previous post, we walked through setting up a time-based calendar. We built some functionality to only show a button if the time block is already taken:

We now want another button, which will only be visible when the time block isn’t taken. Let’s add the new button to the gallery:

We want to interact with our button, but we don’t want to see it. It just needs to be pressed so the OnSelect property is triggered. Before we configure that, our new button needs some properties updating:

Property	Value
BorderColor	Color.Transparent
BorderStyle	BorderStyle.None
Color	Self.BorderColor
DisplayMode	DisplayMode.Edit
Fill	Self.BorderColor
HoverColor	Self.BorderColor
HoverFill	Self.BorderColor
Visible	IsBlank(First(ThisItem.Appointment))

When building our weekly calendar, we set a variable called gvDateSelect. This is simply the date in a usual mm/dd/yyyy format, so that’s already taken care of. We’ll need to capture the Start Time though, as well as its relevant integer value. We then need to prepare the Form control for a new submission, then navigate to the screen we added earlier. The Power Fx for the OnSelect property of our new button therefore looks like this:

				
					Concurrent(
    // Capture time value, eg 8:00 AM
    Set(
        gvStartTimeText,
        ThisItem.Value
    ),
    // Capture time value as int, eg 8:00 AM is 480 
    Set(
        gvStartTimeInt,
        ThisItem.ValueInt
    )
);
// Reset form ready for new submission
NewForm(frmNewAppnt);
// Navigate to screen
Navigate('Booking Screen')

Our two global variables aren’t reliant on each other, so a good practice is to place these inside a Concurrent function. This ensures they run in parallel and not sequential. It probably doesn’t matter just for setting two globals, but best practice is best practice. Meanwhile, the NewForm function ensures our frmNewAppnt Form is ready to accept a new record.

For screen navigations, I don’t add any effect which means it’ll default to ScreenTransition.None. Cover, Uncover and Fade look all fancy on your 5oo mbps superfast broadband, but view them on lower speeds or on dodgy cellular coverage. Laggy as hell, so not the best user experience.

Form updates

We can start plugging in our first wave of variables to our form. Firstly though, we need to tolerate one of the most frustrating things about Form controls.

To edit any of the fields & most of the useful properties in a Form control, first you have to select the DataCard, go to Advanced in the right-hand menu, then select Unlock to change properties:

Repeat for all DataCards (aka fields) in your Form that you’ll need to make changes to.

For our first update, we’ll use the appDate field as an example. We can access the DataCard control for the field, select the Default property and overwrite the value in the formula bar with our gvDateSelect variable:

We’ll repeat this for the Default property of some of the other DataCards too:

Form field	Default property
appDateInt	Text(gvDateSelect, “yyyymmdd”)
appStartText	gvStartTimeText
appStartInt	gvStartTimeInt

So far, so good.

Client information

In our Scheduled Appointments table, the appClientName field is a lookup to our Client Database table. This is reflected in our Form control, as the combo box control is already added and configured for us:

For a combo box, I’ll refer straight away to two important settings. With the combo box selected, check the right-hand panel for Allow multiple selections and Allow searching:

The wife only wants 1 person per appointment so Allow multiple selections is off. We do want to allow searching though, especially as her list of clients grows.

We can also update the Default property for the appClientEmail field, using the appClientName combo box selection to retrieve the email address:

				
					ClientNameComboBox.Selected.ClientEmail

A client can’t be added to our database without an email address. An appointment can’t be logged without one either; it’s a mandatory field because we need it for confirmation emails.

Treatment information

As mentioned above, an appointment can have single or multiple treatments. We need to build some kind of ‘package builder’ experience to capture what the client wants. This will help the wife set up correctly for each appointment.

We also need to handle discounts. This can be client specific (so a standard % for all treatments) or on a case-by-case, treatment-by-treatment basis. The high-level approach we decided on:

> Treatments are shown via a Combo box control, linked to our Treatments table.
> Text input control to show, or manually enter a discount value.
> Button to add chosen treatment details (with or without discount) to a collection.
> Convert finalised collection to JSON.
> Include JSON output in dedicated field in Form control, ready to save as part of the new appointment record.

Setup

We’ll start by adding a new Combo box control to our screen. The Items property needs to be our Treatments table:

Next, we need a Label control with the Text property updated to ‘Discount %’. We’ll also need a Text Input control to show, or add a discount value. I’ve renamed the three controls cmbTreatment, lblDiscount and txtDiscount, respectively:

We need to update a few properties for cmbTreatment and txtDiscount. For the Default property of txtDiscount, we’re leaning on our Client Name lookup column (from within the Form control) to get their allocated discount value if available:

Control	Property	Value
cmbTreatment	SelectMultiple	false
cmbTreatment	IsSearchable	true
cmbTreatment	InputTextPlaceholder	“Find treatment”
txtDiscount	Default	ClientNameComboBox.Selected.ClientDiscountPerc
txtDiscount	Format	TextFormat.Number
txtDiscount	Height	50
txtDiscount	Width	50

We also need to add a Button control. This will add the selected treatment and any relevant discount to a dedicated collection. I’ve updated the name to btnAddToPackage and updated the Text property too:

That’s the initial setup complete:

Actions

In the OnSelect property of btnAddToPackage, we want to add the treatment information to a collection. This will store the treatment name, duration and cost. The cost also needs to factor in any potential discount, so need logic to calculate that.

As an example, let’s say there’s 10% discount. That means we need to work out the remaining % to charge:

100% – 10% = 90%.

I’d like the output in the format of 0.90 though, so we can calculate treatment value multiplied by remaining % to charge:

(100 – 10) / 100.

We need that logic to apply dynamically, referencing our txtDiscount text input control. The calculation can then be applied to the record we are adding to the collection. If there is no discount applied, the sum will output to 1 as the multiplier.

Here’s the Power Fx for the OnSelect property of btnAddToPackage:

				
					With(
    {
        // Calculate discount multiplier
        tvDiscountRate: (100 - Value(txtDiscount.Text)) / 100},
        // Add treatment to package
        Collect(
            colTreatmentPackage,
            {
                Treatment: cmbTreatment.Selected.Name,
                Duration: cmbTreatment.Selected.Duration,
                Cost: cmbTreatment.Selected.Cost * tvDiscountRate
            }
        )
)

Visualise

The wife will need to visualise the treatment package real-time. Our colTreatmentPackage collection can therefore be leveraged with a gallery:

In the gallery, we can add 3 labels and set their Text properties to ThisItem.Treatment, ThisItem.Duration and ThisItem.Cost, respectively:

The final touch is to add an option to delete a row, should the client change their mind. That’s easy enough, add a Trash icon to the gallery and configure the OnSelect property with the following Power Fx:

				
					Remove(
    colTreatmentPackage,
    ThisItem
)

Let’s test our logic out by adding a treatment with and without discount applied:

Excellent, working as expected. With this colTreatmentPackage collection in place, we can now update the Default property of a couple more DataCards in our Form control.

For the appDuration DataCard, the Default property is updated to:

				
					Sum(colTreatmentPackage, Duration)

For the appTotalCost DataCard, the Default property is updated to:

				
					Sum(colTreatmentPackage, Cost)

For the appTreatmentPackageText DataCard, the Default property is updated to:

				
					Concat(colTreatmentPackage, Treatment, ", ")

Example:

End time

We have two fields in our Form control to capture appointment end time information; appEndText and appEndInt. We have to rely on the output of colTreatmentPackage to calculate these.

Start Time + total duration of treatment package = End Time.

For the appEndText DataCard in our Form control, we can update the Default property with the following Power Fx:

				
					// Time value of StartText, eg 8:00 AM
TimeValue(gvStartTimeText) 
+ 
// Calculate Time value of Treatment package
Time(
    0, // hours
    Sum(colTreatmentPackage,Duration), // minutes
    0 // seconds
)

For the appEndInt DataCard in our Form control, we can update the Default property with the following Power Fx:

				
					DateDiff(
    TimeValue("12:00 AM"),
    TimeValue(gvStartTimeText) 
    + 
    Time(
        0,
        Sum(colTreatmentPackage,Duration),
        0
    ),
    TimeUnit.Minutes
)

We’d expect the outputs for both fields to increase, depending on the quantity (and therefore, duration) of treatments being added to the package:

Submit the data

The form control to create our new appointment needs to be submitted back to the Scheduled Appointments table. We need to configure a couple more things for that to happen.

Convert treatments info

A new button needs adding to the screen, below the gallery for visualising colTreatmentPackage. I’ve updated the Text property of the button to Submit and renamed the control to btnSubmitAppointment.

The OnSelect property of the button needs to do 2 things:

> Convert colTreatmentPackage to JSON.
> Submit the form back to the Scheduled Appointments table.

Here’s what the Power Fx looks like:

				
					// Convert colTreatmentPackage into a JSON object
Set(
    gvTreatmentPackageJSON,
    JSON(colTreatmentPackage));
// Submit the form!
SubmitForm(frmNewAppnt)

Before we test this button, we need to update the Default property of the appTreatmentPackage DataCard. It needs to reference the gvTreatmentPackageJSON variable:

Form OnFailure

One of the good things about Form controls is the pre-baked OnFailure and OnSuccess properties. Forms have some base logic to validate against its connected data source, submitting the data if everything checks out ok. Data won’t be submitted if the Form control detects an error upon submission, for example a mandatory field doesn’t have a value.

If an error occurs, we can use the OnFailure property to perform one or a series of actions of our choosing. For our scenario, we’ll configure with a simply Notify function. You can tell the wife wasn’t around when I configured this:

				
					Notify(
    // Custom message
    "Appointment not added. Sucks to be you right now, doesn't it",
    // Notification type
    NotificationType.Error,
    // Show message for 5 seconds
    5000
)

Form OnSuccess

If the submission back to the Scheduled Appointments table is successful, we can use the OnSuccess property to perform a series of actions.

The OnSuccess of our Form control will be a bit more involved, as needs to reset a few variables, empty a collection, show a success message and return back to the calendar screen.

Here’s the Power Fx in full for the OnSuccess property of the form:

				
					Concurrent(
    Notify(
    // Custom message
        "Appointment added successfully",
    // Notification type
        NotificationType.Success,
    // Show message for 2 seconds
        2000
    ),
    // Empty treatment collection
    Clear(colTreatmentPackage),
    // Reset variables/controls
    Set(gvTreatmentPackageJSON,""),
    Reset(cmbTreatment),
    Reset(txtDiscount)
)

In this article, we’ve created a new screen, added a Form control, wired it up to a data source and built a simple mechanism to capture treatments.

With some formatting tweaks, here’s a demo:

In the next article, we’ll walk through triggering a Power Automate flow when a new appointment being added, so the client gets a confirmation email.