Filter gallery with multiselect combobox

(1) Share

Report

Posted on by csuk

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 ?

Categories:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (1)

Sort by

WarrenBelz 155,450 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @csuk,

Firstly, I need to assume that you have not altered any of the other filters during this process. Also how many records are in your list and what is your Data Row Limit set at (none of that filter is Delegable). As a bit of debugging, try isolating that filter and see if you get the same result (to eliminate something that may be happening with the other filters).
Filter( Risk_Master, Len(cmbListRiskBlock.Selected.Value) = 0 || Block_No.Value in cmbListRiskBlock.SelectedItems )

Can you also post the Items (in Text) of the Combo Box and also what type of field is Block_No.

Was this reply helpful? Yes No
csuk 40 on at

Like (0)

Report
Copy link

Link copied!

Hi @WarrenBelz

I am not changing any other filter during this process.

I have 390 records so fat in SP list.

I will try debugging you mentioned later today.

The Block_No is a choice field in SP list, no multi select allowed.

The Items property of combobox is - Choices([@Risk_Master].Block_No)

The items are - 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16

The data row limit is

Was this reply helpful? Yes No
csuk 40 on at

Like (0)

Report
Copy link

Link copied!

Hi @WarrenBelz,

I tried debugging by disabling all other filters and just keeping filter on Block_No. I am getting same result.

Was this reply helpful? Yes No
WarrenBelz 155,450 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@csuk,

Is this a Modern or Classic Combo Box ? If Modern, try with a Classic control.

Was this reply helpful? Yes No
phaaland 10 on at

Like (0)

Report
Copy link

Link copied!

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
)

Was this reply helpful? Yes No
csuk 40 on at

Like (0)

Report
Copy link

Link copied!
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 )

Was this reply helpful? Yes No
WarrenBelz 155,450 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@csuk,

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

Was this reply helpful? Yes No
csuk 40 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
WarrenBelz 155,450 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @csuk,

What type of field is BlockNo and what is the actual (in Text please) Items of cmbListRiskBlock ?

Was this reply helpful? Yes No
csuk 40 on at

Like (0)

Report
Copy link

Link copied!

Hi @WarrenBelz,

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)

Was this reply helpful? Yes No