How to filter view by multiple text strings

[Michael_Morris2]

I have created a formula referencing key dates that returns the values of “Past”, “Present”, or “Future”. I would like to filter a view showing only “Present” or “Future” records. I also want to filter out any record where the status is “Rejected”

I can do (PPF means Past, Present or Future):

Where PPF contains Present
Or PPF contains Future

This satisfies my first requirement. But I’d like to add:

And Status is not Rejected

But it only allows another “Or” statement, not an “AND” statement

Alternately (preferably) I’d like to be able to say:

Where PPF contains “Present” or “Future”

Perhaps my syntax is wrong, but the filter for text fields doesn’t seem to offer boolean operations like this.

My work around is to have a single-select field rather than formula where I can just choose “Past”, “Present” or “Future”. This allows me to choose the “is any of” option in the filter. But then I have to update these fields manually when the dates come and go.

Suggestions?

[kuovonne · Accepted solution]

When a filter contains multiple conditions, they must all be joined by AND or OR, not a mix.

In order to work around this limitation, you can create a new formula field that combines the AND and OR logic. Then filter on this new formula field.

This formula should help you get started. You may need to adjust it slightly depending on your field types.

IF(
  AND(
    OR( 
      FIND("Present", {PPF}),
      FIND("Future", {PPF})
    ),
    NOT({Status} = "Rejected")
  ),
  "Pick Me!"
)

[Michael_Morris2]

Thanks so much for your help! It took me awhile to find that you have an extra comma after the “NOT” line, but I figured it out. This is a little more cumbersome than using the filter settings, but your solution does exactly what I wanted!

[kuovonne]

Thank you, and good catch on the extra comma. I edited the formula if case anyone else has the same situation.