cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Beat
Level 8

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
Community Support Team
Community Support Team

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.
Beat
Level 8

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.

Beat
Level 8

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
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 266 members 3,942 guests
Please welcome our newest community members: