cancel
Showing results for 
Search instead for 
Did you mean: 
darogael

Cascading dropdowns for single line of text

 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.

 Capture1.PNG

 

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.

 

 Capture2.PNG

 

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.

 

Capture3.png

 


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.

 

Capture4.png

 

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.

 

Capture5.PNG
Building the app

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

 

Capture6.PNG 

 

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

 

Capture7.PNG

 

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

 

Capture8.PNG 

 

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

 

Capture9.PNG

 

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.

Capture10.PNG

 

The new screen is by default titled as EditScreen1_1

 

Capture11.PNG

 

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.

 

Capture12.PNG 

 

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.

 

Capture12a.png 

 

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.

 

Capture13.PNG

 

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

 Capture14.PNG


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.

 

Capture14a.png 

 

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.

 

Capture15.PNG


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.

Capture16.PNG


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.

 

Capture17.PNG

 

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.

Capture18.PNG 

 

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.

 

Capture19.PNG 

 

You will now see a total of five data connections available

 

Capture20.PNG


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).

 

Capture21.PNG 

 

Or

 

Capture22.PNG

 

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.

 

Capture23.PNG

 

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

 

Capture24.PNG 

 

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.

 

Capture25.PNG 

 

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.

 

Capture26.PNG


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

 

Capture27.PNG


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.

Comments

Thank you for sharing the logic behind a 3-way cascading dropdown! I've bookmarked this in my browser!

I have no SharePoint but definitely give me more ideas of all these features.

TQ

i'm not sure i unserstand

for examle - why in the Device List the Manafcture is Single line of text and not lookup for the Manafcture list?

 

Nati

Hi NatiP,

I have been asked this question before. Hence I am providing the following reasons I have documented based on years of experience of building either new solutions or adding enhancements to existing ones.

 

Reason #1: Lookups are limited to lists or libraries existing on a site. This means that all your other lists/libraries have to remain in the same site which may work for some folks but not for everyone. However, this isn't a problem when adding a data connection to any list from any site-collection or web application or even a different source, one example being SQL table. Now, granted we can create a site column, however, that is still limited to a site-collection level.

 

Reason #2: You have the option to change a single line of text to multiple lines, choice, number, currency and date and time. Most of the time the data does transfer seamlessly although date and time can sometimes be challenging. However, when it comes to lookup columns you can only point it to a new list and column.

 

Reason #3: Create a lookup from a lookup is not possible. Most of the time this isn't necessary, however, it is important to mention this limitation.

 

Reason #4: To copy or move item or archiving lists which contain lookup columns can be challenging.

 

PowerApps is, well, a powerful tool and hence it's best we don't limit it's capabilities based on what SharePoint cannot do.

 

Hope this helps!

Anonymous

Thank you for your post,

 

i've tried to cascad the thired drop down from the second byt i got error.

 

my cas is: i've one list (single text) as

Capture.PNG

 

what i did, i filtered the Floor based on  selected Location as "Filter('Checklist MD',Title=Dropdown1.Selected.Value).Floor" and it's working fine.

 

when i tried to filter the Item Name based on selected Floor using "Filter('Checklist MD',Title=Dropdown1.Selected.Value && Floor=Dropdown2.Selected.Value).Item_x0020_Name" i got this eroor Untitled.png

 my case need to do this within one list and i cannot split them.

 

is there any way  to fix that ?

thanks in advance

 

 Has anyone been able to get more than one drop down working recently? The canvas seems to have some bugs when multiple drop downs are on a form. The last card grows in height by thousands of pixels when touched.

Anonymous
It is indeed a good tutorial. Cascading is working but in my case I have Numbers(Readings) that has to be stored with floors and equipment. How to store a value?

My sharepoint data is following

 

list 1(floors): one column with floor list

 

list 2(Equipment): Column1: Equipment list

                            : Column2: Lookup column to Floors in list one

 

list 3(Readings): Column 1: Lookup column to Equipment from list 2

                           Column 2 : Lookup column to Floors from list 1

                           Column 3 : Readings

 

I have created powerapps from list 3

In powerapps i put 2 dropdowns , ddfloors and ddequipments ; and one Text input.

 

Text input ;  Default: Parent.Default

                   Update: Value(DataCardValue11.Text) 

it is not showing any error and not storing a value in sharepoint list.

Can anyone hepl me with that?

 

 

Anonymous

Hello Dan,

 

I have used the knowledge you have shared regarding cascading drop downs. I have created simiar app which pulls data from 3 list. It works fine however, all the filters for dropdowns shows error every 10-15 minutes. If i try to refresh the data source, it doesnot refresh ask to "Try Again". If i open the app in browser it says Server issue. When the data refreshes after few minutes all works well. Not able to figure out what could be the issue. Please suggest.

 

 

RajeevC,

Try the Refresh() function with the SharePoint Data source.

Excellent guide! Thank you so much for sharing this.

Excellent, concise sequence of instructions. Thank you. 

Anonymous

Hi Daniel,

i have cascaded a drop down for multiple column on same sharepoint list and its working fine.

but after saving the data ...the cascading frop down is reseting to default value .and i am not able to see the saved data from cascading drop down......!

Hello,

 

Would you guide me how to realize to multi-select field accessory value? I have such requirement from customer.

Hi, I have pre-recorded data in sharepoint list (text) that flow from Kaizala. Can I display the text but at the same time enable my team to re-pickup the data from drop down ?