Cascading dropdowns for single line of text

[darogael]

 Introduction
This blog walks you through the process of how you can create cascading dropdowns in a PowerApp and save it to a  single line of text column. In this blog a SharePoint list with three single line of text columns has been used to save the item. In addition, three separate lists are used for the cascading dropdown data.

Scenario
I need a PowerApp where I can order accessories for a device. The end user should have the options to first choose the Manufacturer>Device>Model and then finally the accessory. Once the accessory is selected all of them are saved to a SharePoint list item where each column type is a single line of text.

Requirements
Five SharePoint SharePoint lists, 1 of them is to save the information and the remaining four is for lookup. Here is the description of each of them.

Manufacturer list - This list is titled as PAMFR which stands for Power Apps Manufacture. Here is a screenshot of all the columns available in this list. This list is already populated with items that have manufacture names and it's respective ID number.

 

Device list - This list is titled as PADevices which stands for Power Apps Devices. Here is a screenshot of all the columns available in this list. This list is already populated with items that have manufacture and device names. For one manufacturer there will be several devices.

 

Model list - This list is titled as PAModel which stands for Power Apps Model Here is a screenshot of all the columns available in this list. This list is already populated with items that have manufacture,  device and model names. For one device there will be several models.

Accessory list - This list is titled as PAAccessory which stands for Power Apps Accessory. Here is a screenshot of all the columns available in this list. This list is already populated with items that have model and Accessory. For one model there will be several accesories.

Cascading list - This is a demo list using which an app is created and all the contents are stored in the columns as shown below.

Building the app

Go to the Cascading dropdown SharePoint list and click on PowerApps and then Create an app as shown below

 

Give you app a name, for this demo the name provided is 'Cascading dropdown demo'. Then click Create.

Below is a screenshot of a window that will temporarily appear while the app is being built.

 

You will also see this helpful quick tour. You can either skip or go through the tour.

By default you will have the BrownScreen1, DetaileScreen1 and EditScreen1. Click on the three dots or the ellipses next to the EditScreen1 and click on Duplicate screen.
Note: It's always a good practice to first make a copy of an existing screen that is working condition and then make changes to the new copy.

The new screen is by default titled as EditScreen1_1

To move the columns simply drag and drop the columns that appear on the right. Once I've made my changes, below is a screenshot of the column order.

 

Now, we have completed all the preliminary work needed to start building the cascading dropdown. Click on the Manufacturer data card and rename it to txtManufacturer. The rename option is available in the Content menu on the left side of the ribbon.

 

Do the same for the Model, Device and Accessory data cards and rename them to txtModel, txtDevice and txtAccessory respectively. Next, select the Manufacturer Data card and make sure the entire section is selected i.e. it includes the txt Manufacturer as well. On the right, click on Advanced, you should see the option to unlock and change properties. Go ahead and unlock.

Once it is unlocked, click on the Manufacturer card only (as shown below) and delete it

 

Here is a screenshot of what it looks like once it is deleted. You can ignore the two warnings for now, the video at the end shows how to remove them.

 

Click on Manufacturer data card and make sure it all selected (as shown below). Next, click on Controls which is available in the Insert menu's ribbon and finally click on the Drop down option.

After moving the new control to it's desired location, here is what the new datacard looks like. Notice the card name has been changed to ddManufacturer.

Follow the same process and replace the existing text controls of Model, Device and Accessory to new Drop down controls. Make sure you rename the new drop down controls to ddModel, ddDevice and ddAccessory respectively. Below is a screenshot of what it should look like.

Adding new data sources
We now need to add new data sources to get the desired drop down choices. To add a data source click on the '+' sign.

 

Next, add your URL and click on Go. The URL should include the site as well. Once you have selected all your lists, click on Connect.

 

You will now see a total of five data connections available

Adding drop down values
Click on Manufacturer and change the Items' formula to PAMFR.MFR i.e. the list name.column name.
Note: To make sure you don't get any duplicates you can change the formula to Distinct(PAMFR , MFR).

 

Or

Here are the formulas to add drop down values for the remaining data cards.

Device

SortByColumns(Filter(PADevices, Title = ddManufacturer.Selected.Value), "Device", SortOrder.Ascending)

Model

Filter(PAModel, Title = ddDevice.Selected.Value && MFR = ddManufacturer.Selected.Value)

Accessory

SortByColumns(Filter(PAAccessory, Title = ddModel.Selected.Value), "Accessory", SortOrder.Ascending)

Populating the single line of text

Now that the cascading drop down is working, we need to save all the values to the original single of text fields that connect to the SharePoint list columns. Hence we now need to add those columns back and assign default values to them, the values are from the drop down controls.

First, add those single line of text fields back to the form. On the right you can click on icon that looks like an eye for Accessory, Device, Model and Title (which is also Manufacturer). Below is a screenshot.

As you see, there are now four new data cards added. These are the single line of text fields that come from the SharePoint list

 

Next, unlock each of these new columns, however, only change the default values of each. For example, click on the new Manufacturer field i.e. the single line of text one and unlock it.

 

Once it is unlocked, change the default value from 'Parent.Default' to 'ddManufacturer.Selected.Value'. After assigning the value, scroll down and change the Visible to 'false. This way you do not see both of them in the form, however, the selected value does get saved to the list column.

Following are the steps for the remaining columns and below are the values.

Device - ddDevice.Selected.Value
Model - ddModel.Selected.Value
Accessory -ddAccessory.Selected.Value

Finally, you can delete the original EditScreen1 and rename the new EditScreen1_1 to EditScreen1

Here's a video which walks you through the same steps described above and ends with a demo. This video also shows how you can remove the warning signs.