How to get a dropdown IN a gallery to filter another dropdown IN the same gallery in the same row

[geon_bell]

Good day all,

I have an issue that I think I'm close to solving, but I'm not quite there yet. Any assistance will be greatly appreciated.

I have a gallery that would allow users to edit rows of simple exercise data. I've managed to have each dropdown default to the data currently stored in the DB.

My hurdle is, if I change the option in first dropdown, I'm not sure how to get the value behind my choice in order to pass to the second dropdown, in the same row, in order for the second dropdown to change it's choices (and to only change the options in the second dropdown  in the same row, and no others).

Example, if I change the first dropdown in the first row from 'Curls' (Pic 1) to 'Sit Ups' (Pic 2), the second dropdown, in that same row, should reflect new choices. Again, I'm not how to get the value of the choice from the first dropdown, to influence the second dropdown in the same row..

Any and all help would be greatly appreciated. If you require any further clarity on my question, please let me know.

[WarrenBelz · Accepted solution]

Hi @geon_bell ,

You can cascade these as far as you need - I did a model a little time ago below to show the principle

You only need two - I will give you the code on these first.

The text is simply the manufacturer name from the list.

From the same list, the first drop-down is - but you should not need any of this.

Sort(
    Distinct(
        Filter(
            Devices,
            ManufacturerName = ManName.Text
        ).DeviceType,
        DeviceType
    ),
    Result
)

More suited to your requirements will be the second drop-down - just ignore the  && ManufacturerName part.

Filter(
    Devices,
    DeviceType = DevType_1.Selected.Result && ManufacturerName = ManName.Text
).'Device Name'

Please see if you can translate this to your List and Field names - happy to help further.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[v-siky-msft]

Hi @geon_bell ,

I think first you need a table to store the correspondence between two dropdown values, ex:

Column1	Column2
Curls	Light
Squats	Bodyweight
Sit Ups	Heavy

If you have had such mentioned table, then you can set the Default property of the second dropdown as follows to cascade with the first dropdown. You can directly use DropDown1.Selected.ColumnName to reference the 1st dropdown selected value, since they are in the same row, then Use LookUp function to get the corresponding 2nd dropdown default value.

2nd DropDown.Default: LookUp(AboveTable, Column1=DropDown1.Selected.ColumnName).Column2

Hope this helps.

Sik

[WarrenBelz]

@v-siky-msft ,

I believe that is what I said in principle in my post.

@geon_bell , happy to elaborate further if required.

[geon_bell]

Hey @WarrenBelz , thank you very much for the reply.

I tried what you suggested and the second dropdown remains blank unfortunately.

Below are the queries behind both dropdowns:

Dropdown1 query: '[dbo].[Exercise]'

Dropdown2 query: Filter('[dbo].[ExerciseWeight]',Exercise_ID = Dropdown1 .Selected.Exercise_ID).ExerciseWeightType

Do you think I'm missing anything? Thanks again for your help so far.

[geon_bell]

Hey @v-siky-msft , I also tried your way via the lookup function, but I get an error. It says it expects a table value

Below is my attempt.

dropdown2: LookUp('[dbo].[ExerciseWeight]', Exercise_ID=DropDown1.Selected.Exercise_ID).ExerciseWeightType

Let me know if i'm missing anything please. Thanks again for the help thus far.

[WarrenBelz]

Hi @geon_bell ,

Please tag whoever you want to continue this thread with as three-way conversations only confuse everyone.

[geon_bell]

Hi @WarrenBelz , sorry about that.

I will continue with you if you have any other possible solutions. Your current solution makes sense and I would have tried something similar prior to writing this post, so I really don't know why the second dropdown in the gallery is still blank.

You have any suggestions by chance?

[WarrenBelz]

Thanks @geon_bell ,

This should not be hard to solve. Your first drop-down Items you have as

'[dbo].[Exercise]'

this is a table - what field name are you displaying in this - should this be

'[dbo].[Exercise]'.ExerciseWeightType

When you are typing in the formula below

Filter(
   '[dbo].[ExerciseWeight]',
   Exercise_ID = Dropdown1.Selected.Exercise_ID
   ).ExerciseWeightType

after typing in Dropdown1.Selected. (note second dot), what valid value comes up below?

I showed you the model I have to demonstrate this principle works on three levels including double filters, we just need to get the right syntax for yours.

[v-siky-msft]

Hi @geon_bell ,

My understanding is that the items of second drop-down box do not need to be changed, you just want to change its default value based on the first drop-down box selection.

@WarrenBelz 's workaround is to Change the Items of 2nd drop-down, as if each exercise in 1st drop-down has its own set of types.

Hence, I don't think we are talking about the same principle. 

But I came here to add a bit on @WarrenBelz 's workaround.

Maybe you need to add a disambiguity operator @ on the data source, since you are referencing another data source in the Gallery.

Filter(
   [@'[dbo].[ExerciseWeight]'],
   Exercise_ID = Dropdown1.Selected.Exercise_ID
   ).ExerciseWeightType

Hope this helps.

Sik