Need Help writing a formula

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
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”
Photo of Aaron Snow

Aaron Snow

  • 190 Points 100 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Do you also have a field called [Due Date]?  Is that how we know if the record is past due?

Photo of Aaron Snow

Aaron Snow

  • 190 Points 100 badge 2x thumb
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
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Let me be more specific

How do I know if his is true?

[Date Field1] is past due


Photo of Aaron Snow

Aaron Snow

  • 190 Points 100 badge 2x thumb
If it is after today's date.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb

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)


Photo of Aaron Snow

Aaron Snow

  • 190 Points 100 badge 2x thumb
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?




Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb

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")
Photo of Aaron Snow

Aaron Snow

  • 190 Points 100 badge 2x thumb
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”
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
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")


Photo of Aaron Snow

Aaron Snow

  • 190 Points 100 badge 2x thumb
Works Beautifully! Thank you for your support.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
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.
Photo of Aaron Snow

Aaron Snow

  • 190 Points 100 badge 2x thumb
Thanks again. i will be keeping this one as a reference for sure.