web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

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 / Filter a galley by man...
Power Apps
Answered

Filter a galley by many columns (Text/range of dates/boolean)

(0) ShareShare
ReportReport
Posted on by Johany_Navarro 66

Hi All, I'm new in PowerApps and got stuck creating an app, I would love any help 😊.

 

Basically, I have a gallery (Sharepoint list) with 44 columns and around 600 rows I need to filter the gallery by 5 text input, 5 range of days and finally, by 5 boolean columns (true or false) any advice about how to create that huge filter??

 

I managed to filter by the 5 text inputs using IFs for all the possible combinations 😵 but haven't found a way to integrate the range of dates and the boolean columns.

Categories:
Building Power Apps
  • RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @Johany_Navarro 

    You can certainly filter without a bunch of If's.  And the ranges and Booleans are fairly simple to integrate into the formula.

    If you can share your current formula, perhaps that would provide some more direct answer to how to resolve your issue.  If you can share that formula and mention any particular names of columns you want to include that are not included, and any column types that are not simple columns (i.e. text, date, numeric).

     

     

  • Johany_Navarro Profile Picture
    Johany_Navarro 66 on at

    This is my formula for the 5 text inputs. 

    If(
    //DEFAULT FILTER BY ALL THE FILTERS ARE EMPTY #1
    IsBlank(inputCompanyName) And IsBlank('inputManager/Leader') And IsBlank(inputRefundClaimForm) And IsBlank(inputTR1Reviewer),
    colProcesses,
    //FILTER BY CLIENT NAME ONLY #2
    !IsBlank(inputCompanyName) And IsBlank('inputManager/Leader') And IsBlank(inputRefundClaimForm) And IsBlank(inputTR1Reviewer),
    Filter(colProcesses,inputCompanyName.Text in Companyname),
    //FILTER BY MANAGER/LEADER ONLY #3
    IsBlank(inputCompanyName) And !IsBlank('inputManager/Leader') And IsBlank(inputRefundClaimForm) And IsBlank(inputTR1Reviewer),
    Filter(colProcesses,'inputManager/Leader'.Text in 'Manager/Leader'),
    //FILTER BY REFUND CLAIM FORM #4
    IsBlank(inputCompanyName) And IsBlank('inputManager/Leader') And !IsBlank(inputRefundClaimForm) And IsBlank(inputTR1Reviewer),
    Filter(colProcesses,inputRefundClaimForm.Text in 'RefundClaimForm?'),
    //FILTER BY TR 1FR REVIEWER #5
    IsBlank(inputCompanyName) And IsBlank('inputManager/Leader') And IsBlank(inputRefundClaimForm) And !IsBlank(inputTR1Reviewer),
    Filter(colProcesses,inputTR1Reviewer.Text in TR1stReveiewer),
    //FILTER BY COMPANY AND MANAGER/LEADER #6
    !IsBlank(inputCompanyName) And !IsBlank('inputManager/Leader') And IsBlank(inputRefundClaimForm) And IsBlank(inputTR1Reviewer),
    Filter(colProcesses,inputCompanyName.Text in Companyname And 'inputManager/Leader'.Text in 'Manager/Leader'),
    //FILTER BY COMPANY AND REFUND CLAIM FORM #7
    !IsBlank(inputCompanyName) And IsBlank('inputManager/Leader') And !IsBlank(inputRefundClaimForm) And IsBlank(inputTR1Reviewer),
    Filter(colProcesses,inputCompanyName.Text in Companyname And inputRefundClaimForm.Text in 'RefundClaimForm?'),
    //FILTER BY COMPANYNAME AND TR1STREVIEWER #8
    !IsBlank(inputCompanyName) And IsBlank('inputManager/Leader') And IsBlank(inputRefundClaimForm) And !IsBlank(inputTR1Reviewer),
    Filter(colProcesses,inputCompanyName.Text in Companyname And inputTR1Reviewer.Text in TR1stReveiewer),
    //FILTER BY MANAGER/LEADER AND REFUND CLAIM FORM #9
    IsBlank(inputCompanyName) And !IsBlank('inputManager/Leader') And !IsBlank(inputRefundClaimForm) And IsBlank(inputTR1Reviewer),
    Filter(colProcesses,'inputManager/Leader'.Text in 'Manager/Leader' And inputRefundClaimForm.Text in 'RefundClaimForm?'),
    //FILTER BY MANAGER/LEADER AND TR1STREVIEWER #10
    IsBlank(inputCompanyName) And !IsBlank('inputManager/Leader') And IsBlank(inputRefundClaimForm) And !IsBlank(inputTR1Reviewer),
    Filter(colProcesses,'inputManager/Leader'.Text in 'Manager/Leader' And inputTR1Reviewer.Text in TR1stReveiewer),
    //FILTER BY REFUND CLAIM FORM AND TR1STREVIEWER#11
    IsBlank(inputCompanyName) And IsBlank('inputManager/Leader') And !IsBlank(inputRefundClaimForm) And !IsBlank(inputTR1Reviewer),
    Filter(colProcesses,inputRefundClaimForm.Text in 'RefundClaimForm?' And inputTR1Reviewer.Text in TR1stReveiewer),
    //FILTER BY ALL OF THEM #12
    !IsBlank(inputCompanyName) And !IsBlank('inputManager/Leader') And !IsBlank(inputRefundClaimForm) And !IsBlank(inputTR1Reviewer),
    Filter(colProcesses, inputCompanyName.Text in Companyname And 'inputManager/Leader'.Text in 'Manager/Leader' And
    inputRefundClaimForm.Text in 'RefundClaimForm?' And inputTR1Reviewer.Text in TR1stReveiewer),
    //FILTER BY NAME, MANAGER AND REFUND CLAIM FORM #13
    !IsBlank(inputCompanyName) And !IsBlank('inputManager/Leader') And !IsBlank(inputRefundClaimForm) And IsBlank(inputTR1Reviewer),
    Filter(colProcesses,inputCompanyName.Text in Companyname And inputRefundClaimForm.Text in 'RefundClaimForm?' And 'inputManager/Leader'.Text in 'Manager/Leader'),
    //FILTER BY MANAGER,REFUND CLAIM FORM AND TR1STREVIEWER#14
    IsBlank(inputCompanyName) And !IsBlank('inputManager/Leader') And !IsBlank(inputRefundClaimForm) And !IsBlank(inputTR1Reviewer),
    Filter(colProcesses,'inputManager/Leader'.Text in 'Manager/Leader' And inputRefundClaimForm.Text in 'RefundClaimForm?' And inputTR1Reviewer.Text in TR1stReveiewer),
    //FILTER BY CLIENTNAME, REFUND CLAIM FORM AND TR1STREVIEWER#15
    !IsBlank(inputCompanyName) And IsBlank('inputManager/Leader') And !IsBlank(inputRefundClaimForm) And !IsBlank(inputTR1Reviewer),
    Filter(colProcesses,inputCompanyName.Text in Companyname And inputRefundClaimForm.Text in 'RefundClaimForm?' And inputTR1Reviewer.Text in TR1stReveiewer),
    ) //Closing IF

     

    Then to filter by a range of days would be like this:

    Filter(
    colProcesses,
    'Month audit completion planned' >= MonthPlannedSTARTPicker.SelectedDate && 'Month audit completion planned' <= MonthPlannedENDPicker.SelectedDate,
    DateSentForPrinting >= SentForPrintingSTARTPicker.SelectedDate && DateSentForPrinting <= SentForPrintingENDPicker.SelectedDate,
    YearEnd >= YearEndFROMPicker.SelectedDate && YearEnd <= YearEndTOPicker.SelectedDate
    )//Filter 

     

    I have another 3 range of dates to filter by.

     

    Here I got stuck cause didn't manage to integrate those different filters in one and actually the booleans I'm not sure how to do it, would be like this:

    Filter(colProcesses, PytReady = true) but this needs to be a checkbox I believe.

     

    I have no clue on how to ingrate all of those different validations in a filter function.

     

    A workaround that I though off was... placing a Combobox with the column names to filter by text and text input in front of it to type the criteria like this Filter(colProcesses, ComboBox.Selected.ColumnName = TextInput.Text) but the gallery shows nothing 😖 also I tried with the dates but got an empty gallery. 

     

     

     

     

  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 156,526 Most Valuable Professional on at

    @Johany_Navarro ,

    Just giving @RandyHayes a break for the weekend - I can only see four text boxes, but the below should give you an idea of the syntax required to combine all your filters.<

    Filter(
 colProcesses,
 If(
 IsBlank(inputCompanyName),
 true,
 inputCompanyName.Text in Companyname
 ) &&
 If(
 IsBlank('inputManager/Leader'),
 true,
 'inputManager/Leader'.Text in 'Manager/Leader'
 ) &&
 If(
 IsBlank(inputRefundClaimForm),
 true,
 inputRefundClaimForm.Text in 'RefundClaimForm?'
 ) &&
 If(
 IsBlank(inputTR1Reviewer),
 true,
 inputTR1Reviewer.Text in TR1stReveiewer
 ) &&
 'Month audit completion planned' >= MonthPlannedSTARTPicker.SelectedDate &&
 'Month audit completion planned' <= MonthPlannedENDPicker.SelectedDate &&
 DateSentForPrinting >= SentForPrintingSTARTPicker.SelectedDate &&
 DateSentForPrinting <= SentForPrintingENDPicker.SelectedDate &&
 YearEnd >= YearEndFROMPicker.SelectedDate && 
 YearEnd <= YearEndTOPicker.SelectedDate
)

     

    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.

  • Verified answer
    RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    Thanks for stepping in @WarrenBelz - I did need a little break over the weekend!  

     

    @Johany_Navarro Warren's formula is a good to go one!  I would only change a couple things.  The If statements are redundant a little and can be removed and you should refer to the Text property of the InputControl, not just the control name.  So the formula would be this:

    Filter(
 colProcesses,
 (IsBlank(inputCompanyName.Text) || inputCompanyName.Text in Companyname) &&
 (IsBlank('inputManager/Leader'.Text) || 'inputManager/Leader'.Text in 'Manager/Leader') &&
 (IsBlank(inputRefundClaimForm.Text) || inputRefundClaimForm.Text in 'RefundClaimForm?') &&
 (IsBlank(inputTR1Reviewer.Text) || inputTR1Reviewer.Text in TR1stReveiewer) &&
 'Month audit completion planned' >= MonthPlannedSTARTPicker.SelectedDate &&
 'Month audit completion planned' <= MonthPlannedENDPicker.SelectedDate &&
 DateSentForPrinting >= SentForPrintingSTARTPicker.SelectedDate &&
 DateSentForPrinting <= SentForPrintingENDPicker.SelectedDate &&
 YearEnd >= YearEndFROMPicker.SelectedDate && 
 YearEnd <= YearEndTOPicker.SelectedDate
)

     

     

  • Johany_Navarro Profile Picture
    Johany_Navarro 66 on at

    You @RandyHayes  and @WarrenBelz are LEGENDS - MASTERS thank you two so much so saved my life...

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

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 402 Super User 2026 Season 2

#2
Mohsin Ali Profile Picture

Mohsin Ali 328

#3
WarrenBelz Profile Picture

WarrenBelz 296 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
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
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans