When the Expiration Date is On or Before Today, Change the Contract Status to Expired
Hello:
I have a contract application.
Goal: When the Contract Expiration Date field is on or before today, and the Exception to the Expiration Date Dynamic Form Rule field is unchecked, I want the Contract Status field to automatically change to Expired.
Question: What is the best method to accomplish the above goal, form rule, text formula, or pipeline?
1. I tried the below form rule. However, it works intermittently. I still see expired contracts that show as active
2. I created a formula - Text field named Contract Status Formula, but I am unsure whether my formula is correct.
If(
Today() <= [Contract Expiration Date], "Expired")
3. I tried to create a pipeline. My first attempt was to Search Records/Update Record. My second attempt was to On New Event/Search Records/Update Record. However, I know I am missing a step.
Any step-by-step guidance would be greatly appreciated.
Thank you,
Roula
Hi Roula,
Thanks for the clarification. Whenever you're reading the logic of a QB formula you should replace the commas with the words "then" and "otherwise". If you do this the formula will read more or less like a sentence.
If we do this, then your formula, line by line, reads like this:
The first comma is always "then" and any comma after that becomes an "otherwise". So the following formula should work. Apologies for using all caps in my last formula, old habits die hard.
If([Contract Status] = "Active" and [Contract Expiration Date]>=Today(),
"Expired")I hope this helps!!
-Maria