Drop-Down Control

To demonstrate the drop-down control , here’s how to apply the control to a text field called “credit check” in our tenancy table. In this example, we’ll limit users to entering the value “yes” or “no” in this field.

To set up our form so that the credit check card uses a drop-down control, the first step is to set the control type of the card to “Allowed values” (Figure 11-4). This will create a drop-down control on the card.

We can now run our app and use the drop-down control to set the “credit check” value of the record. An interesting question we might ask is this: If we open an existing record with a value that doesn’t exist in the drop-down, what would happen? The answer is that the drop-down would show a blank value. In this scenario, Power Apps will not display any error messages to the user, and it will not crash or stop working entirely .

Setting Lookup Values

The simple example we’ve seen is not particularly future-proof because it relies on hardcoded values. To enable users to maintain the choice items that appear in a drop-down or combo box control, we can build a data structure that stores the choice items in a separate table or SharePoint list.

With SharePoint, it’s very simple to develop this type of structure. In our example, we have a tenancy list that stores tenancy details. To store the property that relates to a specific tenancy record, we can define a lookup column in the tenancy list and configure this column to refer to a SharePoint list of property records.

With Dataverse, we can also easily define lookups by creating relationships between tables. With SharePoint and Dataverse data sources, the designer understands the relationships between lists or tables. This enables us to more easily build screens that use the drop-down or combo box controls.

We’ll now look at how to configure a form to store lookup values, based on SharePoint and Excel data sources.

Setting Lookup Values with Other Data Sources

With other data sources such as Excel or SQL Server, Power Apps cannot identify the relationships between tables; and therefore, there is more work involved to configure a combo box or drop-down control.

To demonstrate how to implement lookup columns in data sources other than SharePoint or Dataverse, Figure 11-6 illustrates the data structure that supports a lookup field. In this example, we have a property table that stores property details. This table contains a numeric PropertyID field that uniquely identifies each row in the table. In the tenancy table, the PropertyID field stores the numeric PropertyID value that corresponds to the record in the property table. Our set of tables also includes a property type table to store details of the property type.

As a starting point, let’s suppose we create a new app that includes these Excel tables. We then add a form, set the data source of the form to the tenancy table, and add the property field.

To modify the property card so that it uses a drop-down control, we change the control type to “Allowed values” and unlock the card.

Next, we set the Items property of the card to the property data source, as shown in Figure 11-7. Because the property table includes several columns, we can use the Value property to specify the field that appears in the drop-down.

The default name of the drop-down will be something like DataCardValue28. To make this control easier to identify, we’ll rename it to drpProperty.

The next step is to configure the card to save the numeric property ID value when a user saves the record. To do this, select the parent card control and set the Update property to the following formula:

 drpProperty.Selected.PropertyID

The final step is to set up the drop-down box to show the correct item when a user opens an existing record. To configure this, set the Default property of the drop-down to the following formula:

LookUp(drpProperty, PropertyID = Parent.Default).Name

We can now run our app and set the property value of a tenancy record using a drop-down control.

Customizing the Drop-Down Display Value

The AddColumns function adds columns to a data source, collection, or table. This function accepts three arguments – a data source, the name of the column to add, and the value for each row.

Here, we add a column called AddressDesc that combines the first line of the address and the postcode in parentheses. To display this value in the drop-down, we set the Value property of the drop-down to AddressDesc.

The LookUp function returns the first record from a data source that matches the formula that we provide. The data source we provide here is the formatted version of the Property table that includes the address and the postcode in parentheses. The lookup formula retrieves the record from this data source where the PropertyID value matches the PropertyID value of the tenancy record.

Figure 11-8 shows the appearance of this code in the designer. We can now run our app, and each row in the property drop-down will show the address and postcode fields.

Nesting Drop-Down Controls

To assist data entry, we can nest together sets of combo box or drop-down controls. There are many practical scenarios where this is useful. To give an example, an address entry screen could provide a drop-down box that displays a list of states. When a user selects a state, a corresponding drop-down box will display a list of cities that match the selected state.

To demonstrate this technique, we’ll modify our example form to include a property type drop-down. This drop-down control will display a list of property types such as house, apartment, and bungalow. When a user selects an item from this drop-down, the drop-down box of properties will update to show only the property records that match the selected property type. To support this example, we’ll add a table of property types to our app as shown in Figure 11-9.

