cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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:

 

error1.png

 

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

 

error2.png

 

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?

 

 

3 REPLIES 3
Highlighted
Community Support
Community Support

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

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.

 

Regards,

Michael

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.
Highlighted
Advocate III
Advocate III

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

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.

Highlighted
Advocate III
Advocate III

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

@v-micsh-msft

 

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

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (3,288)