I'm trying to write a formula around a date field.

  • 0
  • 1
  • Question
  • Updated 6 years ago
  • Answered

I'm trying to write a formula around a date field.
Here's the scenario:
1) I have a date field called "Expiration date" and a text field called "Cust number"
2) I need to be able to set the "Expiration Date" to a future date
3) Once that date arrives the "Cust Number" can no longer be available for query. I don't want to delete the "cust number"
but was thinking I can have it MOVED to either an archive table or a new field called "Expired Cust number"

Desired goal example:
From the html frontend search form
1) User queries "A1234" in the "Cust Number field" after the "Expiration Date"
2) Query doees not return results because the "Cust Number" is no longer available

All help is much appreciated. Let me know your input.

Thank You

Photo of jansson17


  • 0 Points

Posted 6 years ago

  • 0
  • 1
Photo of Mark_Shnier


  • 700 Points 500 badge 2x thumb
Create a new formula text field called [Cust number for Queries]

The formula would be
IF([Expiry Date]>Today(),[Cust number])

What that says is that if the Expiry date is in the future, then the Cust number is populated in to that field, else its silent, which means null.

So, set up say an <ask the user> report but target that calculated field [Cust number for Queries] instead of the raw [cust number] field.