This morning, I did a proof of concept of "Targeted Auditing" and would like to share it with the Community.
I thought of the idea years ago but never made time for until my unsatisfying response to this thread twigged my memory. As below, my example records the changes to a particular "Target" custom text parameter in its "Target History" custom calculated parameter counter part...and to my delight, actually works!
The formula turned out to be why-didn't-I-try-this-sooner? simple:
LEFT(IF(LEFT(Target History,LEN(IF(ISBLANK({Target}),"-",{Target})))={Target},Target History,CONCAT(IF(ISBLANK({Target}),"-",{Target})," (",$$NOW,") | ",{Target History})),2000)
And does the following:
- limits the Target History to the most recent 2000 characters (staying within the Workfront database limit)
- checks if the current Target value (treating it as "-" if it is blank) matches the front of the Target History value
- if they match, leaves the Target History "as is"
- if they don't match, replaces the Target History with the (latest) Target, followed by the current date, a vertical bar, and the previous Target History, which (similarly) preserves the previous value(s) and the time(s) entered
I've included a screenshot below so you can visualize how it works.
There are lots of other interesting bells and whistles you could add to what you capture (e.g. who, the time of day, etc.), so for those of you who find this of use and do make any cool enhancements, I encourage you to share them under this thread.
Have fun!
Regards,
Doug
Solved! Go to Solution.
Why, thank you, Heather. It was a satisfying one to finally figure out.
As for your excellent Status-in-English question, I'd suggest the crude but effective method of using a series of IF statements to transform each "Status" (e.g. CUR) to its appropriate counterpart (e.g. "Current") throughout the formula.
The pattern looks like this, being careful to let any unknown (future) Status drop out as itself in the final else:
IF(Status="CUR","Current",IF(Status="PLN","Planned",IF(Status=...,...,Status)))
Regards,
Doug
Views
Replies
Total Likes
I've been playing around with this formula... and it's fabulous!
I've got this in my calculated field... LEFT(IF(LEFT(Status History,LEN(IF(ISBLANK(Status),"-",Status)))=Status,Status History,CONCAT(IF(ISBLANK(Status),"-",Status)," (",$$NOW,") | ",Status History)),2000)
which gives me this for the project I'm testing it on...
I know CUR is the key for Production, GPO is the key for a custom status that we use. My question is... is there a way to replace that status key with the status names so someone other than me knows what the status history is for this project??
Views
Replies
Total Likes
Why, thank you, Heather. It was a satisfying one to finally figure out.
As for your excellent Status-in-English question, I'd suggest the crude but effective method of using a series of IF statements to transform each "Status" (e.g. CUR) to its appropriate counterpart (e.g. "Current") throughout the formula.
The pattern looks like this, being careful to let any unknown (future) Status drop out as itself in the final else:
IF(Status="CUR","Current",IF(Status="PLN","Planned",IF(Status=...,...,Status)))
Regards,
Doug
Views
Replies
Total Likes
Super helpful post and calculation.
Is there a way to calculate the cumulative time a task was in a specific status?
How long total was the task In Progress? In Business Review? etc?
Views
Replies
Total Likes
Thanks @JamesM-MERGE,
Yes, having done so for Issues (many years ago), it is possible to calculate the cumulative time a task was in a specific status (e.g. In Progress, In Business Review, etc.), but it is advanced enough that I suggest ZJD and AtApp discuss a joint venture excursion offline -- I'm at doug.denhoed@atappstore.com
Regards,
Doug
Views
Replies
Total Likes
Views
Replies
Total Likes
Huzzah!
A Guinness in Dallas would settle us up quite nicely, Heather. 😃
Regards,
Doug
Views
Replies
Total Likes
HI Heather,
Would you be able to share the final calculation you got working on this? I am having trouble with the positioning and format of the "status in english" IF statement translations of the root status codes. Thanks in advance.
Regards,
Steve
Views
Replies
Total Likes
Sure Steve, I actually wound up creating 2 custom fields.
The first looks something like this and gives the current status, with additional IF statements for more custom statuses.
IF(Status="INP","In Progress",IF(Status="PLN","Planning",IF(Status="CPL","Completed",Status)))
I've followed that up with another calculated field to get the history as the status changes, ("Status History" and "Current Status" are the names of my custom fields)
LEFT(IF(LEFT(Status History,LEN(IF(ISBLANK(Current Status),"-",Current Status)))=Current Status, Status History,CONCAT(IF(ISBLANK(Current Status),"-",Current Status)," (",$$NOW,") | ",Status History)),2000)
Hope that helps.
Views
Replies
Total Likes
Views
Replies
Total Likes
Hi Heather,
Here's the code I am working with now in my version of the calculation field "Status History".
LEFT(IF(LEFT(Status History,LEN(IF(ISBLANK(Status),"-",Status)))=Status,Status History,CONCAT(Last Updated By.Name," - ",(IF(ISBLANK(Status),"-",Status))," (",$$NOW,") | ",Status History)),2000)
This is returning some strange behavior. I created a new project, some new test tasks and went to check the Status History field on the custom form for each of the two new test tasks. I found that the user/status/datestamp was being applied more than once almost as if every update on the task to anything was causing a new datestemp occurance to be applied. . The calc is recording the default NEW status automatically and multiple times for the new task without any real status change having been done. Can anyone tell me where this code has gone wrong? I am attaching a screenshot of the new task's custom form showing the Status History field so you can see the stamp of the initial NEW status of the field and the repetition of the data.
Thanks in advance for any ideas on this.
Regards,
Steve
Views
Replies
Total Likes
Hi Steve,
The LEFT part of the logic assumes that Status will be the first (leftmost) information within your Status Update. However, since you're currently starting with the Last Updated By.Name, when the LEFT compares the first three characters of that person's name against the current status, since they (likely) don't match, it misconstrues that there's been a status change, and creates a new (unnecessary) Status History entry.
Fortunately, it's an easy fix, if you simply rearrange your formula to match the original LEFT assumption, like this:
LEFT(IF(LEFT(Status History,LEN(IF(ISBLANK(Status),"-",Status)))=Status,Status History,CONCAT((IF(ISBLANK(Status),"-",Status)),Last Updated By.Name," - "," (",$$NOW,") | ",Status History)),2000)
Regards,
Doug
Views
Replies
Total Likes
Views
Replies
Total Likes
Views
Replies
Total Likes
Views
Replies
Total Likes
Back by popular request from this post...
If you'd like to set up Targeted Auditing to keep track of the Status of Tasks as they change between NEW, INP, HLD, and CPL, using the full Status labels (New, In Progress, On Hold, and Complete, thanks to @Heather Kulbacki‚ for her original improvement above), INCLUDING (as is @Kristina Walton‚) the name of the person who made such an edit, I invite you to do the following:
With these in place, as each Task of Interest changes from one Status to another, the Status History will show "grow and show" a Targeted Audit record of what's changed, using the pattern of "Status Label on M/D/YYYY by User Name".
In this fashion, in addition to sifting through Notes records, you can quickly focus on changes to the Task Status over time.
Regards,
Doug
Current Status:
----------------------
IF(Status="NEW","New"
,IF(Status="INP","In Progress"
,IF(Status="HLD","On Hold"
,IF(Status="CPL","Complete"
,Status
)
)
)
)
Status History:
---------------------
LEFT(
IF(LEFT(Status History,LEN(IF(ISBLANK(Current Status),"-",Current Status)))=Current Status
, Status History
,CONCAT(IF(ISBLANK(Current Status),"-",Current Status)," (",$$NOW," by ", Last Updated By.Name , ") | ",Status History)
)
,2000
)
Doug,
What is "Target" and what is "Target history"? Are these 2 calculated fields (date or Text) that you created?
Trying to do this formula in my project report - but i'm getting confused on what I put in text mode, vs what I just create as a custom field. Thanks for clarification.
Views
Replies
Total Likes
Hi Benetta,
In my opening post above, the "Target" custom text parameter is the one that I am interesting monitoring for changes to it, and its "Target History" custom calculated parameter counter part contains the formula that will accumulate the history of the changes to the target as they are made.
Over time now (thanks to @Heather Kulbacki‚ and others who've since joined in), the whole "Targeted Auditing" concept has broadened in that it can be used to track anything as a Target, whether Custom Parameters (text, number, date, dropdown, etc.) or a Built In Field (e.g. Status, Project Owner, Percent Complete, etc.)
Good luck with it, and I'm interested to hear how you make out!
Regards,
Doug
Views
Replies
Total Likes
I really love this idea, Doug! I've had this post open in a browser tab for a long while now and just got some time to noodle it out. I tried adding your Target & Target History fields to my Project Custom Form (in Preview environment), by copying/pasting your OP code, but I can't get it to take the calculation. Any idea what I'm missing?
Views
Replies
Total Likes
Views
Likes
Replies