Exclude from gallery if List column has date

[bmcnal]

Hello, 

I am developing an employee database to track training, skills, hire date, and termination date.  I want to be able to sort the gallery and filter some results out.  I have created two sort icons to sort alpha vs hire date already.  It was pretty straight forward.

I am looking for a way to create a button (or icon) to remove from the gallery any row that has a value in a certain column.  For example, if an employee has a value in the "Last Date" column, exclude this employee from the gallery view.  Then still being able to apply the alpha vs hire date sort function.  The "Last Date" column is a Date and Time formatted column.

Here is my formula in the Items section of the gallery - 

SortByColumns(
Filter(
[@'Master Database V.2'],
StartsWith(
Title,
TextSearchBox1.Text
)
),
varColumnSort,
varColumnDir
)
)

This is a picture from the Microsoft List - the rows with a value in the last date I want to be able to exclude.

[RandyHayes]

@bmcnal 

You will be wanting to look for a value to be Not Blank.  This is not something that is delegable to your list.  Although I am doubting that delegation is something you are running into as you are using the SortByColumns with variables (which is already not delegable).

So, depending on the number of records you have or expect to have and the Record Limit setting in your app, the following formula may be an option for you:

With({_items:
    Filter('Master Database V.2',
        StartsWith(Title, TextSearchBox1.Text) 
    )
},

    SortByColumns(
        Filter(_items, !IsBlank('Last Date')),
        varColumnSort,
        varColumnDir
    )
)

I hope this is helpful for you.

[bmcnal]

Thanks for the info. I'll give that a try. 

How might I work that into a button or clickable icon to turn on and off? 

Thanks again,

[RandyHayes · Accepted solution]

@bmcnal 

What is it you want to turn on and off?

If it is the filter for the blank that you are referring to, then I would suggest a toggle control.

Then the formula would become:

With({_items:
    Filter('Master Database V.2',
        StartsWith(Title, TextSearchBox1.Text) 
    )
},

    SortByColumns(
        Filter(_items, (yourToggle.Value || !IsBlank('Last Date'))),
        varColumnSort,
        varColumnDir
    )
)

With this, if the toggle is on, then the blank check is ignored.  If it is off, then only non-blank records will be returned.

[bmcnal]

One more question. Could this be done with multiple toggles to further exclude items from the gallery? 

The gallery is filtered by toggle 1 for all rows that have empty cells in column 'last date'. toggle 2 for all rows that have empty cells in column 'triage'. When each is active individually, the condition is considered. When both are active, both must be true for the gallery to populate.