Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Filter gallery with multiselect combobox

(1) ShareShare
ReportReport
Posted on by 40
I have a gallery that is being filter by several criteria. All data comes from a Sharepoint list. Here are filters 
  • Risk block number - this is multi select combobox with values 1to16 - Default 1 to 6 are selected 
    • Corresponding SP column is choice column with no multi select allowed, each risk can have only one number from 1 to 16
  • Risk Owner - Combobox with single Person select - Default selection none
  • Risk Site - Combobox with single select - Default selection none
  • Risk Status - Combobox with single select - Default selected value - 'Approved'
  • Keyword - Search for text entered in box in several columns - Default text none
 
Here is the Items property of Gallery 
Sort(
    Filter(
            Risk_Master,
            (
                (IsBlank(cmbListRiskBlock.SelectedItems) || IsEmpty(cmbListRiskBlock.SelectedItems) || Block_No.Value in cmbListRiskBlock.SelectedItems) &&
                (IsBlank(cmbListRiskOwner.Selected) || IsEmpty(cmbListRiskOwner.SelectedItems) || Issue_Owner.DisplayName = cmbListRiskOwner.Selected.Result) && 
                (IsBlank(cmbListRiskSite.Selected) || IsEmpty(cmbListRiskSite.SelectedItems) || Site.Value = cmbListRiskSite.Selected.Value) && 
                ((cmbListRiskStatus.Selected.Value = "All Status") || (IsBlank(cmbListRiskStatus.Selected) || IsEmpty(cmbListRiskStatus.SelectedItems) || Issue_Status.Value = cmbListRiskStatus.Selected.Value)) && 
                (IsBlank(txtKeyword.Value) || txtKeyword.Value in Title & Resolution_Month & Resolution_Year & Funding_Type.Value)
            )
    ),
    Value(Block_No.Value),
    SortOrder.Ascending
)
My problem is 
  • Initial load of data in gallery is working fine 
  • When I start removing default selected risk block number one by one, gallery gets correctly updated with each deselection 
  • I reach point where nothing is selected in multi-select box - Gallery correctly shows all issue 
  • Now I select 7 in block number - gallery goes blank nothing shows (I know there are items in SP list with block no 7)
  • I select 8, gallery stays blank
