Showing results for 
Search instead for 
Did you mean: 
Advocate III
Advocate III

Cascading dropdowns in a Customized Form - cannot edit record without refreshing SP list

I needed to implement cascading dropdowns in a customized form, where the lookup field is from a different SP list. Since there is no way of filtering Choices based on a field in a different list, I had to add a second data source and pull in the filtered values from there (aware of delegation limits, which won't be an issue as it's a small dataset that won't grow beyond 500 items):

Filter('BLISS Plans',Hub_x0020_ID_x0020_String = DataCardValue2.Selected.Value)

Now I needed to save the selected value back to the SP list. Since I was working in a customized form, I had to tweak the default properties as follows

If(FormMode.New = 1, {'@odata.type' : "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: DataCardValue4.Selected.ID,
Value: DataCardValue4.Selected.Title
}, ThisItem.BLISS_x0020_Plan_x0020_Title)

So that it would work in both, Edit and New mode. After many tries I figured out that I needed to set this as the default not only for the DataCardValue but as well for the DataCard itself.

This works like a charm now, with one caveat. If I create a new record and then edit it, I get the following error:




If I try to save again, the next error comes up:




If I refresh the SP list in the browser after creating a new item, I can edit it immediately with no error messages.


Is there any way to avoid this, by e.g. forcing a refresh after submitting a new item? How?



Community Support
Community Support

You should be able to filter the Lookup fields through the following formula:

Filter(Choices(SharePointList.LookupField), Value=DataCardValue2.Selected.Value)

And there is no need to load the other List.

Please check more information about the Choices() function:

Choices function in PowerApps


Further, to manually update a lookup field, the schema has been updated to:


LookupField:{Id:"" , Value:""}


Besides, for your situation, you may take a try to add a refresh(DataSource) under the OnSuccess property of the EditForm control.


See if that would help.




Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-micsh-msft but unfortunately no, I couldn't get it to work. Whatever I do with Filter(Choices()... it either gives me all existing options in the drop-down or none at all.


About manually updating a lookup field, in which property I would have to use

LookupField:{Id:"" , Value:""}

Is the issue maybe that my lookup column is already a lookup column in the SharePoint list, getting values from another list?


I tried to add Refresh(DataSource) in the OnSuccess event, but to no avail. I need to refresh the list manually in the browser before I can edit a newly created record. What is even worse, with Refresh(DataSource) in the OnSuccess event, the selected value in the lookup list is no longer written back to the list upon saving the record, neither in new nor edit mode. I rather keep it as is then.



Let me try and explain what I want to achieve with a very simple example.

1. List "Countries" has just one column with the Country, single line of text

2. List "Cities" has just two columns, the City (single line of text) and the Country (lookup from Countries list)

3. List "Offices" has some address columns and the lookup columns Country (from Countries list) and City (from Cities list).


In the customized form for the Offices list, the Country drop-down items are Choices(Offices.Country) and the City drop-down items are Choices(Offices.City). That works fine, but of course an incorrect country could be selected.


Once I select a country in the first dropdown, the list of cities in the second drop down should limit itself to those in the country selected.


Filter(Choices(Offices.City), Country.Value = DataCardValue3.Selected.Value)


doesn't work because Country.Value refers to the Country in Offices list, not Country in Cities list. I can't filter on that list without loading the table separately into the model.


I thought that perhaps the statement "Choices returns all columns of the foreign table" does not apply to lookup columns in that foreign table and tried with a Single Line of Text field populated with Country Value by a simple flow upon creation of a Cities item. But no joy. The only workaround I found is to load the countries and cities list as additional data sources and use a Distinct() clause to populate the drop-downs. Now I can perfectly use


Distinct(Sort(Filter(Cities,Country = Dropdown1.Selected.Value), City), City)


Which gives me just the list of cities in the selected country, sorted by City name, to select from.

And if I make these two fields (City and Country) in the Offices list single line of text, I don't even have to bother about saving the selected lookup Id and Value back to the list, it takes the string values just fine.


It works but may not be very elegant. I'd prefer somehow to make that Filter(Choices()) work properly, but how can I if Choices doesn't return the other fields (Country in this case) from Cities lookup table? Or am I missing a point here? Thanks!



Helpful resources

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.


Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (7,900)