Power Apps provides a simple no code method to implement nested drop-downs. This simple technique is less customizable, and there is another, more complex method that we can adopt. We’ll now cover both these techniques

Setting Drop-Down Control Dependencies

Starting with the simple technique, the first step is to add an additional “property type” drop-down to the card that we adapted to include a property selection drop-down. We would set the Items property of the new “property type” drop-down to our property type table.

The properties panel of the existing property drop-down includes a link that enables us to specify a dependency. We can use this to configure the property drop-down to only show records where the property type ID matches the property type ID in the property type drop-down (Figure 11-10).

Although this technique is simple and involves no code, the limitation is that we must use the items drop-down to select a data source and we must use the Value property to define the text that displays in each row of the drop-down. This prevents us from customizing the text that displays in the drop-down.

Changing the Combo Box Layout

If we want to specify drop-down dependencies using a no code technique but also require more flexibility over the text that appears, another option is to use a combo box rather than a drop-down.

A useful feature of the combo box control is that it provides three row item templates. The default template displays a single data item per row. There is also a double item template that displays two data items per row and a person template that enables us to show a photo.

Let’s imagine we now replace our drop-down control with a combo box control. In the fields section of the properties panel, we can choose the layout that we want to apply (Figure 11-11).

Once we specify a layout, we can choose the data items that we want to show on each row of the combo box. In this example, we choose the double layout, and we configure the options to display the first line of the address and postcode. At runtime, the combo box will show both of these values as shown in Figure 11-12.

Defining Drop-Down Dependencies with Formulas

Although the combo box control provides more flexibility, it might not completely satisfy our needs. We may want to display more than two data fields per row, customize the display text, or not like the appearance of each item taking up two lines in the combo box. In these cases, we can implement drop-down dependencies by writing our own formulas.

To demonstrate this technique, we’ll extend our earlier example where we customized each row of our drop-down control to display the first line of the address followed by the postcode in parentheses.

Taking the form that displays a tenancy record, the first step is to add a “property type” drop-down to the card in the form that corresponds to the property ID field. Next, set the Items property of this drop-down to the property type table and name the control drpPropertyType.

To configure the “property” drop-down to show only those records that match the selected property type, we would set the Items property of the property drop-down to the following formula:

Filter(AddColumns(Property,

                  "AddressDesc",

                  Address1  & " (" & Postcode & ")"

                  ),

       PropertyTypeID=drpPropertyType.Selected.PropertyTypeID

)

Like before, we would set the Value property to AddressDesc, and we would set the Default property to the formula shown in the following:

LookUp(AddColumns(Property,

                  "AddressDesc",

                  Address1  & " (" & Postcode & ")"),

       PropertyID = Parent.Default).AddressDesc

Figure 11-13 shows the appearance of this in the designer.

To complete this feature, we can configure the default display of the property type drop-down. When a user loads an existing tenancy record, we can set the value of the property type drop-down to match the property type of the property that is associated with the tenancy record. To configure this, set the Default property of the property type drop-down to the following formula:

LookUp(PropertyType,

       LookUp(Property,

              PropertyID = ThisItem.PropertyID

       ). PropertyTypeID = PropertyTypeID

).PropertyTypeDesc

This formula contains a set of nested lookups. The innermost lookup retrieves the property record that relates to the tenancy record in the form. The outermost lookup retrieves the property type record that is related to the property record.

Note that we can also add the same logic to the property type drop-downs that we created with the no code method. Figure 11-14 shows the appearance of this in the designer.

When we now run our app, the items that appear in the property drop-down will display the first line of the address and postcode in parentheses, and the property type drop-down will also show the property type that correctly matches the property.

Selecting Multiple Items with a Combo Box

A powerful feature that the combo box control offers is the ability for users to select multiple items. The easiest way to set up a multi-selection combo box is to use a SharePoint data source. With a bit more work, we can also utilize multi-selection combo boxes with other data sources. In this section, we’ll walk through the steps to configure a multi-selection combo box in both these scenarios.

Combo Box Multi-selection with SharePoint

With SharePoint, we can define a choice column that permits multiple selections. With this type of column, we can easily construct a data entry form without needing to write any code.

To demonstrate this feature, Figure 11-15 shows the setup of a choice column in our tenancy list. The purpose of this column is to provide a list of utilities that is related to a tenancy.

We can now add a form, set the data source to the tenancy list, and include the utilities field. The designer will create a card with a combo box control with the multi-selection feature enabled. The good news is that this now all works without us needing to perform any additional tasks.

Figure 11-16 shows the appearance of the combo box at runtime. When a user expands the combo box control, it’s possible to select multiple items. The selected items appear at the top of the combo box, and it’s possible to remove an item by clicking the cross icon.

Combo Box Multi-selection with Other Data Sources

With data sources other than SharePoint, we can set up a multi-selection combo box, but it involves more work and custom formulas.

To demonstrate, we’ll recreate our SharePoint example with Excel. The first step is to add a column called utilities to the tenancy table in our spreadsheet. We will store comma-separated choice items in this column.

We can now add a form, set the data source to the tenancy table, and include the utilities field. By default, the utilities card will contain a textbox. We need to unlock this card, delete the textbox control, and replace it with a combo box control. We’ll call this control cboUtilities.

We can define the items that we want to display in the combo box by setting the Items property to the following formula:

["Electric","Gas","Phone", "Water"]

To store the selected items in a comma-separated format, we would set the Update property of the card to the following function:

Mid(

     Concat(cboUtilities.SelectedItems, "," & Value),

     2,

     Len(Concat(cboUtilities.SelectedItems, "," & Value))

)

The SelectedItems property of the combo box control returns a table of selected items. We call the Concat function to combine these rows into a single string, with each row prefixed with a comma. The Mid function removes the proceeding comma.

To configure the combo box control to show the selected items when a user loads an existing record, we set the DefaultSelectedItems property to the following formula:

RenameColumns(Split(Parent.Default, ","),

              "Result",

              "Value"

)

This formula calls the Split function to separate the comma-separated input value into a table. The Split function creates a table with the column name Result. We call the RenameColumns function to rename this to Value. This matches the “display fields” property of our combo box control and enables it to correctly display the selected items. Figure 11-17 illustrates the appearance of our form in the designer.

List Box Control

The list box control enables users to select one or more items from a list. For practical purposes, this control works almost identically to the drop-down control. In addition to the multi-select capability, the only difference is that the data items are always on display.

The Items property defines the data source, and the Value property defines the data field that is shown for each item in the control.

Just like the drop-down control, we can customize the item text by setting the Items property to a formula that utilizes the AddColumns function . We can also specify a dependency to filter the items in the control based on the value of another control.

To highlight this control, here’s an example that we used earlier in this book (Figure 11-18).

The list box control can support multiple selections, and the SelectMultiple property controls this feature.

We can use the SelectedItems property to retrieve a table of selected items. If we were to use this control to display a data value for an existing record, we could use the Default property to specify the selected item. However, a major limitation is that we can preselect a maximum of one item only, and this prevents us from using this control to display multiple items.

Therefore, the list box control works best for data entry purposes. A typical scenario is where we want to build a screen that accepts search criteria values.

Displaying Tables of Data

The data table control is a great control that enables us to quickly and easily display data in a tabular format. The table control is read-only and doesn’t provide cell-level editing like Excel.

To specify the rows and columns to display in the data table, we can set the data source with the Items property or the data source drop-down. We can then use the fields section to define the columns that we want to display, as shown in Figure 11-19.

Through the fields section, we can define the control type. Where this comes in useful is for telephone or web address fields. With these data types, we can use the properties of the column to enable a hyperlink. This configures the cell value to display a hyperlink that opens the telephone dialer or the web address in a browser.

Through the properties of the column, we can set the header text and the column width attributes (Figure 11-20).

The data table control behaves similarly to the gallery control because it keeps track of the selected row. Therefore, we can use it for record selection purposes, or we can utilize the design pattern where we place an edit form next to the data table control and set the data source of the edit form to the selected item in the data table .

Choosing a Gallery Type

There are three gallery types that we can add to a screen. These are the vertical, horizontal, and flexible height galleries (Figure 11-21).

A vertical gallery arranges items from top to bottom, whereas a horizontal gallery arranges items from left to right. A horizontal gallery works well with the landscape orientation of a tablet device.

The purpose of a flexible height gallery is to display content that could vary in length. Figure 11-22 compares the appearance of a vertical gallery with that of a flexible height gallery.

A vertical gallery imposes a fixed row height. If the amount of data in a row exceeds the specified height, the content in the row will overlap the next row. We can avoid this problem with a flexible height gallery . With this type of gallery, the row height will grow automatically, so long as the content that we add to the item template are labels or HTML text controls with the AutoHeight property set to true.

