Cascading for a SharePoint Lookup field

(0) Share

Report

Posted on by johnjohn123

3,506

I have the following 3 SharePoint lists:-

1) Country List

2) City List. Contain a Lookup field named Country >> which reference the Country list.

3) Location List. Contain a Lookup field named City >> which reference the City list.

Now i am customizing the Location list SharePoint form using power apps. and since i am not storing the Country inside the Location list >> so added a Country ComboBox inside the form, as follow:-

So my question is how i can cascade the City list based on the country selection? currently the city list has the following inside the Items property:-

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report
@johnjohn123

Yes, if these are lookup columns, then change your Country dropdown Items property to:

AddColumns(Country As _country, "_cities", AddColumns( Filter(City, Country.Id = _country.ID) As _city, "_locations", Filter(Location, City.Id = _city.ID) ) )

This will produce a grouped list of all the items.

For your City dropdown, set the Items property to: yourCountryDropdown.Selected._cities

For your Location dropdown, set the Items property to: yourCityDropdown.Selected._locations

I hope this is helpful for you.

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@RandyHayes sorry but i did not understand your formula, as i never used grouped lists before. And why i need to use grouped list? I mean can not i just add a filter to the City combo-box's items property to only show the cities linked to the selected Country? am i missing something? thanks

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@RandyHayesi tried your approach/formual where i will get the cascade effect for the City list, but when i save the form the City value will always be null. any idea what is causing this?
Thanks

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@johnjohn123

Yes, you can add the Filter to the Items like you have if you want your app to make constant datasource trips and performance is not an issue.

The formula provided makes one trip to the datasource and gets the information needed for all the dropdowns without them needing to make more trips to the datasource after that.

You can do either way.

How are you saving the City value? And what kind of column is City in your datasource that you are storing into? Is it a lookup as well, or just a text column?

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@RandyHayesi tried your approach/formula where i will get the cascade effect for the City list, but when i save the form the City value will always be null. any idea what is causing this?

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@RandyHayesyes the City field inside the Location list is a lookup to the City list, as mentioned on my original question..

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@johnjohn123

Then what is the Update property of your datacard that contains that dropdown?

It will need to result in a record with an Id and a Value.

Ex. {Id: yourDropdown.Selected.ID, Value: yourDropdown.Selected.Title}

The use of Title above is dependent on how your lookup column is defined.

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@RandyHayesi did not change the Update property of the City Combo box, and it is as follow:-

Do i need to change it?

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@johnjohn123

Your original Items property of the dropdown was based on Choices. Choices is smart enough to know what kind of column you have and will only return the record type needed. So what you had would have worked fine.

At this point, we are basing on the actual list as you wanted more filtering control as you cascaded.

So the Update property needs to produce the proper Lookup column record and as mentioned, it would be:

{Id: DataCardValue2.Selected.ID, Value: DataCardValue2.Selected.Title}

Was this reply helpful? Yes No