Cascading Dropdowns on a SharePoint List is still way to difficult to configure

[Peter1234]

I have this simple scenario with 3 lists
* Vehicles

* VehicleTypes
* VehicleBrands

In VehicleTypes i have
* Automobiles
* Motorcycles

In vehicleBrands i have following data
* Ford,Automobiles
* Cadilac,Automobiles
* Harley-Davidson,Motorcycles

(the vehicle type has been added to the vehiclebrand list)

In the vehicles list i have then added
* vehicle 1, Automobiles, Ford, ...
* Vehicle 2, Motorcycles, Harley-davison, ...

(the vehiclebrand and vehicletype list has been added here)

When I generate a powerapp for Vehicles, it will give me two dropdown but they are of course not linked

Now I want to enable that when a new vehicle is added, you can choose first between adding a automobile or a motorcycle and then get the choice of the brand depending on the first selection.

The datacard that gets added has following databinding

Choices(Vehicles.VehicleBrand)

How can i write a filter statementhere that limits the brands to the selected vehicle type?

I have tried adding a separate combo and binding it to the VehicleBrand list separately and that works, but i can't figure out how to feed that selection back to the card and it just doesn't feel like this is the way it should be done.

I have tried in so many ways... this is such a common scenario... can somebody help me out?

[Drrickryp]

Hi @Peter1234,

You are quite right.  This is a very common scenario.  The reason you are having problems is that IMHO, your model is flawed. 

I would start over and avoid the use of Lookup columns in your lists and instead make the foreign key a Number type column. IE. use the ID from the One side of the relationship as the foreign key in the Many side. When you create your app, use Dropdown controls instead of Comboboxes, they are much simpler to work with.

In PowerApps, I can picture you having a screen containing two dropdown controls and a gallery.  The first dropdown control would have VehicleType list as the Items property.  The second Dropdown control would have the Brands list as the Items property filtered on the first dropdown as follows: 

Filter(Brands,TypeID=Dropdown1.Selected.ID)

The  Gallery would have It's items property filtered on the second Dropdown control as follows:

Filter(Vehicles ,BrandID = Dropdown2.Selected.ID)

You could then have more details from the Vehicle list in your gallery.  For  more information regarding how to structure the database lists behind your app, see my posts in the Community Blog.  PowerApps-Community-Blog

regarding relational database design in PowerApps.

[Peter1234]

Hi Drrickryp,

Thanks for your quick response, but your design is directed towards a sql database model, not towards a SharePoint list. Sure, It will work for a SharePoint list but it's not a good idea and i'll explain why.

If I do it your way, I can't create views for Automobiles, Motorcycles, etc... in the Vehicles list. Your design also means brands will be represented by numbers and not by the actual name. This is not understandable for a user navigating to the vehicles list.

If I were to build my solution in a database, instead of a SharePoint list, I would fully agree with your datamodel, but in SharePoint my model is the only way to really do this otherwise I will have to create heavily customized views too if the list is to be any where near functional in SharePoint.

I managed meanwhile to create a formula that will work as far as showing the right items in the brand combobox:
Filter(VehicleBrand_1, VehicleType.Value = DataCardValue5.Selected.Value)
=> Where VehicleBrand_1 is a reference to the VehicleBrand list

But it has two issues:
* When the type is changed, the brand is not being reset
* When submitting the form, it does not save the brand

The second is issue is caused by the fact that I use VehicleBrand_1 as a recordsource which doesn't sit well with the Submitform function that is executed when saving the form. I don't know if the parameters of this method can be changed somewhere?

Best regards,

Peter

[Peter1234]

The solution to this problem could be very simple.

Right now the formula for the items of Brand is:

Choices(Vehicles.VehicleBrand)

If I could turn this into filter:

> Filter(Choices(Vehicles.VehicleBrand), VehicleType.Value=DataCardValue7.Selected.Value)

Where DataCardValue7.Selected.Value is the value of the selected vehicle type, al my problems would be solved.

But it doesnt't work, even hardcoding this will not work:

Filter(Choices(Vehicles.VehicleBrand), VehicleType.Id=1)

>Returns all vehicle brands!!!!

This seems strange to me... the field is being enumerated and the formula is evaluated as correct, so is this a bug?

[v-xida-msft]

Hi @Peter1234,

Could you please share a bit more about the VehicleType column in your VehicleBrand SP list? Is it a LookUp type column?