Applying Layouts

To help us quickly build the item template of the gallery in a visual way, we can apply a layout. Each layout provides a templated set of placeholder controls. The choice of layout depends on the gallery type. Figure 11-23 shows the gallery layouts that are available with each gallery type. The icons in this screenshot indicate the appearance of the layout.

The vertical gallery type provides the largest choice of layouts. The horizontal and flexible height gallery types provide a choice of only three layouts each. Once we apply a layout, we can use the designer to select which fields to display in each placeholder.

Figure 11-24 demonstrates how we would apply the “title, subtitle, and body” layout to a vertical gallery and how to set the placeholder fields.

Setting Properties

In this section, we’ll explore how to modify the appearance and behavior of galleries through the gallery control settings. Figure 11-25 shows a horizontal gallery and an excerpt from the properties pane.

The gallery control can display forward and backward navigation buttons. The “Show navigation” setting controls the visibility of these buttons. The Navigation step setting controls how many records to move through when the user clicks the navigation button. In this example, the gallery fits four records. By setting the navigation step to 4, the user can quickly view the next or previous four records by clicking the navigation button.

It’s possible to hide the scrollbar and navigation items. A scenario where we would do this is if we were building a menu structure and we didn’t want navigation items to appear.

There are three transition options that we can set – none, push, and pop. The Transition setting defines the visual behavior of an item when a user hovers the mouse over the item. With the pop setting, the controls in the item template pop out toward the user and become more prominent when the user hovers the mouse over the data item. With the push setting, the controls in the item template sink into the gallery when the user hovers the mouse over the data item.

Another useful setting is wrap count. On a vertical gallery, this defines the number of records to display in each row. On a horizontal gallery, this defines the number of rows that are visible. We can use the wrap count setting to fit more records into a space or to help build a tile layout of records.

Figure 11-26 shows a horizontal gallery with the wrap count of 3. With this setup, the gallery will display records from top to bottom (for three rows) and then left to right.

Incorporating Data Entry Controls

The beauty of the gallery control is that we can add all sorts of controls to an item template. These include data entry controls and nested galleries. For example, we could add a checkbox control to the item template of a gallery, and this would provide a means for users to select multiple records. We could use this as an input to update or to delete multiple records.

To demonstrate how to incorporate data entry controls inside a gallery, we’ll walk through the steps to build a single screen that allows a user to edit multiple records in one go. Figure 11-27 illustrates the appearance of this screen. Using this screen, the user can amend the description of multiple records and click the “Save all changes” button to save all the changes in one go.

Let’s look at the parts that make up this screen. The most crucial part is the central gallery control. The Items property of this control is set to our property data source. The item template of this gallery contains two controls – a label that displays the address and postcode fields and a text input control (Figure 11-28). The Default property of the text input control is set to the description field.

The gallery control contains a property called AllItems. Through this property, we can access all items within the gallery. This includes not just the data values in the gallery but also any controls that we add to the item template.

Within this hidden gallery, there are two controls, as shown in Figure 11-29. These include a label called lblPropertyID that displays the property ID and a label called lblNewDescription that stores the updated description.

This formula loops through the items in the hidden gallery and calls the UpdateIf function to update the description that corresponds to the property ID of the record. We covered the UpdateIf function in Chapter 7.

This example highlights the benefit of using a hidden gallery. It makes our call to the ForAll function easier to read because we’ve extracted the function that filters the source gallery for any modified records. Also, by referencing the updated values through labels, we avoid any tricky disambiguation syntax due to the data source of our updated records and the target data source sharing the same column name.

If the formula fails to update our data source, another advantage of this technique is that we can debug the problem by setting the visibility of the hidden gallery to true and inspecting the records that we want to update in more detail.

Laying Out a Form

We can use the Layout setting to define where the field labels appear in relation to the data entry controls. There are two settings we can apply – vertical and horizontal.

The default is the vertical layout. With this layout, the labels appear above the data entry controls. With the horizontal layout, the labels appear to the left of the data entry controls. Figure 11-30 illustrates a form with a vertical layout compared to a form with a horizontal layout.

Defining Form Columns

Two other settings that control the layout of a form are the Columns and Snap to columns settings. To demonstrate how these settings work, Figure 11-31 illustrates a form with a Columns setting of 4. This example highlights a way in which we can improve the presentation of a form. For some fields such as the address fields, we would want to provide a wider text input control, whereas for some of the shorter fields, we would want to combine some of those onto the same row to help conserve screen space.

