KanBan Color Coding Based on a date passed

EddyElasmar
Qrew Member
2 years ago
Shop Status is a drop down field which contains:
In Queue
Pulling
Cutting
Milling
Sealing
Building/Boxing
Complete & Ready to Ship

When a project is "In Queue" - KanBan ticket is green always

When a project is "Pulling" - Kanban ticket is green for the first 24 hours, yellow for the next 48, red after that

When a project is "Cutting", "Milling", "Sealing", & "Building/Boxing" - Kanban ticket is green for the first 48 hours, yellow for next 48 hours, red after 96 hours.

"ready to ship" green

so basically each time that [Shop Status] Field changes, the timer needs to reset. Weather that could all be encoded or I need fields around that like i have currently a dynamic form rule to fill in a different field the date once [Shop Status]="Pulling"

(Named those date fields: [Pulling Date Snapshot on Shop Status Change] - I know they're not technically snapshot fields... just dynamic form rules)

This is what I have which is wrong and not much.
Case([Shop Status],"In Queue","#23ff00",
Case([Shop Status],"Pulling","#23ff00",
Case([Shop Status],"Complete & Ready to Ship","#23ff00",
if([Shop Status]="Pulling" & ([Pulling Date Snapshot on Shop Status Change] -today() >hours(0) & ([Pulling Date Snapshot on Shop Status Change] -today() >hours(48),
"<div style=\"color:white;background-color:#9B0909;\">" & due: "&[Pulling Date Snapshot on Shop Status Change]" ))))

------------------------------
Eddy Elasmar
------------------------------
- MarkShnier__You
  Qrew Legend
  2 years ago
  One way to capture the date and time of the status change the status field's Properties to "Log Changes", and also to set it to log the time as well.
  
  I suggest renaming the field to be called [Status Update].
  Then make two new fields.
  
  [Status] as a formula text field:
  Trim(Right([Status Update],"]"))
  
  and [Status Date/ Time] as a formula Date / Time field:
  
  var text DateAndTime = Left(Right([Status Update],"["),18);
  var date TheDate = ToDate(Left($DateAndTime," "));
  var timeofday TheTime = ToTimeOfDay(NotLeft($DateAndTime,9));
  ToTimestamp ($TheDate, $TheTime)
  
  OK, so now you have a clean field for the Status and a clean date time field for the date and time of the most recent status change.
  
  Let's now do a new formula numeric field for the [# Hours in Status]
  
  ToHours(Now() - [Status Date/ Time])
  
  Now we can do the formula for the color. I suggest making this a formula field too, called [Kanban Color] so you can easily see what's in it.
  
  //When a project is "In Queue" - KanBan ticket is green always
  IF(
  [Status] = "In Queue", "Green",
  
  //When a project is "Pulling" - Kanban ticket is green for the first 24 hours, yellow for the next 48, red after that
  
  [Status]= "Pulling" and [# Hours in Status] <=24, "green",
  [Status]= "Pulling" and [# Hours in Status] <=48, "yellow",
  [Status]= "Pulling" and [# Hours in Status] >48, "red",
  
  //When a project is "Cutting", "Milling", "Sealing", & "Building/Boxing" - Kanban ticket is green for the first 48 hours, yellow for next 48 hours, red after 96 hours.
  
  Contains("Cutting Milling Sealing Building/Boxing", [Status])
  and [Status]<>""
  and [# Hours in Status] <=48, "green",
  
  Contains("Cutting Milling Sealing Building/Boxing", [Status])
  and [Status]<>""
  and [# Hours in Status] <=96, "yellow",
  
  Contains("Cutting Milling Sealing Building/Boxing", [Status])
  and [Status]<>""
  and [# Hours in Status] >96, "red",
  
  //"ready to ship" green
  
  [Status] = "Complete & Ready to Ship", "green")
  
  All done, so now just set the Kanban colorization formula in the report to be [Kanban Color]. You cam also then conveniently use the same formula for row colorization on a regular table Report.
  
  ------------------------------
  Mark Shnier (Your Quickbase Coach)
  mark.shnier@gmail.com
  ------------------------------
  - EddyElasmar
    Qrew Member
    2 years ago
    Mark,
    
    This looks very promising!
    I have 1 issue & 1 question.
    
    Issue: I now cannot select any type of status for the KanBan to be based off of. As we switched the [Status Update] log the time.
    
    Question: should the colors in your formula be HTML Color codes or just leave it as "Green" & quickbase will know what to do as it just generates the word "Green" rather than highlights a color is what I think would happen.
    
    Please advise
    Thank you very much for your time
    
    ------------------------------
    Eddy Elasmar
    ------------------------------

Forum Discussion

KanBan Color Coding Based on a date passed