I assume that the VehicleType column is  LookUp type column in your VehicleBrand SP list, and the VehicleBrand column is also a LookUp type column in your Vehicles SP list, is it true?

Based on the formula that you provided, I think there is something wrong with it. The result the Choices(Vehicles.VehicleBrand) formula returned only contains Id and Value property, it does not contain VehicleType property.

I have made a test on my side, please modify your formula as below:

Set the OnVisible property of the first screen of your app to following:

ClearCollect(BrandsCollection, VehicleBrand_1) /* <-- VehicleBrand_1 represents your VehicleBrand list*/

Set the Items property of the Combo Box control within the VehicleBrand Data card in your Edit form to following:

 Filter(
       Choices(Vehicles.VehicleBrand), 
       Value in Filter(BrandsCollection, VehicleType.Value = DataCardValue7.Selected.Value).BrandColumn
)

Note: The BrandColumn represents the column in your VehicleBrand SP list, which used to store the brand value. The DataCardValue7 represents the Combo Box control within the Vehicle Type Data card in your Edit form.

Best regards,

Kris

[Peter1234]

Hi v-xida-msft ,

Yes both vehicletype and vehiclebrand are lookup columns.

I have tried your formula but it doesn't work, clicking the combo box doesn't return any results.

The formula is supposed to be this (i think):

Filter(
Choices(Vehicles.VehicleBrand),Value in Filter(BrandsCollection, VehicleType.Id = DataCardValue7.Selected.Id
).VehicleBrand)

But it doesn't accept it, i can only create following one

Filter(
Choices(Vehicles.VehicleBrand),Value in Filter(BrandsCollection, VehicleType.Id = DataCardValue7.Selected.Id
).VehicleType)

also tried:

Filter(
Choices(Vehicles.VehicleBrand),Value in Filter(BrandsCollection, VehicleType.Id = DataCardValue7.Selected.Id
).ID) 

Neither works.

Thanks for the effort.

Best regards,

Peter

PS: The BrandColumn represents the column in your VehicleBrand SP list, which used to store the brand value.... => this is the ID column of VehicleBrand SP list, no?

[v-xida-msft · Accepted solution]

Hi @Peter1234,

Could you please share a bit more about the data structure of your VehicleBrand SP list?

I have made a test on my side, and don't have the issue that you metioned.

The BrandColumn column is not the ID column in your VehicleBrand SP list, it just be the column (may be Single line of text type column) which used to store the brand value (e.g. Ford, Cadilac, etc).

If the BrandColumn column is a Single line of text type in your VehicleBrand SP list, I think the formula I provided above is proper.

Set the OnVisible or OnStart property of the first screen of your app to following:

ClearCollect(BrandsCollection, VehicleBrand_1) /* <-- VehicleBrand_1 represents your VehicleBrand list*/

Set the Items property of the Combo Box control within the VehicleBrand Data card in your Edit form to following:

Filter(
       Choices(Vehicles.VehicleBrand), 
       Value in Filter(BrandsCollection, VehicleType.Value = DataCardValue7.Selected.Value).BrandColumn
)

Please save and re-publish your app, then re-load it again, then check if the issue is solved.

Note: The BrandsCollection would be populated with your VehicleBrand SP list records only when the first screen of your app is visible or your app is started.

Best regards,

Kris

[Peter1234]

Hi v-xida-msft,

I owe you an appology... your formula indeed works, I changed it to

Filter(Choices(Vehicles.VehicleBrand);Value in Filter(BrandsCollection; VehicleType.Value = DataCardValue7.Selected.Value).Titel)

Not sure why I didn't find that last week, maybe I had been looking for too long to see what was just in front of me.

Thanks a lot for your solution!!!

As for the structure of my data, it was quite simple:

VehicleType is just a standard list (no columns added)

VehicleBrand is a standard list with one column added: a lookup column to VehicleType

Vehicle is a standard list with two columns added: a lookup column for VehicleType, the other a lookup column for VehicleBrand

You can set this kind of thing up out of the box in 5 minutes, and I think a lot of people do this kind of thing

So now when a user enters a vehicle, he selects the type (motorcycle or automobile), then the user chooses a brand, but he/she only gets the choose between motorcycle brand or automobile brands.

On could split this further up to three or four levels.

The only thing I don't like in your solution is having to cache the brands... If a solution for that could be found, it would be good enough and it would just require someone to explain in this sort of thing in the documentation off PowerApps.

I can't imagine, I'm the only one that does this kind of thing.

Best regards and thank you again,

Peter