Dropdown filtered based on another dropdown where I have to split the selected value

[sasrsc]

These are all in SP Lists. 

Benefits List:
ID: 1   Title: xxxx, VendorTypes (single line of text): "1"
ID: 2    Title: yyyy, VendorTypes: "1,3"
ID: 3    Title: gggg, VendorTypes: "1,2,3"

Vendor Type List:
ID: 1 Title: Airline
ID: 2  Title: Hotel
ID: 3 Title: Car

I am displaying the titles for the Vendor Type List in a Dropdown.
The user makes a choice and therefore the ID is available to us.
I want to filter the Benefits Lists where the ID of the Vendor Type List is equal to one of the numbers in VendorTypes. I don't believe an "in" clause is correct because 1 would be in 1, 11, 12, 13, etc.. so I need to split the VendorTypes by "," and loop through the Result and see if that result is in Dropdown1.Selected.ID

I think I'm close with this but not quite right...

Filter(Benefits,ForAll(Split(VendorTypes,","),Result=Dropdown1.Selected.ID))

[WarrenBelz · Accepted solution]

Hi @sasrsc ,

Is this a single or multiple selection ? You have also caused yourself some extra work using the ID as it is a number you are trying to find in Text and also unless you use Split, you would also find a single number in a larger one (1 is in 11) - so single select

Filter(
   Benefits,
   Text(Dropdown1.Selected.ID) in 
   Split(
      VendorTypes, 
      ","
   ).Result
)

Multiple Select

Ungroup(
   ForAll(
      Dropdown1.SelectedItems As aSel,
      Filter(
         Benefits,
         Text(aSel.ID) in 
         Split(
            VendorTypes, 
            ","
         ).Result
      )
   ),
   "Value"
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[sasrsc]

@WarrenBelz Single selection. This works... and it handled a scenario with 1,11, etc.. properly.

Filter(
    Benefits,
    Active=true &&
    Text(DrpType.Selected.ID) in (Split(
        VendorTypes,
        ","
    ).Result)
)

I was trying to avoid Choice columns as that also creates it's own set of annoyances.
I can change my setup if you feel strongly there's a better way.
What would you have done?

[WarrenBelz]

Hi @sasrsc,

My blog on data structure may explain a bit here. I do not use complex field types at all other than multi-choice if I need to store them as a Table. I also keep any potential "key" or "linking" fields to the same data type in both lists.

[sasrsc]

I posted on your blog page because I get what you are saying but I'm curious as to your strategy when you have a 1:n relationship...see your blog. As always I appreciate your thorough responses. You always make me think!