Skip to main content
Question

Need Help writing a formula

  • January 15, 2019
  • 0 replies
  • 42 views
I am new to Quick Base and new to writing any sort of HTML at all so i apologize in advance for the clumsy logic. I this case i am using a form where each section of the form is driven by due dates. I have tried using rules within the form but i need the "Status" (Late / On Track) to update even when i am not within the form its self. Below is my sad attempt at writing how i think the logic should work. 

Show status as ?On Track? unless any of the following are true...

-[Date Field1] is past due and [Check Box 1] is not checked then, show Status as ?Late?

-[Date Field 2] is past Due and [Field1] or [Field2] or [field3] are blank then, show Status as ?Late?

-[Date Field 3] is past Due and [Check Box2] and [Check Box3] and [Check Box 4] are not checked then, show Status as ?Late?

-[Date Field 4] is past Due and [Check Box5] and [Check Box6] and [Check Box 7] are not checked then, show Status as ?Late?
    This topic has been closed for replies.

    Do you also have a field called [Due Date]?  Is that how we know if the record is past due?

    • AaronSnow
    • Author
    • Registered
    • January 15, 2019
    Date Field 1, Date Field 2, and Date Field 3 are separate date fields. There is also a Status field to show Late or On track
    Let me be more specific

    How do I know if his is true?

    [Date Field1] is past due


    • AaronSnow
    • Author
    • Registered
    • January 15, 2019
    If it is after today's date.

    OK, try this.  The tests are processed in this sequence, so you will need to ensure that the sequence is correct.

    IF(

    [Date Field1] < Today() and not [Check Box 1], "Late",

    [Date Field 2]  < Today() 

    and 

    (Trim([Field1])="", or Trim([Field2])="" or Trim([field3])=""),  "Late",

    [Date Field 3]  < Today() 

      and not [Check Box2] 

      and not [Check Box3] 

      and not [Check Box 4], "Late",

    [Date Field 4] < Today()
     and not [Check Box5]
     and not [Check Box6]
     and not [Check Box 7],"Late")


    If the above does not pass a syntax check and you can't fix it please post the formula and the error message but copying and pasting into the forum (not a screen shot)


    • AaronSnow
    • Author
    • Registered
    • January 15, 2019
    This is very close!

    There are just a couple things that need tweaking i think...

    -Status "Late" goes away once [Check Box 2], [Check Box 3], or [Check Box 4] is checked - Can this change to require that all three must be checked? Same goes for [Check Box 5], [Check Box 6], and [Check Box 7]?

    - Status does not change to "On Track" but I'm guessing you left that formula out for now to make sure this is working?




    I have added that last "On Track" result.


    As for the other change, can you clarify if you feel that your original question was mis-stated or if my formula does not match the result stated in your original question.

    If you need to restate your original statement, please restate the whole statement with your revision, and note that the tests will be performed in the order of your words.



    IF(

    [Date Field1] < Today() and not [Check Box 1], "Late",

    [Date Field 2]  < Today() 

    and 

    (Trim([Field1])="", or Trim([Field2])="" or Trim([field3])=""),  "Late",

    [Date Field 3]  < Today() 

      and not [Check Box2] 

      and not [Check Box3] 

      and not [Check Box 4], "Late",

    [Date Field 4] < Today()
     and not [Check Box5] 
     and not [Check Box6] 
     and not [Check Box 7],"Late", "On Track")
    • AaronSnow
    • Author
    • Registered
    • January 15, 2019
    It is very likely that there were errors in my original statement. Maybe this will work?

    Show status as �On Track� unless any of the following are true...

    -[Date Field1] is past due and [Check Box 1] is not checked then, show Status as �Late�

    -[Date Field 2] is past Due and [Field1] or [Field2] or [field3] are blank then, show Status as �Late�

    -[Date Field 3] is past Due and [Check Box2] and or [Check Box3] and or [Check Box 4] are not checked then, show Status as �Late�

    -[Date Field 4] is past Due and [Check Box5] and or [Check Box6] and or [Check Box 7] are not checked then, show Status as �Late�
    OK try this version

    IF(

    [Date Field1] < Today() and not [Check Box 1], "Late",

    [Date Field 2]  < Today() 

    and 

    (Trim([Field1])="", or Trim([Field2])="" or Trim([field3])=""),  "Late",

    [Date Field 3]  < Today() 

      and 

        (not [Check Box2] 

          or not [Check Box3] 

          or not [Check Box 4]), "Late",

    [Date Field 4] < Today()
     and
    (not [Check Box5] 
      or not [Check Box6] 
      or not [Check Box 7]),"Late", "On Track")


    • AaronSnow
    • Author
    • Registered
    • January 15, 2019
    Works Beautifully! Thank you for your support.
    Ok great, thx for letting me know.  By following that syntax you can probably write your own formuals in future.  That one was was a bit complicated for sure.
    • AaronSnow
    • Author
    • Registered
    • January 16, 2019
    Thanks again. i will be keeping this one as a reference for sure. 
    Terms & ConditionsAccessibility statement