Targeted Auditing Proof Of Concept

[Doug_Den_Hoed__AtAppStore]

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

[Heather_Kulbacki]

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??

[Doug_Den_Hoed__AtAppStore · Accepted 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

[JamesM-MERGE]

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?

[Doug_Den_Hoed__AtAppStore]

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

[Heather_Kulbacki]

Perfect! That worked. You're my hero Doug !! We've been trying to create a field for so long that would give us a track record of project status changes and the date of each change. This is amazing!

[Doug_Den_Hoed__AtAppStore]

Huzzah!

A Guinness in Dallas would settle us up quite nicely, Heather. 😃

Regards,

Doug

[StevenHi2]

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

[Heather_Kulbacki]

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.

[StevenHi2]

HI Heather, Ah, there's the rub! I was trying to embed the Status translation to English directly in the Status History field. While that may be possible I like your method and will employ it. Thanks for sharing!! Cheers, Steve Steven Hirsch The Estee Lauder Companies Inc

[Heather_Kulbacki]

I also initially tried to do it all in one field and struggled with that. Glad it's working for you!

[StevenHi2]

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

[Doug_Den_Hoed__AtAppStore]

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

[Doug_Den_Hoed__AtAppStore]

On second thought, I'd suggest you CONCAT one additional dash, for legibility: 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 Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[StevenHi2]

This works much better Doug. I figured it was something to do with the LEFT statement and I did not realize it was only testing the first 3 characters of the field. Thanks so much for your expertise and responsiveness. ELC is grateful to you. Cheers, Steve Steven Hirsch The Estee Lauder Companies Inc

[ErikaGa]

Hi Doug, I am interested in your method above to hopefully capture changes on fields within custom forms. This way we have the original entry and then the updated entry, for historical capture. My first questions to you are: How did you set this up to capture the data? Is there a form I have to create, or do I add new fields to the form(s) we are using? My second questions to you are: Is this a calculated field, and just input your formulas above? I am assuming that "Target" is the original field name and "Target History" is maybe where the calculated field is applied. Hoping you can help. Erika Garrett Gulfstream Aerospace

[Doug_Den_Hoed__AtAppStore]

Thanks for your interest, Erika. To answer your questions: I set up my example to capture the Target History data right next to its Target, which struck me as an intuitive location. You can do likewise and add new fields to the form(s) you are using (either "beside", as I did, or perhaps in a separate "History" section); or if you prefer, technically, the history parameters could even be on a separate form. Yes, the Target History is a calculated field, and yes, you can just input my sample formulas, replacing "Target" with your existing field name and "Target History" being the new calculated field in which the calculation is applied. Regards, Doug Doug Den Hoed - AtAppStore

[Doug_Den_Hoed__AtAppStore]

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:

• if you don't have one already, create a Task level custom form such as "Targeted Auditing" and add it to all Tasks of interest
• on the Task level custom form, add two calculated text parameters called "Current Status" and "Status History", then save the form to make Workfront aware that both parameters now exist
• paste in the calculation for each as shown below (noting that if you have more than just the four built in Task Statuses defined, you can continue the first formula's pattern to also translate such additional ones), then save the form again

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

)

[BenettaPe]

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.

[Doug_Den_Hoed__AtAppStore]

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

[Sheri_Whitten]

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?