Skip to main content

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?

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

)

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!

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!


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

Reply


Terms & Conditions