Combine "AND and "OR" with filtering

philippejj0421 · 05/13/18

I did not see this topic covered:

Is there a way to combine logical "AND" and "OR" when creating a filter?

The only options seems to be 'All conditions" (AND) or "One of the conditions" (OR)

Here is why I am asking:

I want to filter a buzzword that some people put in the "Comments" field and others put in the "Suggestions" field. But I want to also add a strict time window.

To put it another way, I want a filter that can do:

"Date greater than X" AND "Date Less than Y" AND ["Buzzword in Field A" OR "Buzzword in Field B"]

Or, is it possible to "Pipe" filters: Results of "Filter A" (set time frame) are piped into "Filter B" (look for buzzword)?

Thanks!

ricki · 05/14/18

You could have a calculated column that would return 1 if buzzword is in any of the fields and then you could filter where you have a 1 and the date ranges

Sasan · 05/14/18

Philippe,

Would the following example filter criteria in your Report Builder help? See attached.

Sasan

Screen Shot 2018-05-14 at 11.52.47 AM.png

Screen Shot 2018-05-14 at 11.57.45 AM.png

philippejj0421 · 05/14/18

Thank you Sasan and Ricki

Sasan, Report Builder is indeed one option, but I am trying to stay away from it as it has limited formatting, sorting and viewing (no Cards) capabilities and needs to be refreshed to display the latest changes.

Ricki, I ended up doing just that: I created a bunch of hidden columns with some IF (buzzword) and IF (Date) statements and I was able to get what I wanted by combining them.

But the bottom line is that today I cannot mix AND and OR in the same cell.

ricki · 05/14/18

What do you mean that you can't combine and and OR in the same cell? Why couldn't you do something like this as the formula for your single cell?

=IF(AND(TestDate1 > TODAY(), TestDate2 < TODAY(), OR(FIND("buzz", [Primary Column]2) > 0, FIND("buzz", [Primary Column]3 > 0))), 1, 0)

philippejj0421 · 05/14/18

Sorry I misspoke.

My last sentence should have been; You cannot mix AND and OR in a Filter.

Regarding your formula, thank you, I will try it. I spent a few hours trying to make one work but gave up in frustration. I must have been forgetting a parenthesis or two.

ricki · 05/14/18

Thats true but this is a simple enough workaround to accomplish exactly what you need while keeping the filters interface simple enough to use.

Also, you only need to only have the formula for the OR peice of things. The rest can be defined in a filter with ALL conditions

Holly Magnuson · 10/11/18

philippejj0421 Did you find a resolution to your question?

I'm having the same issue. I want a filter that shows me any task that have a due date in the past OR a due date in the next 10 days AND are not marked complete.

With multiple sheets a report might make more sense, but as (I believe) you said you don't get the option for Card view or the other views.

Just curious if you found a workable solution.

Best regard,

Holly

dthiele · 10/26/18

@Holly & @philippejj0421, I am trying to do the exact same thing. I wish there were an easy way to get this done.

Esther Aller · 01/09/20

I second @dthiele's thought. I would like to have my own personal filter on a shared smartsheet. I don't want to clutter a smartsheet shared by my entire team with columns just for my own filters.

In my particular use case, the smartsheet is our intake. My name can appear either as the primary owner or as one of the secondary owners. We also have a status column. I want to filter to any records where I'm either the primary OR the secondary owner AND are not closed.

I understand the concern about keeping the filter interface simple, but even an "Advanced options" button that would let me input a formula would be nice!

Andrée Starå · 01/09/20

Hi everyone,

Please submit an Enhancement Request when you have a moment to have your vote added

A New Way to Submit Your Feature Requests

To make your Enhancement / Feature Request count, send in the form above because there isn’t a guarantee, it will be registered otherwise.

Original Post: https://community.smartsheet.com/announcement/new-way-submit-your-feature-requests

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Shinya · 03/10/20

I can't believe Smartsheet didn't build this into their report builder, are we asking too much? It is like a basic filter with OR, they can only allow AND? Are you kidding me!

Paul Newcome · 03/11/20

@Shinya I'm not sure I follow... You can use OR in report builders...

Andrée Starå · 03/12/20

@Shinya

Hi Shinya,

Please see the below screenshot.

Would that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jamie Bedford · 08/05/20

Just want to add my voice to this request. Having some kind of an "advanced" report builder would be very helpful. (I've submitted an Enhancement Request already.)

Setting up helper columns to do the logic can become quite complex, and can require a bunch of different helper columns depending on the different types of reports being set up, or the complexity of the logic. Even something simple like "either this column or this column, as long as this other column" is too complex for the current report builder, though.

It'd be nice if the report builder let you write your own WHERE-style clauses, or if it let you add additional "What?" filters. I get that it can become visually complex, though.

Tzippy · 01/18/21

Hi!

@Paul Newcome

Is there any way to do this on the updated reports filter? I want to be able to have the "and" & "or" option within the same filter group. Right now its only allowing to use either one for each group.

Thank you.

Smartsheet Basics

Smartsheet Basics

Combine "AND and "OR" with filtering

Comments

Quick Links

Categories

Trending in Smartsheet Basics

Save and Refresh with multiple users

Conversations in Sheet vs Report (for the Sheet)

Looking for Guidance on Smartsheet Aligned Solution Professional Certification