What am I doing wrong ?
 
 
  • csuk Profile Picture
    csuk 40 on at
    Filter gallery with multiselect combobox
     
    Thank you for your reply and answer. For me, this field will never have any values other than number 1 to 16. So I think I should be good (fingers crossed)
  • WarrenBelz Profile Picture
    WarrenBelz 145,422 on at
    Filter gallery with multiselect combobox
    Hi @csuk,
    I am just noting I have in the past found searching a text field with a number at times not always reliable (although it sees to work in your case). There is nothing I know to address it other than not searching on only numbers (you could have your values as B1, B2, B3 etc)
  • csuk Profile Picture
    csuk 40 on at
    Filter gallery with multiselect combobox
     
    That seems to have solved the problem. I just have one more question before mark your answer as solution. 
     
    What problems you are referring to? Is there anything I can do to avoid those problem?
  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 145,422 on at
    Filter gallery with multiselect combobox
    Try
    BlockNo.Value in cmbListRiskBlock.SelectedItems.Value
    Although I suspect that searching only on a numeric character (as I have found) is at times problematic.
     
    Please click Does this answer your question 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 a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    LinkedIn    Buy me a coffee
  • csuk Profile Picture
    csuk 40 on at
    Filter gallery with multiselect combobox
     
    The Block_No is choice field in SharePoint list. Multiple selections are not allowed. The choices are - 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 ,13, 14, 15, 16
     
    Block_No is classic combobox in PowerApps with multiple selections allowed, It's items property is - Choices([@Risk_Master].Block_No)
  • WarrenBelz Profile Picture
    WarrenBelz 145,422 on at
    Filter gallery with multiselect combobox
    Hi @csuk,
    What type of field is BlockNo and what is the actual (in Text please) Items of cmbListRiskBlock ?
  • csuk Profile Picture
    csuk 40 on at
    Filter gallery with multiselect combobox
    Hi @WarrenBelz thank you for you reply. 
     
    I have increased Data Row Limit to 1000. 
     
    Yes, resolution_month and resolution_year are text fields in SP list.
     
    I tried your code. It gives me same results. Initially, gallery shows list of all 'Approved' risk items from block no 1 to 6. Once I start deselecting block no, gallery updates properly. Once I deselect all pre-selected block no, gallery shows all risk items. But when I select any block_no after it, gallery does not show anything. 

    I tested little more. I deselected all Block_No value and started playing with other dropdown filters and various combinations of them. Gallery filter correctly each time. The issue happens only when I select something in Block_No dropdown.  
  • WarrenBelz Profile Picture
    WarrenBelz 145,422 on at
    Filter gallery with multiselect combobox
    Firstly, I will try to get some Delegation capacity and field type structure into this. I have to assume that Resolution_Month and Resolution_Year are Text fields, otherwise this will require more alteration
    With(
       {
          _Data:
          Filter(
             Risk_Master,
             (
                Len(cmbListRiskOwner.Selected.Result) = 0 || 
                Issue_Owner.DisplayName = cmbListRiskOwner.Selected.Result
             ) && 
             (
                Len(cmbListRiskSite.Selected.Value) = 0 || 
                Site.Value = cmbListRiskSite.Selected.Value
             ) && 
             (
                cmbListRiskStatus.Selected.Value = "All Status" || 
                Len(cmbListRiskStatus.Selected.Value) = 0 || 
                Issue_Status.Value = cmbListRiskStatus.Selected.Value
             )
          )
       },
       Sort(
          Search(
             Filter(
                AddColumns(
                   _Data,
                   BlockNo,
                   BlockNo.Value,
                   FundingType,
                   Funding_Type.Value,
                   LRPFunding,
                   Long_Range_Plan_Funding.Value
                ),
                Len(cmbListRiskBlock.Selected.Value) = 0 || 
                BlockNo in cmbListRiskBlock.SelectedItems
             ),
             txtKeyword.Value,
             Title,
             Resolution_Month,
             Resolution_Year,
             FundingType,
             LRPlanFunding
          )
       )
    )
     
    Please click Does this answer your question 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 a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    LinkedIn    Buy me a coffee
  • csuk Profile Picture
    csuk 40 on at
    Filter gallery with multiselect combobox
    Hi @WarrenBelz - this is classic combobox. 
     
    Hi @phaaland I have simplified the filter code. It is now as follows. Please note that I am trying to search multiple columns with whatever user enters in textbox. 
     
    Sort(
        Filter(
                Risk_Master,
                (
                    (Len(cmbListRiskBlock.Selected.Value)=0 || Block_No.Value in cmbListRiskBlock.SelectedItems) &&
                    (Len(cmbListRiskOwner.Selected.Result)=0 || Issue_Owner.DisplayName = cmbListRiskOwner.Selected.Result) && 
                    (Len(cmbListRiskSite.Selected.Value)=0 || Site.Value = cmbListRiskSite.Selected.Value) && 
                    ((cmbListRiskStatus.Selected.Value = "All Status") || (IsEmpty(cmbListRiskStatus.SelectedItems) || Issue_Status.Value = cmbListRiskStatus.Selected.Value)) && 
                    (IsBlank(txtKeyword.Value) || txtKeyword.Value in Title & Resolution_Month & Resolution_Year & Funding_Type.Value & Long_Range_Plan_Funding.Value)
                )
        ),
        Value(Block_No.Value),
        SortOrder.Ascending
    )
     
  • phaaland Profile Picture
    phaaland 10 on at
    Filter gallery with multiselect combobox
    Think you can make your filter easier.
    For the combobox, you do not need to check both IsBlank || IsEmpty, since you can select none, one or more - IsEmpty will suffice.
     
    Also when doing a check on a value in a textinput, if the textInput.Value is first (textInput.Value in <Field>), you do not need to check if there is anything (IsBlank) in the field. If the textfield is empty, it returns everything.
     
    Filter(colTestList,
        IsEmpty(cmbRisks.SelectedItems) || Risk in cmbRisks.SelectedItems
        && txtTitleSearch.Value in Title
    )

    Also played around with status (New, In Progress, Complete).
    Adds the statuses to a collection:
    ClearCollect(colListStatus, Choices([@'TestList'].ItmStatus));
    // Add to listStatus an item with text "All"
    Collect(colListStatus, {Value: "All"});

    Then use a Radio button group with items: colListStatus.
    I would like to since "All" should show all status I add this to the Radio Button Group.OnChange:
    If(!(Self.Selected.Value = "All"),
        UpdateContext({ctxStatus: Self.Selected.Value}),
        UpdateContext({ctxStatus: Blank()})
    )

    Then my filter is changed to:
    Filter(colTestList,
        IsEmpty(cmbRisksSelector.SelectedItems) || Risk in cmbRisksSelector.SelectedItems
        && IsBlank(ctxStatus) || ItmStatus.Value = ctxStatus
        && txtTitleSearch.Value in Title
    )



     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,422

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,711

Leaderboard