Help writing the logic to handle multiple if and filter statements

[morall]

I have all of these different filters that a user has access to. I'm trying to write the logic in the items section of the gallery but it's becoming pretty gnarly.

These are all of the filters:

This works for handling the search bar, etc. 

Sort(
    If(
        And('Already Viewed Toggle'.Value=true, Radio1.Selected.Value = "HM Interest"),
            Filter(
                pipeline_candidates,
                CountHMIntested > 0, 
                'iCIMS ID' in viewed_profiles,
                PipelineTemplate.Name = 'pipeline drpdwn'.Selected.Result, 
                (search_box.Text in Name)
            ),
        And('Already Viewed Toggle'.Value=true, Radio1.Selected.Value = "w/o HM Interest"),
            Filter(
                pipeline_candidates,
                CountHMIntested = 0, 
                'iCIMS ID' in viewed_profiles, 
                PipelineTemplate.Name = 'pipeline drpdwn'.Selected.Result, 
                (search_box.Text in Name)
            ),
        And('Already Viewed Toggle'.Value=false, Radio1.Selected.Value = "HM Interest"),
            Filter(
                pipeline_candidates,
                CountHMIntested > 0, 
                PipelineTemplate.Name = 'pipeline drpdwn'.Selected.Result, 
                (search_box.Text in Name)
            ),
        And('Already Viewed Toggle'.Value=false, Radio1.Selected.Value = "w/o HM Interest"),
            Filter(
                pipeline_candidates,
                CountHMIntested = 0, 
                PipelineTemplate.Name = 'pipeline drpdwn'.Selected.Result, 
                (search_box.Text in Name)
            ),
            Filter(
                pipeline_candidates, 
                PipelineTemplate.Name = 'pipeline drpdwn'.Selected.Result, 
                (search_box.Text in Name))
    ),
    'Created On',
    Descending
)

But I'm having to write every permutation in the If statement, which is creating a headache when I now want to add the ability to filter on years of experience:

    If(
        'Yrs of Exp drpdown'.Selected.Value = "All", 
            Filter(
                'Pipeline Candidates', 'Years of experience' > 0
            ), 
        'Yrs of Exp drpdown'.Selected.Value = "2-5 yrs", 
            Filter(
                'Pipeline Candidates', 
                'Years of experience' >= 2,
                'Years of experience' <= 5
            ),
        'Yrs of Exp drpdown'.Selected.Value = "6-9 yrs", 
            Filter(
                'Pipeline Candidates', 
                'Years of experience' >= 6,
                'Years of experience' <= 9
            ),
        'Yrs of Exp drpdown'.Selected.Value = "10+ yrs", 
            Filter(
                'Pipeline Candidates', 
                'Years of experience' >= 10
            )
    ), 

Any thoughts on how I can clean this up? 

[RandyHayes · Accepted solution]

@morall 

Consider revisiting your formula and simplifying.  It will be much easier...

SortByColunmns(
    Filter(pipeline_candidates,
         
        If('Already Viewed Toggle'.Value, 
           iCIMS ID' in viewed_profiles, 
           true
        ) &&

        Switch(Radio1.Selected.Value,
            "HM Interest", CountHMIntested > 0, 
            "w/o HM Interest", CountHMIntested = 0,
            true
        ) &&
        
        Switch('Yrs of Exp drpdown'.Selected.Value,            
            "2-5 yrs", 'Years of experience' >= 2 && 'Years of experience' <= 5,
            "2-5 yrs", 'Years of experience' >= 6 && 'Years of experience' <= 9, 
            "10+ yrs", 'Years of experience' >= 10,
            true
        ) &&
           
        search_box.Text in Name &&
        PipelineTemplate.Name = 'pipeline drpdwn'.Selected.Result
    ),
    'Created On',
    Descending
)

I hope this is helpful for you.

[morall]

yes, this is amazing. Thank you.

[morall]

Hey @RandyHayes ,

This is really helpful in understanding how to have multiple If statements. I'm trying to add one of my own and would appreciate your input.

This is a multi-select input:

 

I'm trying to incorporate this, where I have the Filter() inside the If statement. If the dropdown is selected to "All", show everyone, otherwise Filter(). I'm a little confused as to how I can write this with the Filter outside the If statement

Sort(
    If(
        drpLocation.Selected.Value = "All", pipeline_candidates,
        Filter(pipeline_candidates, drpLocation.Selected.Value in Concat('Desired Location'.Value, Value & ", "))
    ),
    'Created On',
    Descending
)

I've been trying a few different ways, trying both If() and Switch() but can't get it to work. Any thoughts? 

[RandyHayes]

@morall 

Give this a shot...

Sort(
   Filter(
          pipeline_candidates, 
          If(drpLocation.Selected.Value = "All", 
             true,
             drpLocation.Selected.Value in Concat('Desired Location'.Value, Value & ", ")
          )
    ),
    'Created On',
    Descending
)

Thing to remember with filter is that it has a condition.  If any of the condition evaluates to "true", then the row will be part of the results.  

So, in the above, if the dropdown has "All" in it, then the result of the If will be "true" (meaning every row will be a true returned result).  If the dropdown has something else, then it is up to other part (the "in" operator) to determine true or false.

[morall]

Hey Randy,

That worked! Great explanation, that makes sense. Thank you!