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 ...
Forum Discussion
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:
- Create a new field to capture date my desired field is updated. We'll call it "Status Last Updated"
- Create pipeline with logic that says, "If the record is updated and "Status Detail" changes, update "Status Last Updated" to "Today""
- 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!!!!!!!!!!!!!
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
Qrew 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
.
