Discussions

 View Only
Expand all | Collapse all

KanBan Color Coding Based on a date passed

  • 1.  KanBan Color Coding Based on a date passed

    Posted 01-20-2023 11:24
    Oh man do I need some help... I have a kanban that i am trying to color code based on a due date that has passed. So I have a status field and when that status changes to for example "Status 1", a dynamic form rule logs in the date in another field... based on that date, within 2 days, green. 4 days, yellow. so on, red.... BUT WHEN STATUS CHANGES to "Status 2" it logs in the date in a different field and the same concept applies. So I want the ticket colors to change based on how long a status has not been changes. Maybe I could change it so whenever status changes, it logs a new date in the same field. I am pretty lost and could use some help! Thank You in advance

    ------------------------------
    Eddy Elasmar
    ------------------------------


  • 2.  RE: KanBan Color Coding Based on a date passed

    Posted 01-20-2023 12:30
    Sure we can help.  Can you state very clearly  and as concisely as possible as to the names of these date fields and what the logic should be for the colours?


    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: KanBan Color Coding Based on a date passed

    Posted 01-20-2023 13:15
    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
    ------------------------------



  • 4.  RE: KanBan Color Coding Based on a date passed

    Posted 01-22-2023 04:50
    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
    ------------------------------



  • 5.  RE: KanBan Color Coding Based on a date passed

    Posted 01-23-2023 08:12
    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
    ------------------------------



  • 6.  RE: KanBan Color Coding Based on a date passed

    Posted 01-23-2023 09:02
    Quickbase can do many of the colours that are just expressed in regular words but here is a nice site dine by  @Laura Thacker Laura who is also a  another QuickBase Solution Provider,  which has basically all the colours you would ever need and you would use the hex codes instead of the word green.  

    https://laurahillier.quickbase.com/db/bhy8pumtp

    As for you other issue, yes that is a fatal flaw in my suggestion.  I did not realize that the status was being used for the Kanban column choices. 

    So one way is to retain your status field  for the kanban choices.
    Then retain your logged field.
    Then retain the formula field for the most recent status update which is a formula field.

    Then use an "ACTION" to change the value of the logged field to be the value of the Status field.  Fire the ACTION when the record is changed and the status field changes.  
    Also use a pipeline for this but the trouble is the pipeline could take five or 10 seconds to run so not as good a user experience as a very old-fashioned but fast running ACTION.  




    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: KanBan Color Coding Based on a date passed

    Posted 01-23-2023 10:19
    I was actually able to use a dynamic form rule for this. 
    I added another field copied from status. Names it shop status...
    When [Shop Status] IS NOT EQUAL TO "Blank"
    Action Change [Status Update] to "The value in Shop Status"

    Then unchecked the fire changed box.

    Yes ! Laura is awesome I am very familiar with her work. As she is mine. 
    Thank you so much for your help. It works and looks awesome.

    ------------------------------
    Eddy Elasmar
    ------------------------------



  • 8.  RE: KanBan Color Coding Based on a date passed

    Posted 01-23-2023 10:21
    Yes, Dynamic form rules will work if your changes are being edited on a form.  I thought you were dragging records between Kanban columns to change the status.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: KanBan Color Coding Based on a date passed

    Posted 01-23-2023 11:24
    Mark,
    I am changes the status by dragging the record between the KanBan. I have tested it, it is still triggering normally and logging in the date as it would if I manually changed the dropdown.

    Do you know something I don't here?
    Thanks for all your help. Pretty long thread so I appreciate the quick turnaround

    ------------------------------
    Eddy Elasmar
    ------------------------------



  • 10.  RE: KanBan Color Coding Based on a date passed

    Posted 01-23-2023 11:27
    If it works it works. What happened to this comment.

    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. 

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------