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?
Qrew LegendWell, 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.
Qrew Legendnp, 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!
Qrew LegendThere 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..
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:
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:
Qrew LegendTo 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
.
