cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johany_Navarro
Helper I
Helper I

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

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions

@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.

View solution in original post

RandyHayes
Super User
Super User

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
)

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

5 REPLIES 5
RandyHayes
Super User
Super User

@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).

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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. 

 

 

 

 

@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.

View solution in original post

RandyHayes
Super User
Super User

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
)

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,130)