FIlter Gallery with multiple status columns

[VD]

I have multiple status columns in SharePoint list, and in gallery I have added the filter to filter the data on multiple columns.

but the filter code is not working, below is my gallery filter code:

Sort(
                Filter(
                    'MOC Requests List',
                    (IsBlank(txtFIlterMOCNumber.Text) || StartsWith(
                        Title,
                        txtFIlterMOCNumber.Text
                    )) && (IsBlank(txtFIlterRnDCredit.Text) || StartsWith(
                        'RnD Credit',
                        txtFIlterRnDCredit.Text
                    )) && (IsBlank(txtFIlterPlantLocation.Text) || StartsWith(
                        'Plant Location',
                        txtFIlterPlantLocation.Text
                    )) && 'Submission Date' >= DateAdd(
                        dtFIlterDate.SelectedDate,
                        0,
                        TimeUnit.Days
                    ) && Or(
                        (IsBlank(varPreProjectSafetyAssessment) && IsBlank(varPreProjectEnvAssessment) && IsBlank(varPreProjectQnFAssessment) && IsBlank(varPreProjectProjectPlanningAssessment) && IsBlank(varPreProjectTechnicalAssessment) && IsBlank(varPreProjectEngAssessment)),
                        'Safety Assessment' = varPreProjectSafetyAssessment,
                        'Environmental Assessment' = varPreProjectEnvAssessment,
                        'Project Planning' = varPreProjectProjectPlanningAssessment,
                        'Quality and Food Assessment' = varPreProjectQnFAssessment,
                        'Technical Assessment' = varPreProjectTechnicalAssessment,
                        'Engineering Assessment' = varPreProjectEngAssessment
                    )
                ),
                'Submission Date',
                If(
                    varApproverSortDirection = SortOrder.Ascending,
                    SortOrder.Ascending,
                    SortOrder.Descending
                )
            )

on status filter I have given checkboxes and if user select updating the variable value to use it in gallery filter code

[WiZey]

Hello @VD ,

Could you further elaborate on what's not working with your filter and what's your ideal result?

I"m guessing you want to show all records with at least one column containing TRUE when the corresponding checkbox is checked, so your formula should looke like this:

Or(
   (
      IsBlank(varPreProjectSafetyAssessment) && 
      IsBlank(varPreProjectEnvAssessment) && 
      IsBlank(varPreProjectQnFAssessment) && 
      IsBlank(varPreProjectProjectPlanningAssessment) && 
      IsBlank(varPreProjectTechnicalAssessment) && 
      IsBlank(varPreProjectEngAssessment)
   ),
   Or(
      'Safety Assessment' && varPreProjectSafetyAssessment,
      'Environmental Assessment' && varPreProjectEnvAssessment,   
      'Project Planning' && varPreProjectProjectPlanningAssessment,
      'Quality and Food Assessment' && varPreProjectQnFAssessment,
      'Technical Assessment' && varPreProjectTechnicalAssessment,
      'Engineering Assessment' && varPreProjectEngAssessment
   )
)

What this does:

If ALL checkboxes are left unchecked, select the record

OR

If column is TRUE and checkbox is TRUE, select the record

Hope this was helpful to you.

[VD]

Thanks for the reply, I have multiple filters on the screen so user can filter on any column

and in status filter I have given multiple checkbox to check the status, if user select "Safety Assessment" checkbox then system should display only "Pending" records for safety assessment approvers to approve, if user select "Safety Assessment" and "Project Planning" checkbox then system should display "Pending" records for both assessment. on checkbox checked I am setting the variable to "Pending" and on uncheck setting variable to blank,

current filter code is displaying all records even if I select any checkbox.

Sort(
                Filter(
                    'MOC Requests List',
                    (IsBlank(txtFIlterMOCNumber.Text) || StartsWith(
                        Title,
                        txtFIlterMOCNumber.Text
                    )) && (IsBlank(txtFIlterRnDCredit.Text) || StartsWith(
                        'RnD Credit',
                        txtFIlterRnDCredit.Text
                    )) && (IsBlank(txtFIlterPlantLocation.Text) || StartsWith(
                        'Plant Location',
                        txtFIlterPlantLocation.Text
                    )) && 'Submission Date' <= DateAdd(
                        dtFIlterDate.SelectedDate,
                        0,
                        TimeUnit.Days
                    ) && Or(
                        (IsBlank(varPreProjectSafetyAssessment) && IsBlank(varPreProjectEnvAssessment) && IsBlank(varPreProjectQnFAssessment) && IsBlank(varPreProjectProjectPlanningAssessment) && IsBlank(varPreProjectTechnicalAssessment) && IsBlank(varPreProjectEngAssessment)),
                        'Safety Assessment' = varPreProjectSafetyAssessment,
                        'Environmental Assessment' = varPreProjectEnvAssessment,
                        'Project Planning' = varPreProjectProjectPlanningAssessment,
                        'Quality and Food Assessment' = varPreProjectQnFAssessment,
                        'Technical Assessment' = varPreProjectTechnicalAssessment,
                        'Engineering Assessment' = varPreProjectEngAssessment
                    )
                ),
                'Submission Date',
                If(
                    varApproverSortDirection = SortOrder.Ascending,
                    SortOrder.Ascending,
                    SortOrder.Descending
                )
            )

[WiZey · Accepted solution]

I see, so my formula wasn't so far off.

After a little edit, here is what I suggest:

Or(
   (
      IsBlank(varPreProjectSafetyAssessment) && 
      IsBlank(varPreProjectEnvAssessment) && 
      IsBlank(varPreProjectQnFAssessment) && 
      IsBlank(varPreProjectProjectPlanningAssessment) && 
      IsBlank(varPreProjectTechnicalAssessment) && 
      IsBlank(varPreProjectEngAssessment)
   ),
   Or(
      'Safety Assessment' = "Pending" && varPreProjectSafetyAssessment = "Pending",
      'Environmental Assessment' = "Pending" && varPreProjectEnvAssessment = "Pending",   
      'Project Planning' = "Pending" && varPreProjectProjectPlanningAssessment = "Pending",
      'Quality and Food Assessment' = "Pending" && varPreProjectQnFAssessment = "Pending",
      'Technical Assessment' = "Pending" && varPreProjectTechnicalAssessment = "Pending",
      'Engineering Assessment' = "Pending" && varPreProjectEngAssessment = "Pending"
   )
)

The explaination is the same as in my previous post.

By the way, is there a reason you're using a variable instead of directly referencing the checkbox's value?

[VD]

Thanks for the solution.

The column can have the value: Completed or blank or Pending and I just want Pending if user select a checkbox.