Do Filters Work Correctly?

I created a filter that:

Shows rows that match <all conditions>
<Status> <is not one of> values selected> (DONE, DEAD, LATER)
<Archive> <is unchecked>

The instructions my team is getting are that they can flip tasks to DONE and DEAD, but they won't be hidden from view until our next meeting where they are then archived.

Problem is, if I check Archive for ANY row, save, and reload, the filter hides the row.

Are checkboxes unreliable? I have it set to restrict to checkbox use only, but toggling it doesn't seem to make a difference.

Find more posts tagged with

Sheets

Conditional Formatting

Card View

Comments

Andrée Starå

Hi John,

Your filter will not work. If you want to show the rows where the status is DONE or DEAD and Archive isn't checked. The filter should be:

Statis is not one off LATER

Archive is unchecked.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Debbie Sawyer

The behaviour you are experiencing from the filter seems logical to me. You are asking it to filter (ie to only show) records that are NOT Dead, Done or Later (i.e. I would assume you have one called Active or something similar) AND Archived IS unchecked (i.e. it is an active record that has not been set to archive).

So I would assume you only see Active records that have not been marked up for archiving and are still active status'.

If you tick the Archive checkbox, then it is no longer part of the filtered result and is consequently hidden. (In the same way as if you set any row to Dead, Done or Later)

Sometimes when we create a filter we actually want the opposite to what we ask for. The behaviour here sounds logical to the settings explained. Have another think about the logic of the filter - everything in the filtered conditions area will be criteria to leaving the data visible in the results.

Hope this helps.

Kind regards

Debbie Sawyer Consultant & Training Manager

Smarter Processes mean happy people in successful businesses

johnvilsack

I'm trying to understand specifically why it will not work.

When writing a filter, which of the statements is true:

Show me all rows where STATUS IS NOT DEAD OR DONE; from those results, show me all where ARCHIVE IS NOT CHECKED
Show me all rows where STATUS IS NOT DEAD OR DONE AND ARCHIVE IS NOT CHECKED

Fundamentally, these are two completely distinct queries.

johnvilsack

Thank you for the response. As I note in my above reply to Andree, I need to understand the distinction between filter application.

Andrée Starå

Hi John,

When you set the filter to "all conditions" it checks if all the conditions in the filter are true.

Your queries will give the same result. It's basically the same query.

Hope that helps!

Best,

Andrée

johnvilsack

Thanks for staying on top of this.

In my example above, I would expect #1 to hide a STATUS that is ACTIVE, but the ARCHIVE box is checked (Hide all based on status first, then hide unchecked).

#2 would hide ONLY if the STATUS is DEAD or DONE AND the box is checked.

From a query perspective, #1 would execute in sequence, #2 would execute as a single qualifier.

Am I missing something?

johnvilsack

I ended up creating a hidden column that lets me build the formula that I needed. It gives me far greater flexibility, I just wish there was a way to see the query the filters run and modify that accordingly.

Thanks for the help!

Debbie Sawyer

I would say both of these are the same!? In both cases here both fields (status and archive) must return true to be shown in the result set.

If your second example had an OR instead of an AND then only one or the other of the fields must return true to be shown in the result set.

Confusing though isn't it!?

Debbie

johnvilsack

They are not the same. In my first example, All DEAD and DONE rows are removed, then all rows where the checkbox is checked are removed. In my second example, only the rows that are DEAD OR DONE that also have the checkbox checked are removed.

The first example would hide a row that was set to ACTIVE but had checked the box. In the second example, that row would still be visible, since both conditions were not met.

Andrée Starå

Happy to help!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…