Customising SharePoint list forms with PowerApps – part two
Microsoft 365 Power Apps SharePointThis series of articles will show you how to customise a SharePoint list form using PowerApps:
- part one – customise a list form, create new, edit, and view forms, and wire up the buttons (read now)
- part two – create cascading drop-downs (read below)
- part three – create tabs so long forms can be broken into sections (read now).
Part 2 – Cascading drop-downs
From the research I have done and the tutorials by Shane Young and others I have worked through, the consensus at this point is that SharePoint lookup columns “do not play well” when customising a form in PowerApps.
I have tried various combinations to use filter, sort and distinct functions on lookup columns without success. The approach seems to be if you have a second list that would normally be your source for the lookup column to uses a text column and let PowerApps do all the magic of pulling the list into a drop-down control, then write the chosen selection into the list’s text field.
This is very similar to how you would have done it in the old days with InfoPath. In InfoPath you would create a Data Connection to your lookup list and connect it to the drop-down.
In this example I have a second list called ‘Australian City’ which gives me State and City. City is unique but State is not.
The list I am customising in PowerApps has two ‘Single line of text’ fields for State and City. To reiterate these are text columns and not lookup columns.
If you try to add a drop-down control to a screen, it will be added ‘outside’ the form. So instead select your Ffrm in the left-navigation and then add the two fields from your list.
Now that they are on the form as a datacard, unlock the datacard and then delete the text control.
Add a drop-down control in its place. You may need to re-align the error message and resize the card to suit. Change the control’s name to something more sensible, in my case I choose CityDropdown1.
The last red ‘x’ will be highlighting the ‘Update’ function on the datacard, as this will be trying to update the original control’s name. Change to ‘Update = CityDropdown1.Selected.Value’
The next part is to wire up the drop-down control to pull the actual list of cities from the second list.
Select the drop-down control and from the properties pane choose your data source. If you have not made a connection to your lookup list then choose the ‘Add a data source’ button.
When you play in Preview, you will see the unfiltered list of cities in your drop-down control.
Repeat the same steps for your state control.
These controls are now pulling in the data and we just need to filter the list of states to remove duplicates and then cascade the drop-downs, so that when choosing the state of NSW, it filters the cities to be only the cities in NSW.
Drag the state control above the city control as the end user will choose the state first.
To filter the state drop-down control:
Items = Sort(Distinct(‘Australian City’.State, State), Result, Ascending)
As I mentioned in the last article, PowerApps cache and sometimes when you make a change and then try it in Preview mode, you may find your last change is not applied. If this happens to you, when you paste in the above line, simply copy that last line into your clipboard, delete the ‘Items’ line, preview, then re-paste back into Items and preview again. There seem to be a few quirks that we have to live with at this point in time.
To filter our cities control, in ‘Items’ add the following :
Filter(‘Australian City’,State=StateDropdown1.Selected.Value)
Publish to your list, refresh twice and you have a cascading drop-down.
