Skip to main content

Notifications

Announcements

Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps - Building Power Apps / Filter Gallery Combobo...
Power Apps - Building Power Apps
Answered

Filter Gallery Combobox No Records on Additional Filters

(0) ShareShare
ReportReport
Posted on by RazenC

I have a MS SQL table with 100K+ records. I managed to remove the delegation warning by having a workaround in NOT using IN clause. Based on this tutorial Video .

 

I have these filters in my Canvas App.

Error.JPG

 

The Create Date, Requirement Id and Policy Number filters are working just fine. 

Now for these comboboxes If I populate these by having one each per filter, it gives me 0 records where it should have a records when I check in my table in Database. 

 

I have a pagination feature for this canvas app and it works fine. 

 

Lets say we select 1 item each for Type, Case Status and Sales Source, it works fine. But if I added another item for either the remaining comboboxes that has no item selected it now returns me 0 records.

 

All the Combobox filters works fine but if it reaches the 4th field filter for Comboboxes it gives me 0 records. 

No issues if I added filter for Date and text fields with 3 populated comboboxes with items. The problem exists for the Comboboxes in any order as long as there are 4 fields filtered.

 

Here is my query for filter:

Filter(
 AGENGEN_NEWCASE,
 (IsBlank(dte_requirementDate.SelectedDate) || ReqCreateDateAsString = Text(dte_requirementDate.SelectedDate,"yyyymd")),
 (txt_requirementId.Text in RequirementId),
 (txt_policyNumberNewCase.Text in PolicyNumber),
 And(Or(
 IsBlank(cmb_StateIssueNewCase.SelectedItems),
 IsEmpty(cmb_StateIssueNewCase.SelectedItems),
 StateOfIssue = Last(FirstN(cmb_StateIssueNewCase.SelectedItems,1)).Value,
 StateOfIssue = Last(FirstN(cmb_StateIssueNewCase.SelectedItems,2)).Value,
 StateOfIssue = Last(FirstN(cmb_StateIssueNewCase.SelectedItems,3)).Value,
 StateOfIssue = Last(FirstN(cmb_StateIssueNewCase.SelectedItems,4)).Value,
 StateOfIssue = Last(FirstN(cmb_StateIssueNewCase.SelectedItems,5)).Value
 )),
 And(Or(
 IsBlank(cmb_requirementStatus.SelectedItems),
 IsEmpty(cmb_requirementStatus.SelectedItems),
 RequirementStatus = Last(FirstN(cmb_requirementStatus.SelectedItems,1)).Value,
 RequirementStatus = Last(FirstN(cmb_requirementStatus.SelectedItems,2)).Value,
 RequirementStatus = Last(FirstN(cmb_requirementStatus.SelectedItems,3)).Value,
 RequirementStatus = Last(FirstN(cmb_requirementStatus.SelectedItems,4)).Value,
 RequirementStatus = Last(FirstN(cmb_requirementStatus.SelectedItems,5)).Value
 )),
 And(Or(
 IsBlank(cmb_caseStatusNewCase.SelectedItems),
 IsEmpty(cmb_caseStatusNewCase.SelectedItems),
 CaseStatus = Last(FirstN(cmb_caseStatusNewCase.SelectedItems,1)).Value,
 CaseStatus = Last(FirstN(cmb_caseStatusNewCase.SelectedItems,2)).Value,
 CaseStatus = Last(FirstN(cmb_caseStatusNewCase.SelectedItems,3)).Value,
 CaseStatus = Last(FirstN(cmb_caseStatusNewCase.SelectedItems,4)).Value,
 CaseStatus = Last(FirstN(cmb_caseStatusNewCase.SelectedItems,5)).Value
 )),
 And(Or(
 IsBlank(cmb_Type.SelectedItems),
 IsEmpty(cmb_Type.SelectedItems),
 Type = Last(FirstN(cmb_Type.SelectedItems,1)).Value,
 Type = Last(FirstN(cmb_Type.SelectedItems,2)).Value,
 Type = Last(FirstN(cmb_Type.SelectedItems,3)).Value,
 Type = Last(FirstN(cmb_Type.SelectedItems,4)).Value,
 Type = Last(FirstN(cmb_Type.SelectedItems,5)).Value
 )),
 And(Or(
 IsBlank(cmb_salesSource.SelectedItems),
 IsEmpty(cmb_salesSource.SelectedItems),
 SalesSource = Last(FirstN(cmb_salesSource.SelectedItems,1)).Value,
 SalesSource = Last(FirstN(cmb_salesSource.SelectedItems,2)).Value,
 SalesSource = Last(FirstN(cmb_salesSource.SelectedItems,3)).Value,
 SalesSource = Last(FirstN(cmb_salesSource.SelectedItems,4)).Value,
 SalesSource = Last(FirstN(cmb_salesSource.SelectedItems,5)).Value
 )),
 And(Or(
 IsBlank(cmb_pathCategory.SelectedItems),
 IsEmpty(cmb_pathCategory.SelectedItems),
 UnderwritingPathCategory = Last(FirstN(cmb_pathCategory.SelectedItems,1)).Value,
 UnderwritingPathCategory = Last(FirstN(cmb_pathCategory.SelectedItems,2)).Value,
 UnderwritingPathCategory = Last(FirstN(cmb_pathCategory.SelectedItems,3)).Value,
 UnderwritingPathCategory = Last(FirstN(cmb_pathCategory.SelectedItems,4)).Value,
 UnderwritingPathCategory = Last(FirstN(cmb_pathCategory.SelectedItems,5)).Value
 ))
)
Categories:
Connecting to Data
General Questions
  • WarrenBelz Profile Picture
    WarrenBelz 146,702 Most Valuable Professional on at
    Re: Filter Gallery Combobox No Records on Additional Filters

    @RazenC ,

    I am not sure how the last code I posted would have given a Delegation warning if the latest version you posted did not as I moved all the combo box filters into a local query.

  • Verified answer
    RazenC Profile Picture
    RazenC on at
    Re: Filter Gallery Combobox No Records on Additional Filters

    Unfortunately the last query you sent is not delagated. There's no delegation warning showing up but the data is strictly set to 2000. 

     

    Anyways after reading your replies and little understanding about this gallery issue. I tried reducing the number of filters based on the choices the user might select. So I focused on what was really the requirement in using the app. Lets say I have a combobox with 3 items then in the filter I only limit the user to select only 2 items because it doesn't makes sense to select all the 3 items if they can just leave it blank and have a same functionality. Here is my final query and managed to make all the 6 comboboxes work with text field and date filters.

     

    Thank you @WarrenBelz I learned a lot. Gonna make sure I share this to some channels I might know.

    Filter(
 AGENGEN_NEWCASE,
 (IsBlank(dte_requirementDate.SelectedDate) || ReqCreateDateAsString = Text(dte_requirementDate.SelectedDate,"yyyymd")) &&
 (txt_requirementId.Text in RequirementId) &&
 (txt_policyNumberNewCase.Text in PolicyNumber) &&
 (
 Len(cmb_StateIssueNewCase.Selected.Value) = 0 ||
 StateOfIssue = Last(FirstN(cmb_StateIssueNewCase.SelectedItems,1)).Value ||
 StateOfIssue = Last(FirstN(cmb_StateIssueNewCase.SelectedItems,2)).Value ||
 StateOfIssue = Last(FirstN(cmb_StateIssueNewCase.SelectedItems,3)).Value ||
 StateOfIssue = Last(FirstN(cmb_StateIssueNewCase.SelectedItems,4)).Value
 ) &&
 (
 Len(cmb_requirementStatus.Selected.Value) = 0 ||
 RequirementStatus = Last(FirstN(cmb_requirementStatus.SelectedItems,1)).Value ||
 RequirementStatus = Last(FirstN(cmb_requirementStatus.SelectedItems,2)).Value
 ) &&
 (
 Len(cmb_caseStatusNewCase.Selected.Value) = 0 ||
 CaseStatus = Last(FirstN(cmb_caseStatusNewCase.SelectedItems,1)).Value ||
 CaseStatus = Last(FirstN(cmb_caseStatusNewCase.SelectedItems,2)).Value ||
 CaseStatus = Last(FirstN(cmb_caseStatusNewCase.SelectedItems,3)).Value ||
 CaseStatus = Last(FirstN(cmb_caseStatusNewCase.SelectedItems,4)).Value
 ) &&
 (
 Len(cmb_Type.Selected.Value) = 0 ||
 Type = Last(FirstN(cmb_Type.SelectedItems,1)).Value ||
 Type = Last(FirstN(cmb_Type.SelectedItems,2)).Value
 ) &&
 (
 Len(cmb_salesSource.Selected.Value) = 0 ||
 SalesSource = Last(FirstN(cmb_salesSource.SelectedItems,1)).Value ||
 SalesSource = Last(FirstN(cmb_salesSource.SelectedItems,2)).Value ||
 SalesSource = Last(FirstN(cmb_salesSource.SelectedItems,3)).Value ||
 SalesSource = Last(FirstN(cmb_salesSource.SelectedItems,4)).Value
 ) &&
 (
 Len(cmb_pathCategory.Selected.Value) = 0 ||
 UnderwritingPathCategory = Last(FirstN(cmb_pathCategory.SelectedItems,1)).Value ||
 UnderwritingPathCategory = Last(FirstN(cmb_pathCategory.SelectedItems,2)).Value
 )
)

     

  • WarrenBelz Profile Picture
    WarrenBelz 146,702 Most Valuable Professional on at
    Re: Filter Gallery Combobox No Records on Additional Filters

    @RazenC ,

    100k records are always problematic when you hit a Delegation roadblock. Dataverse is probably the answer here as SharePoint is not Delegable at all with the in filter. Your original structure is technically Delegable, but as I mentioned 30+ queries in one filter is going to test the integrity of a Gallery control (which I have seen fail with partial results on a number of occasions on really complex filters). I will have one more go at this with another idea - NOTE the top filter needs to return record numbers under your Delegation limit for this to work fully

    With(
 {
 _Data:
 Filter(
 AGENGEN_NEWCASE,
 (
 Value(dte_requirementDate.SelectedDate) < 1 || 
 ReqCreateDateAsString = Text(dte_requirementDate.SelectedDate,"yyyymd")
 ) &&
 txt_requirementId.Text in RequirementId &&
 txt_policyNumberNewCase.Text in PolicyNumber
 )
 },
 Filter(
 _Data,
 (
 Len(cmb_StateIssueNewCase.Selected.Value) = 0 ||
 StateOfIssue in cmb_StateIssueNewCase.SelectedItems.Value
 ) &&
 (
 Len(cmb_requirementStatus.Selected.Value) = 0 ||
 RequirementStatus in cmb_requirementStatus.SelectedItems.Value
 ) &&
 (
 Len(cmb_caseStatusNewCase.Selected.Value) = 0 ||
 CaseStatus in cmb_caseStatusNewCase.SelectedItems.Value
 ) &&
 (
 Len(cmb_Type.Selected.Value) = 0 ||
 Type in cmb_Type.SelectedItems.Value
 ) &&
 (
 Len(cmb_salesSource.Selected.Value) = 0 ||
 SalesSource in cmb_salesSource.SelectedItems.Value
 ) &&
 (
 Len(cmb_pathCategory.Selected.Value) = 0 ||
 UnderwritingPathCategory in cmb_pathCategory.SelectedItems.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

  • RazenC Profile Picture
    RazenC on at
    Re: Filter Gallery Combobox No Records on Additional Filters

    Unfortunately the IN clause really returns a Delegation Warning in SQL based on the query you've sent. I really appreciate you looking into this. I've really hit this roadblock for almost 2 months now since I'm finding an alternative in using IN clause. 

     

    I tried using a collection and I have a query that has no delegation issue but the thing is I cannot put inside the 100K records inside it without my app crashing or not viewing the data for a long time I can only show it up to 36K records and had to remove the pagination feature to make it work properly.

     

    This is what I've tried 

     

    I've also tried using Power Automate to run a query then send it back as a response but the flow is receiving gateway timeout since it's giving back 100K+ records. 

     

    I'm really out of options and exhaust all the possible resources I could think of. So that's why I'm really thankful someone's looking into this.

  • RazenC Profile Picture
    RazenC on at
    Re: Filter Gallery Combobox No Records on Additional Filters

    I'm directly connected to a table in SQL that's why IN is not Delegable. I've tried Dataverse but in Sharepoint I have zero experience. I'll check on this and get back at you. Thanks.

  • WarrenBelz Profile Picture
    WarrenBelz 146,702 Most Valuable Professional on at
    Re: Filter Gallery Combobox No Records on Additional Filters

    @RazenC ,

    I assume you are using Dataverse here (or the top in filters would not be Delegable). I am not a Dataverse user (SharePoint only) but would have thought the same filter in the combo box Items would also be Delegable (obviously not). My reference to 30 elements was that you are sending 5 queries for every Combo Box and the issue is more about Galleries, which I have seen on a number of occasions simply not resolving highly complex filters properly, whereas a Colleciton using the same filter gathers all the relevant records and by the outcome you posted, this may be the case. I will give you something else to try

    With(
 {
 _State: Concat(cmb_StateIssueNewCase.SelectedItems, Value),
 _Req: Concat(cmb_requirementStatus.SelectedItems, Value),
 _Case: Concat(cmb_caseStatusNewCase.SelectedItems, Value),
 _Type: Concat(cmb_Type.SelectedItems.SelectedItems, Value),
 _Sales: Concat(cmb_salesSource.SelectedItems, Value),
 _Cat: Concat(cmb_pathCategory.SelectedItems, Value)
 },
 Filter(
 AGENGEN_NEWCASE,
 (
 IsBlank(dte_requirementDate.SelectedDate) || 
 ReqCreateDateAsString = Text(dte_requirementDate.SelectedDate,"yyyymd")
 ) &&
 txt_requirementId.Text in RequirementId &&
 txt_policyNumberNewCase.Text in PolicyNumber &&
 (
 Len(cmb_StateIssueNewCase.Selected.Value) = 0 ||
 StateOfIssue in _State
 ) &&
 (
 Len(cmb_requirementStatus.Selected.Value) = 0 ||
 RequirementStatus in _Req
 ) &&
 (
 Len(cmb_caseStatusNewCase.Selected.Value) = 0 ||
 CaseStatus in _Case
 ) &&
 (
 Len(cmb_Type.Selected.Value) = 0 ||
 Type in _Type
 ) &&
 (
 Len(cmb_salesSource.Selected.Value) = 0 ||
 SalesSource in _Sales
 ) &&
 (
 Len(cmb_pathCategory.Selected.Value) = 0 ||
 UnderwritingPathCategory in _Cat
 )
 )
)

     

    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

  • RazenC Profile Picture
    RazenC on at
    Re: Filter Gallery Combobox No Records on Additional Filters

    I've tried again the IN clause you've mentioned and it's no luck. There's still a Delegation Warning.

     

    DelegationQueryIn.JPG

     

    Also unfortunately the Query you sent is not filtering my StateOfIssue column in the gallery. 

    I've also tried different variations of the query you sent but still won't work. 

    1st variation

    Filter(
	AGENGEN_NEWCASE,
 (IsBlank(dte_requirementDate.SelectedDate) || ReqCreateDateAsString = Text(dte_requirementDate.SelectedDate,"yyyymd")),
	(txt_requirementId.Text in RequirementId),
	(txt_policyNumberNewCase.Text in PolicyNumber),
 Or(
 Len(cmb_StateIssueNewCase.Selected.Value) = 0,
 StateOfIssue = First(cmb_StateIssueNewCase.SelectedItems).Value,
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,2).Value,
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,3).Value,
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,4).Value,
 StateOfIssue = Last(cmb_StateIssueNewCase.SelectedItems).Value
 )
)

     

    2nd variation

    Filter(
	AGENGEN_NEWCASE,
 (IsBlank(dte_requirementDate.SelectedDate) || ReqCreateDateAsString = Text(dte_requirementDate.SelectedDate,"yyyymd")),
	(txt_requirementId.Text in RequirementId),
	(txt_policyNumberNewCase.Text in PolicyNumber),
 (
 IsBlank(cmb_pathCategory.SelectedItems) ||
 IsEmpty(cmb_pathCategory.SelectedItems) ||
 StateOfIssue = First(cmb_StateIssueNewCase.SelectedItems).Value ||
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,2).Value ||
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,3).Value ||
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,4).Value ||
 StateOfIssue = Last(cmb_StateIssueNewCase.SelectedItems).Value
 )
)

     

    3rd variation

    Filter(
	AGENGEN_NEWCASE,
 (IsBlank(dte_requirementDate.SelectedDate) || ReqCreateDateAsString = Text(dte_requirementDate.SelectedDate,"yyyymd")) &&
	(txt_requirementId.Text in RequirementId) &&
	(txt_policyNumberNewCase.Text in PolicyNumber) &&
 (
 IsBlank(cmb_pathCategory.SelectedItems) ||
 IsEmpty(cmb_pathCategory.SelectedItems) ||
 StateOfIssue = First(cmb_StateIssueNewCase.SelectedItems).Value ||
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,2).Value ||
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,3).Value ||
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,4).Value ||
 StateOfIssue = Last(cmb_StateIssueNewCase.SelectedItems).Value
 )
)

     

    4th variation

    Filter(
	AGENGEN_NEWCASE,
 (IsBlank(dte_requirementDate.SelectedDate) || ReqCreateDateAsString = Text(dte_requirementDate.SelectedDate,"yyyymd")) &&
	(txt_requirementId.Text in RequirementId) &&
	(txt_policyNumberNewCase.Text in PolicyNumber) &&
 Or(
 IsBlank(cmb_pathCategory.SelectedItems),
 IsEmpty(cmb_pathCategory.SelectedItems),
 StateOfIssue = First(cmb_StateIssueNewCase.SelectedItems).Value,
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,2).Value,
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,3).Value,
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,4).Value,
 StateOfIssue = Last(cmb_StateIssueNewCase.SelectedItems).Value
 )
)

     

    Any alternatives?

    And also in the first comment you've said about something like "query with over 30 elements" what do you actually mean by that? Do you mean Power apps can't handle many filter conditions since I have so many because of these comboboxes? 

  • WarrenBelz Profile Picture
    WarrenBelz 146,702 Most Valuable Professional on at
    Re: Filter Gallery Combobox No Records on Additional Filters

    Hi @RazenC ,

    Single would be

    cmb_StateIssueNewCase.Selected.Value

    The .Value is not generally necessary if there is only one column in the Table, however it does not hurt to include it.

  • RazenC Profile Picture
    RazenC on at
    Re: Filter Gallery Combobox No Records on Additional Filters

    Thank you. I will try the query you suggested and let you know. As for the 

    StateOfIssue in cmb_StateIssueNewCase.SelectedItems.Value

    If we remove the .Value then yes it is not Delegable in SQL. Not sure though about this one but question.... isn't the SelectedItems.Value only get 1 value from the selected combobox items?

  • WarrenBelz Profile Picture
    WarrenBelz 146,702 Most Valuable Professional on at
    Re: Filter Gallery Combobox No Records on Additional Filters

    Hi @RazenC ,

    I think I know the issue (which I have seen before), but not necessarily the solution. You are asking a Gallery to resolve a query with over 30 elements and at times, it simply will not do this. To test it this is correct, make a collection with the same criteria and see if this resolves correctly. I will also offer something to try (example)

    Or(
 Len(cmb_StateIssueNewCase.Selected.Value) = 0, 
 StateOfIssue = First(cmb_StateIssueNewCase.SelectedItems).Value,
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,2).Value,
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,3).Value,
 StateOfIssue = Index(cmb_StateIssueNewCase.SelectedItems,4).Value,
 StateOfIssue = Last(cmb_StateIssueNewCase.SelectedItems).Value
)

    also are you saying this is not Delegable in SQL

    StateOfIssue in cmb_StateIssueNewCase.SelectedItems.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

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

News and Announcements

Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Markus Franz – Community Spotlight

We are honored to recognize Markus Franz as our April 2025 Community…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,702 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,015 Most Valuable Professional

Leaderboard

Featured topics

Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps

Product updates

Microsoft Power Platform Community release plans