I am trying to colorize a row based on a 4 hour time span after the date/time in the field is reached. Any thoughts on how to do that?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I'm creating a table to monitor activity of a customer's event, which typically lasts 4 hours. I have set the time/date for the event, and created a report to show all of the customer's events happening on that day. This provides the team with a visual dashboard for start/stop activity. How would I accomplish this?

Photo of Kelly

Kelly

  • 1,812 Points 1k badge 2x thumb

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
Try this as a row colorization formula at the bottom of a table report.

If(now() > [My Date/Time field] + Hours(4),"yellow") 
Photo of Kelly

Kelly

  • 1,812 Points 1k badge 2x thumb
I'm getting this error:
Formula syntax error

Please check the syntax of your entire formula. Possible culprits are a mismatched set of parentheses, missing quotes, or an extra bracket.

If([Notes]>"0", "pink",
If(now() > [My Date/Time field] + Hours(4),"yellow")

I do have another condition in the field, so the formula looks like this:
If([Notes]>"0", "pink",
If(now() > [My Date/Time field] + Hours(4),"yellow")
Photo of Kelly

Kelly

  • 1,812 Points 1k badge 2x thumb
I just realized I didn't change the field.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
If(
[Notes]>"0", "pink",
now() > [My Date/Time field] + Hours(4),"yellow")

The sequence also matters. In the sequence above, if its pink, then it will never be yellow.
Photo of Kelly

Kelly

  • 1,812 Points 1k badge 2x thumb
That's working but the 4 hour time span does not limit the duration. All events from yesterday are still yellow.
Photo of Kelly

Kelly

  • 1,812 Points 1k badge 2x thumb
I made it < and that worked. I should've reached out to you hours ago!! Thank you!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
Right, I had that backwards. Glad its working.
Photo of Kelly

Kelly

  • 1,812 Points 1k badge 2x thumb
I'm not out of the woods yet. I want this to generate according to the time, and I just put in 5:50 (Time is now 5:43), and it's yellow. The idea is that it shouldn't activate until the start time. How would I make that modification?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
It seems to me that original formula was right then. Flip the sign to >.
Photo of Kelly

Kelly

  • 1,812 Points 1k badge 2x thumb
Well, no, because it highlights all of the dates in the past, and doesn't highlight the current time at all. I just think that the < does not provide a specific enough reference to the date. If I change the date to 4 hours prior to the current time, the yellow goes away. That is correct. However, if I put in a time to anticipate the activation, such as a few minutes prior to the actual start time, it already displays. I guess I'll have to play with it to see where the activation point is.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
Can you tell me what time zone you are in yourself, and also what time zone your app is set to. The time zone is set in app properties.
Photo of Kelly

Kelly

  • 1,812 Points 1k badge 2x thumb
Yes, it's in the Eastern Time Zone.
Photo of Kelly

Kelly

  • 1,812 Points 1k badge 2x thumb
I have set the start time to 11:45pm, and it still appears yellow.
Photo of Kelly

Kelly

  • 1,812 Points 1k badge 2x thumb
Is it possible that the order of the equation is backwards? Because if i go 4 hours in the other direction, the yellow goes away, but if I revers the <, everything from the past turns yellow.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
So the goal is to color yellow when it's now 4 hours or more past the date and time in the date/time field?
Photo of Kelly

Kelly

  • 1,812 Points 1k badge 2x thumb
The goal is for the yellow to appear when the time in the field is reached, and stay yellow for 4 hours beyond that.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
That goal was not clear to me.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
If(
[Notes]>"0", "pink",
now() >[my date / time field] and now() <= [my date/time field] +Hours(4),"yellow")
Photo of Kelly

Kelly

  • 1,812 Points 1k badge 2x thumb
That worked! Thank you so much! The only think that would make it better is if you didn't have to refresh the screen to get it to pop up :)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
There is a way to make a dashboard page auto refresh.  Is that what you are trying to do?
Photo of Kelly

Kelly

  • 1,812 Points 1k badge 2x thumb
yes!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
can you post that as a new question?
Photo of Kelly

Kelly

  • 1,812 Points 1k badge 2x thumb
Sure.