Forum Discussion

AaronSnow's avatar
AaronSnow
Qrew Cadet
7 years ago

Need Help writing a formula

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?
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    Do you also have a field called [Due Date]?  Is that how we know if the record is past due?

  • AaronSnow's avatar
    AaronSnow
    Qrew Cadet
    7 years ago
    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
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago

    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's avatar
    AaronSnow
    Qrew Cadet
    7 years ago
    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?




  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago

    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's avatar
    AaronSnow
    Qrew Cadet
    7 years ago
    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�
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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")