Filter Gallery against Combo box that allows for multiple selections

[sasrsc]

How to filter a gallery based on multiple selections in a combo box.

Currently my filter does this 

If(IsEmpty(CmbFilterSubject.SelectedItems),true,true)

If the CmbFilterSubject.SelectedItems is not empty then what I really want to do is filter my gallery ...
So in my SP List I stored SubjectMatterID as a text variable which contains the concat of whatever items are selected (using the ID value) so it could be 1, or 1,5,10, or 3,7, etc...
These are the ID values.
When they select the items in the combo box I can see the output in a label 

Concat(CmbFilterSubject.SelectedItems,ID&",")

 and it appears as 7,9, etc... 
So I only want to show items in my gallery where the SubjectMatterID contains a 7 or a 9 in this case.
I know the CmbFilterSubject.SelectedItems is a table. 
I know the SubjectMatterID is a text variable.
I've done similar things before but not exactly the same and for the life of me I can't figure it out.

[WarrenBelz · Accepted solution]

Hi @sasrsc ,

Replace xxxx with the output (Value/Result/FieldName) of your combo box

Ungroup(
   ForAll(
      cmbSelectedItems As aData,
      Filter(
         SPListName,
         aData.xxxx in SubjectMatterID
      )
   ),
   "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]

This is the total code so where would I put this UnGroup...?
I'm already doing a bunch of filtering as you can see...SubjectMatterID is in Regulations which is used in the "with" stmt below.
This is one of the hardest things for me to grasp in Power Apps combining statements into one and where things go.

With(
    {
        _l: Filter(
            Regulations,
            If(
                IsBlank(ComboBox1.Selected),
                true,
                TrackingOwnerID = ComboBox1.Selected.ID
            ) && If(
                IsBlank(TextInput1.Text),
                true,
                TextInput1.Text in Title
            ) && If(
                IsEmpty(CmbFilterStatus.SelectedItems),
                true,
                Status in Concat(
                    CmbFilterStatus.SelectedItems,
                    Title & ","
                )
            ) && If(
                IsEmpty(CmbFilterSubject.SelectedItems),
                true,
                true
            ) && If(
                DrpSeverity_1.Selected.Title = "All",
                true,
                ImpactSeverityID = DrpSeverity_1.Selected.ID
            )
        )
    },
    ForAll(
        Sequence(CountRows(_l)),
        Patch(
            Last(
                FirstN(
                    _l,
                    Value
                )
            ),
            {RowNo: Value}
        )
    )
)

[WarrenBelz]

Hi @sasrsc ,

You can also do a man to one the way you have with your top filter, but I will give you a structure based on what I posted

With(
   {
      _l: 
      Filter(
         Regulations,
         (
            Len(ComboBox1.Selected.ID) = 0 ||
            TrackingOwnerID = ComboBox1.Selected.ID
         ) && 
         (   
            Len(TextInput1.Text) = 0 ||
            TextInput1.Text in Title
         ) && 
         (
            IsEmpty(CmbFilterStatus.SelectedItems) ||
            Status in 
            Concat(
               CmbFilterStatus.SelectedItems,
               Title & 
               ","
            )
         ) && 
         (
            IsEmpty(CmbFilterSubject.SelectedItems) ||
            (
               DrpSeverity_1.Selected.Title = "All" ||
               ImpactSeverityID = DrpSeverity_1.Selected.ID
            )
         )
      )
   },
   With(
      {
         _2:
         Ungroup(
            ForAll(
               cmbSelectedItems As aData,
               Filter(
                  _1,
                  aData.xxxx in SubjectMatterID
               )
            ),
            "Value"
         )
      },
      ForAll(
         Sequence(CountRows(_2)),
         Patch(
            Index(
               _2,
               Value
            ),
            {RowNo: 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]

Wow Warren - I must admit I'm not sure I get this 100% but it works mostly. I did have to tweak a couple of things but you got me started. 

Here's the current code which still isn't quite right because when I somehow need to set the combo boxes to empty when I display the page (onVisible). I have nothing in the defaultSelectedItems (allows multiple selections BTW). 
I tried doing a Reset(CmbFilterStatus) in the onVisible which erases the previous values which is good but the filter doesn't display rows until I make a selection to show value rows or make a selection and then delete it.

$"Subject empty={IsEmpty(CmbFilterSubject.SelectedItems)}"

$"Subject blank={IsBlank(CmbFilterSubject.SelectedItems)}"

I check for IsEmpty and IsBlank on the CmbFilterSubject on the page and when it first shows the 
isEmpty=false
isBlank=true
Then when I populate values it changes to 
isEmpty=false
isBlank=true
Then when I erase the selectedItems it changes to...
IsEmpty=true
IsBlank=false
Obviously I'm need to somehow make the combo box be read as Empty when I first enter the page. I tried adding {} to the defaultSelectedItems and that didn't work.

The full refined code (thanks to your help is)

With(
    {
        _l: Filter(
            Regulations,
            (Len(TextInput1.Text) = 0 || TextInput1.Text in Title) && (Len(CmbFilterOwner.Selected.ID) = 0 || TrackingOwnerID = CmbFilterOwner.Selected.ID) && (IsEmpty(CmbFilterStatus.SelectedItems) || Status in Concat(
                CmbFilterStatus.SelectedItems,
                Title & ","
            )) && (IsEmpty(CmbFilterSubject.SelectedItems) || (DrpSeverity_1.Selected.Title = "All" || ImpactSeverityID = DrpSeverity_1.Selected.ID))
        )
    },
    With(
        {
            _2: If(
                IsEmpty(CmbFilterSubject.SelectedItems),
                _l,
                Ungroup(
                    ForAll(
                        CmbFilterSubject.SelectedItems As aData,
                        Filter(
                            _l,
                            aData.ID in SubjectMatterID
                        )
                    ),
                    "Value"
                )
            )
        },
        ForAll(
            Sequence(CountRows(_2)),
            Patch(
                Last(
                    FirstN(
                        _2,
                        Value
                    )
                ),
                {RowNo: Value}
            )
        )
    )
)

Everything works NOW but only after I added items to the combo box and then delete them again to handle No selected items properly. 

[WarrenBelz]

Hi @sasrsc ,

Yes - I simply inserted the code in your exiting item - if you need to test for no input  try this instead - also note the small change at the bottom to get rid of a couple of rows

With(
   {
      _l: 
      Filter(
         Regulations,
         (
            Len(ComboBox1.Selected.ID) = 0 ||
            TrackingOwnerID = ComboBox1.Selected.ID
         ) && 
         (   
            Len(TextInput1.Text) = 0 ||
            TextInput1.Text in Title
         ) && 
         (
            CountRows(CmbFilterStatus.SelectedItems) = 0 ||
            Status in 
            Concat(
               CmbFilterStatus.SelectedItems,
               Title & 
               ","
            )
         ) && 
         (
            CountRows(CmbFilterSubject.SelectedItems) = 0 ||
            (
               DrpSeverity_1.Selected.Title = "All" ||
               ImpactSeverityID = DrpSeverity_1.Selected.ID
            )
         )
      )
   },
   With(
      {
         _2:
         If(
            CountRows(cmbSelectedItems) = 0,
            _1,
            Ungroup(
               ForAll(
                  cmbSelectedItems As aData,
                  Filter(
                     _1,
                     aData.ID in SubjectMatterID
                  )
               ),
               "Value"
            )
         )
      },
      ForAll(
         Sequence(CountRows(_2)),
         Patch(
            Index(
               _2,
               Value
            ),
            {RowNo: 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]

Thanks again Warren. I had to make a few tweaks but your concepts helped  steer the way. It's odd because the combo box on initial load isBlank=true and isEmpty=false and the CountRows is blank. When I add a selectedItem and then delete it, the isBlank=false, the isEmpty=true and the CountRows is 0. So it's that initial state that was throwing me off. I did do a Reset on the combo box when isVisible and that's necessary because I don't want it showing the previous selectedItems. There's nothing in the defaultSelectedItems. So I would have thought isEmpty would be true. Alas... move on ... with some tweak I have it working. I also noted your few tweaks to the code at the bottom but I haven't wrapped my head around that yet. Sometimes we just trust and go with it as much as I'd like to know it all 😉

Here's the revised code....

With(
    {
        _l: Filter(
            Regulations,
            (Len(TextInput1.Text) = 0 || TextInput1.Text in Title) 
            && 
            (Len(CmbFilterOwner.Selected.ID) = 0 || TrackingOwnerID = CmbFilterOwner.Selected.ID) 
            && 
            ((IsEmpty(CmbFilterStatus.SelectedItems) || IsBlank(CmbFilterStatus.SelectedItems)) || Status in Concat(
                CmbFilterStatus.SelectedItems,
                Title & ","
            )) 
            && 
            (DrpSeverity_1.Selected.Title = "All" || ImpactSeverityID = DrpSeverity_1.Selected.ID)
        )
    },
    With(
        {
            _2: If(
                (IsBlank(CmbFilterSubject.SelectedItems) || IsEmpty(CmbFilterSubject.SelectedItems)), 
                _l,
                Ungroup(
                    ForAll(
                        CmbFilterSubject.SelectedItems As aData,
                        Filter(
                            _l,
                            aData.ID in SubjectMatterID
                        )
                    ),
                    "Value"
                )
            )
        },
        ForAll(
            Sequence(CountRows(_2)),
            Patch(
                Index(
                    _2,
                    Value
                ),
                {RowNo: Value}
            )
        )
    )
)

[WarrenBelz]

Hi @sasrsc ,

The bottom bit is just using the Index() function which is relatively new - saves First(LastN . . .). I also find it interesting that CountRows() does not always work for you as I have never had a reported issue with it. You can also use Len(ComboBox.Selected.xxxx) here.