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

Pipelines

MariaPeralta
7 months ago
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

DonLarson
Qrew Elite
7 months ago
A form rule only works when someone opens the record with that particular form. I suggest that you build your Contracts like this:
The Contract Status Change Table does a lot for you. It is a history of the Contract, when and who made the changes. You can also require a Note for the Change.
So with this architecture, I would build a Scheduled Pipeline that searches the Contract table once a day for Contracts past their date without the Exception being false and the current status is not Expired.
When it finds a record that meets that criteria, the Pipeline adds a record to the Contract Status Change table.
- RoulaHabbal
  Qrew Cadet
  7 months ago
  DonLarson,
  
  Thank you very much for your time and willingness to help me.
  Roula
- RoulaHabbal
  Qrew Cadet
  7 months ago
  Thank you, Don.
  I appreciate your time and help.
  Roula
  - DonLarson
    Qrew Elite
    7 months ago
    Roula,
    Did you solve your issue? Need any more info on this particular problem?
MarkShnier__You
Qrew Legend
7 months ago
I have a different suggestion. Like Don says, a form rule only fires when a human user (or maybe one of Elon's Robots) edits the record using the Forms user interface. Does not suddenly wake up in the middle of the night and noticed that the date has changed to tomorrow.
A formula is the best solution because it happens immediately and does not depend on a pipeline.
You formula looks correct but is not dealing with your exception field.
Maybe this is what you want
IF([Exception to the Expiration Date Dynamic Form Rule] = true, "Active",
Today() <= [Contract Expiration Date], "Expired",
"Active")
- RoulaHabbal
  Qrew Cadet
  7 months ago
  Hi Mark:
  
  Thank you. I appreciate your help and time.
  
  Since using a form rule is not the best method, in my case, I no longer need my checkbox field Exception to the Expiration Date Dynamic Form Rule.
  
  Updated goal: When the Contract Expiration Date field is on or before today, and the Contract Status field is Active, change the Contract Status field to Expired.
  How would I correct the first line of my formula?
  *************************************************
  IF([Contract Status], "Active",
  Today() <= [Contract Expiration Date], "Expired")
  *************************************************
  Thank you,
  Roula
MarkShnier__You
Qrew Legend
7 months ago
IF(
[Contract Status], "Active"
and

Today() <= [Contract Expiration Date], "Expired")
- RoulaHabbal
  Qrew Cadet
  7 months ago
  Good morning, Mark.
  Thank you. When I pasted the updated formula, I received "Expecting bool but found text."
  - MariaPeralta
    Community Manager
    7 months ago
    Hi Roula,
    
    The above assumes that your field names and types are the same as Mark's. You can try this instead:
    
    If([Contract Status] = "Active" AND Today()<= [Contract Expiration Date],
    "Expired")
    
    Though you'll still likely need to make tweaks to make it fit your app and your use case.
    
    Best,
    Maria

Forum Discussion

When the Expiration Date is On or Before Today, Change the Contract Status to Expired