With a Columns setting of 4, Power Apps applies an invisible grid beneath the form that consists of four columns. We can position cards so that they fit into a column, or we can stretch a card so that it spans multiple columns.

With the Snap to columns setting turned on, we can only stretch a card so that it fills an entire column. With Snap to columns turned off, we can stretch a card so that it partially fills a column (Figure 11-32).

Here are some additional tips. It isn’t possible to rearrange the order of the cards on the form by dragging and dropping the cards in the designer. The way to rearrange the order of the cards is to click the Edit fields link and to rearrange the cards from that section of the designer.

If we want to leave an empty column, we can add a blank custom card, and we can position that into the column that we want to leave blank.

Combining Forms and Patch

Sometimes, a form layout can be too rigid. For additional flexibility, a technique we can use is to combine multiple forms and the Patch function . The benefit of this technique is that in the case of an edit screen, we can use the graphical designer to build the UI that loads an existing record, and we can do this productively in a no code way. By separating fields into separate forms, we can design our data entry screens more granularly. We can split data entry sections into tabs, or we can separate forms across multiple screens.

Figure 11-33 shows a simple example of a screen.

The form control exposes a property called Updates . This returns the values of the data fields that have been changed by the user.

Setting Form Default Values

To help users more easily create new records, we can set default field values for a record. With a form, we can write a formula that detects the form mode. This can be one of two modes – new or edit. If the form is in new mode, we can show a default value. If the form is in edit mode, we can show the existing field value instead. To demonstrate, here’s how to modify an edit form based on our tenancy table. When a user creates a new record, we’ll default the start date to today’s date.

To build this example, the first step is to open the edit form and to find the card that corresponds to the start date field, as shown in Figure 11-34. We unlock the card and set the Default property of the data card to the formula shown in the following:

If (frmTenancy.Mode = FormMode.New, Now(), ThisItem.StartDate)

When we now run this form, the start date field will display today’s date when we create a new record. When we edit an existing record, it will show the existing start date value.

Resetting Form Controls

Power Apps provides a framework to reset control values back to their default states. This feature serves a very useful purpose because we can use it to enable users to undo their changes. We’ll demonstrate this feature by adding a discard button to an edit form. If a user starts to edit a record and decides not to continue, the discard button will restore the control values back to their initial state.

Every control includes a property called Reset, and all controls will respond to a change in this property. When the Reset property for a control changes to true, it triggers the control to restore its value back to its initial state. The pattern to build a discard feature is to set the Reset property of all controls to a context variable. To trigger the reset operation, we would set the value of the variable to true. Next, we would immediately set the value back to false. By setting the value to false, the user can repeat the discard operation at a later point in time.

To build this feature, the first step is to add a button that triggers the reset operation (Figure 11-35).

We would now set the Reset property on all the data entry controls to the variable ResetForm. We can now run our screen. If we change the values on a form and click the reset button, the controls will revert to their initial values.

Validating Form Values

A great benefit of using forms with data sources such as SharePoint, SQL Server, and Dataverse is that the designer creates the controls to enforce any basic data constraints that we define at the data source. This includes mandatory field, data length, and range validation (i.e., whether an input value falls between a defined minimum and maximum range).

If we need to apply more complex validation rules, we need to build this into our application manually. To demonstrate a typical validation rule, here’s how to ensure that users enter end date values that are greater than the start date.

To build this feature, the first step is to unlock the start date card. To make our formula more readable, we’ll rename the start date picker and time controls to dteStart, drpHourStart, and drpMinuteStart.

Next, unlock the end date card and rename the end date picker and time controls to dteEnd, drpHourEnd, and drpMinuteEnd.

Inside the end date card, we’ll add a label control and set the color to red, as shown in Figure 11-36. We then set the Text property of the label control to the formula that’s shown in the following:

If((DteStart.SelectedDate +

        Time(Value(DrpHourStart.Selected.Value),

             Value(DrpMinuteStart.Selected.Value),

             0)

    )

      >

    (DteEnd.SelectedDate +

        Time(Value(DrpHourEnd.Selected.Value),

             Value(DrpMinuteEnd.Selected.Value),

             0)),

    "End date must be greater than start date",

    ""

)

This formula calls the If function to test if the end date value exceeds the start date. If a user enters valid data, the label shows an empty string. Otherwise, it shows a message that indicates the error to the user.