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

[Johany_Navarro]

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.

[RandyHayes]

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

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. 

[WarrenBelz · Accepted solution]

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

[RandyHayes · Accepted solution]

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]

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