Does not include Filter condition based on another condition

[ShiroKame]

Hi,

My Sharepoint List have a Yes/No column, and in List screen, I have a checkbox for this Yes/No column.

If User check the checkbox, the gallery will return True value of Yes/No column, and if User uncheck the checkbox, ALL items will show.

I am currently using below filter

If(checkBox.Value,
Filter(TableA, YesNoColumn.value = true, Condition A, Condition B, Condition C),
Filter(TableA, Condition A, Condition B, Condition C))

↑ The problem is 2 have more than 2 Yes/No column to deal with, so the code for 2 Yes/No column is

If(CheckBox1.Value,
   //Checkbox 1 true, include Condition C
   If(CheckBox2.Value,
      //checkbox 2 true, return True value
      Filter(TableA, YesNoColumn1.value = true, Condition A, Condition B, Condition C),
      //checkbox 2 false, return All value
      Filter(TableA, Condition A, Condition B, Condition C)),
   //Checkbox 1 false, does not include Condition C
   If(CheckBox2.Value,
      //checkbox 2 true, return True value
      Filter(TableA, YesNoColumn1.value = true, Condition A, Condition B),
      //checkbox 2 false, return All value
      Filter(TableA, Condition A, Condition B))

And as you see above, the maintenance effort for the code is HUGE.

Do you have any suggestion?

Thanks so much!!!

[Nogueira1306]

Hey

The code for the 2 Yes/No collumns should be:

If(CheckBox1.Value = false & CheckBox2.Value = false,
   //Condition for both false  ,
   If(CheckBox1.Value = true & CheckBox2.Value = false,
      //Condition for 1 true and 2 false   ,
      If(CheckBox1.Value = false & CheckBox2.Value = true,
         //Condition for 1 false and 2 true    ,
         //Condition for both true 
         )
   )
)

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

[zmansuri]

I have never tried this. But can you try this?

Filter(TableA, YesNoColumn.Value= checkBox.Value, Condition A, Condition B, Condition C)

[ShiroKame]

The number of condition is the same, 4 conditions, so I think the code maintenance effort is the same. 

[ShiroKame]

It doesn't work 😞

[v-jefferni · Accepted solution]

Hi @ShiroKame ,

So this is a logical test optimization problem. Let's sort out the logic.

You have a Yes/No column, 2 Check boxes out of the Gallery, and would like to filter the table by the Yes/No column value and other 3 conditions. One Check box bonds to the Yes/No column value and the other bonds to condition C. No matter how you check the 2 Check boxes condition A & B will always as the filter conditions. So, try below formula:

Filter(
       TableA,
       ConditionA 
       && 
       ConditionB 
       && 
       //CheckBox1 check, return ConditionC, otherwise returen true
       If(CheckBox1.Value, ConditionC, true)
       && 
       //CheckBox2 check, return YesNoColumn ture, otherwise returen true
       If(Checkbox2.Value, YesNoColumn.Value = true, true) 
)

This is the simplest way for the two check boxes. But you will see delegation warning in the formula since Yes/No column is non-delegable. So we will have to filter by the three conditions first, then filter the result table by the Yes/no column:

With(
     {Results:
              Filter(
                     TableA,
                     ConditionA 
                     && 
                     ConditionB 
                     && 
                     //CheckBox1 check, return ConditionC, otherwise returen true
                     If(CheckBox1.Value, ConditionC, true)
              )
      },
      Filter(
             Results,
             //CheckBox2 check, return YesNoColumn ture, otherwise returen true
             If(Checkbox2.Value, YesNoColumn.Value = true, true) 
      )
)

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[ShiroKame]

Dear Jeffer,

Brilliant!!!!

My code reduce as 1/4 compared to the original.

Thank you so much!!!!!!!!!!!!!!!!!!!!!!!!!

I have never used With function before, and now I have learnt it from your code!!!!!