Discussions

Expand all | Collapse all

Need Help writing a formula

  • 1.  Need Help writing a formula

    Posted 01-14-2019 21:04
    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?


  • 2.  RE: Need Help writing a formula

    Posted 01-14-2019 21:06
    Do you also have a field called [Due Date]?  Is that how we know if the record is past due?



  • 3.  RE: Need Help writing a formula

    Posted 01-14-2019 21:39
    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


  • 4.  RE: Need Help writing a formula

    Posted 01-14-2019 22:00
    Let me be more specific

    How do I know if his is true?

    [Date Field1] is past due




  • 5.  RE: Need Help writing a formula

    Posted 01-14-2019 22:08
    If it is after today's date.


  • 6.  RE: Need Help writing a formula

    Posted 01-14-2019 22:18

    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)




  • 7.  RE: Need Help writing a formula

    Posted 01-15-2019 15:20
    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?






  • 8.  RE: Need Help writing a formula

    Posted 01-15-2019 15:32

    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")


  • 9.  RE: Need Help writing a formula

    Posted 01-15-2019 16:43
    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�


  • 10.  RE: Need Help writing a formula

    Posted 01-15-2019 16:47
    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")




  • 11.  RE: Need Help writing a formula

    Posted 01-15-2019 18:46
    Works Beautifully! Thank you for your support.


  • 12.  RE: Need Help writing a formula

    Posted 01-15-2019 18:47
    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.


  • 13.  RE: Need Help writing a formula

    Posted 01-15-2019 20:58
    Thanks again. i will be keeping this one as a reference for sure.