Forum Discussion

RoulaHabbal's avatar
RoulaHabbal
Qrew Cadet
7 months ago

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 ...
Pipelines
  • MariaPeralta's avatar
    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

     

MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew 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's avatar
    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