Forum Discussion

ChristineKirk's avatar
ChristineKirk
Qrew Cadet
2 months ago

Color coding report based on Date Modified

Hi all! I'm trying my hand at color-coding a report based on any records updated in the past 7 days. I found a few similar threads/tried to emulate the formulas, but it's giving me guff because Date Modified is a Date/Time field.

My not-working formula is:

If(
[Date Modified]<=Today()-Days(7),"green"

 

My error says "The operator '<' can't be applied on types datetime, date

 

#SendHelp! Thanks!!

  • Fabulous! Worked!

    A sub-thought - is there a way to actually trigger this based on if a specific field was updated in the last 7 days -- without doing change logs on that field?

    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend

      Well, somehow you need to trap that date.  Probably a Pipline to detect the change of a field and update a date fieldf with the current date.

  • np, that built in field for [Date Modified] is actually a Date / Time field type. Soi we need to convert it to be just the Date.  Try this:

     

    IF(
    ToDate([Date Modified])<=Today() - Days(7),"green")

  • Ok - no pipelines experience yet. Here's an idea I'm trying.

    New field - Formula Date - and then color code report based on whether that's changed in the past 7 days.

    My not-quite-working new formula:

    If(Changed([Present Status Brief Detail])>= Today-Days(7), "")

    Error Detail:

    On "Changed" function - "The number of arguments supplie ddo not meet the requirements of the function Case. The function is defined to be If (Boolean condition1, result1, ..., else-result

     

    Bonus Wishes:

    It would be really awesome if I can actually tie this to a specific weekday -- if that field has been updated since last Wednesday. This way, when we're writing our quick team notes, we can skim whole reports of statuses, and the recently-updated statuses will stand out.

     

    Thank you, Mark!

    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend

      There is no function called "Changed", so you can't just invent a function which don't exists.  Can you you telll me if you are using Legacy Style forms or New style forms and also if all edits will be on forms as opposed to users using Grid Edit.  There may be a simple way to trap the date a certain field was changed if you happen to be using Legacy Forms..

       

      • ChristineKirk's avatar
        ChristineKirk
        Qrew Cadet

        Thanks, Mark! I used Google to try to find that hah - that's the whole "experimental" disclaimer of AI, I guess! 🤣

        I'm using mostly new style forms, though I have a few handful legacy forms left. Last I knew (this may have updated), by default, tables will include one legacy template that can't be deleted.

        I coudl make a legacy form for the purposes of this trap though -- the activity I'm doing is very specific to our team huddles, and the new field I created "Present status breif detail" was created specifically for this purpose, so it could absolutely live alone on its own Legacy-style form. Typically, my staff are grid-editing the detail each week though, not using a form. In my mind, I'd love to find a way to essentially have it highlight any records that had a status update, for ease of viewing the team-wide updates.

  • OK I am going to wade into this. I'm guessing, but double-checking here if my thinking is correct.... the way to do this and achieve what I want will be to:

     

    1. Create a new field to capture date my desired field is updated. We'll call it "Status Last Updated"
    2. Create pipeline with logic that says, "If the record is updated and "Status Detail" changes, update "Status Last Updated" to "Today""
    3. Go back to my report that I want color-coded, and now color code it based on that date

     

    Is that accurate?

     

    A different question/problem that I'm trying to fix on the same report. I'd like to use my report to show high level which buckets a partner has completed through implementation - using simple checkboxes that are automated. Pipelines for this? Or formula box? A few key implementation steps do log dates, so it's not as simple as "This field = This", because that field always equals a string of changes. But on the report, I just want simple, clean checkboxes that don't require manual fill-ins for folks.

     

    Thank you!!!!!!!!!!!!!

    • ChristineKirk's avatar
      ChristineKirk
      Qrew Cadet

      Coming back to update actually. Pipeline worked! Woo!!!! Took some learning how to write the "Today" code (I used {{time.now}}).

       

      Still remaining two items:

       

      • Is there a way to color code specifically anything since the prior Wednesday? Currently I'm using a code that says anytime in the last 6 days..... but if I can keep it to "since last Wednesday", even better
      • My subsequent question which is unrelated to this one - putting that here to consolidate the outstanding items:
        • I'd like to show the status of implementation across 4 key buckets (OR actually it would be really cool to even show percentage of each bucket perhaps, based on the sub-tasks on the table).... but for now, a checkbox showing whether each implementation bucket is or is not finished would be great. Presently, I've got it just reporting out on the final step of each stage - but one of them is a log-edits field and so that's a lot to look at. Looking for ways to automate that into a checkbox or percentage of steps!
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        To help answer the first part of your question, here is a formula for a formula date field which will calculate to the previous Wednesday. The reason the first line is commented out is so you can have a test date in your table for manual data entry just to see how it behaves on different days of the week fort "Today".

         

        //var date TodaysDate = [Date Test];
        var date TodaysDate = Today();
        var number DaysToSubtract =  
        Case(DayOfWeek($TodaysDate),
        0,4,
        1,5,
        2,6,
        3,7,
        4,1,
        5,2,
        6,3);
        $TodaysDate - Days($DaysToSubtract)

        The second part of your question is more difficult to answer because you haven't really describe the structure of your app and the different fields and von. I will say however that extracting data out of a history of a log field is extremely messy and in the end will be a dead end. So you'll have to describe more about how are you were tracking these dates. If you do need to track dates that certain fields are updated nice just having some kind of child table which will record the history of those dates because the formulas to parse those log fields is pretty horrible