Combine "AND and "OR" with filtering

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!

Find more posts tagged with

Reports

Conditional Formatting

Comments

ricki

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

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

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

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

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

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

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

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

Esther Aller

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å

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

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

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

Andrée Starå

@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

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

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Does anyone else find Collections worthless?
I find Collections worthless, and am wondering if others feel the same. If these are replacing WorkApps I have so many questions. The biggest gap by far (IMO) are managed references. If the whole point of Collections is to control access and only share certain things, then this is not going to work for me. For example, I…
Can input from one form separate to two rows when they input to sheets based on conditions?
We currently ask people to select one form per one need for referral into a program. For example, I want to submit a referral to a community health team and another one to behavioral health team. I need to submit two separate forms because each form submission will create one row to master task list where it then auto move…
Tracking changes
Hi, I'm wondering how to track changes that other team members have made in our shared sheets, if someone could let me know how to do this or if there's a report I can pull down, that'd be great.