How to filter view by multiple text strings

Question

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 Answer

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!"
)

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!

Michael_Morris2 · Answer

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!"
)

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!

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded