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

[Doug_Den_Hoed__AtAppStore]

Hi Nuria,

🎭 You might want to sit down for this one.

In my original design (having refreshed my memory), Current Status is a calculated parameter, with the following formula (noting that for Issues, there would likely be more entries):

IF(Status="NEW","New"

,IF(Status="INP","In Progress"

,IF(Status="HLD","On Hold"

,IF(Status="CPL","Complete"

,Status

)

)

)

)

When an edit is made within Workfront, there a number of steps that occur in a certain order, among which, resolving calculations is (in my mind) "after" the edits have been made, but "before" the record is fully saved. In my case (and, I suspect, @Greg Wilson‚ and the others on this thread), that certain order might (I now appreciate) be part of the reason the behavior we are seeing is as we desire.

In your case, however, since Current Status is instead a custom text parameter, I suspect it is being evaluated "as" the edits are being made, which in turn is causing the unexpected behavior you are observing.

So! Presuming that you have good reason to leave Current Status as a text parameter, I suggest you try this (or something similar):

• create a calculated parameter beside Current Status called "Current Status Calc"
• set its formula to "=Current Status", effectively pointing at the manually entered status
• edit your Status History custom calculated parameter's formula and replace "Current Status" with "Current Status Calc", thereby tapping into that special "after" edits but "before" sweet spot
• save everything and test

I look forward to hearing how you make out, and hope it does the trick.

Regards,

Doug

[GregWi]

@Doug Den Hoed‚ A seat 💺 AND have had at least one ‚òï - but made total sense one the caffine kicked in! Let's hope that's the reason! If this stuff was easy we would not need a community!

[NuriaMunguia]

@Doug Den Hoed‚ Soo.. I definitely sat down for this one and had way too much coffee !! and oh my goodness.. you are legendary Doug!!!👏

It worked! you are right, it had to do with the order in which the system resolved the calculations on the free text field.

THANK YOU!!!!!😊

[NuriaMunguia]

@Doug Den Hoed‚ Also.. this gives you flexibility for code reuse - without changing your code.. code runs on value on Current Status Calc, so you can assign different values to Current Status Calc without having to change the code every tiime :-D win win!!! Awesome.. just awesome! thank you again!

[Doug_Den_Hoed__AtAppStore]

Fantastic Nuria.

Thanks for confirming, and yes, I see how "decoupling" the formula in that way has fringe benefits, too.

Excellent challenge, valuable insight, and bonus side effects: the trifecta.

Regards,

Doug

[Jason_JB]

@Doug Den Hoed‚ - thank you for sharing the above!

I was able to leverage it to go a step further to address some needs we have. Sharing a recap below in case it helps others.

The examples focus on an internal metric we call "Pi health score". I needed to be able to capture the field, identify change since last entry, date of change, and wanted the historical record as noted above.

I've created 5 custom fields:

-Pi health score

-Pi health score - prior

-Pi health score - last change date

-Pi health score - change

-Pi health score - history

Formulas for each are:

Pi health score - prior

RIGHT({Pi Health Score - History},SUM(LEN({Pi Health Score - History}),-SUM(SEARCH("|",{Pi Health Score - History},1),2)))

Pi Health Score - Last Change Date

IF(ISBLANK(Pi Health Score),"",IF(Pi Health Score={Pi Health Score - Prior},{Pi Health Score - Last Change Date},$$NOW))

Pi Health Score - Change

SUM(Pi Health Score-{Pi Health Score - Prior})

Pi Health Score - History

IF(ISBLANK(Pi Health Score),IF(ISBLANK({Pi Health Score - Prior}),"",

LEFT(IF(LEFT({Pi Health Score - History}, IF(ISBLANK(Pi Health Score),0, SUM(SEARCH("(",{Pi Health Score - History}), -1))) =ROUND(Pi Health Score,2),{Pi Health Score - History},CONCAT(IF(ISBLANK(Pi Health Score),"-",ROUND(Pi Health Score,2))," (",$$NOW,") | ",{Pi Health Score - History})),1000)),LEFT( IF(LEFT({Pi Health Score - History}, IF(ISBLANK(Pi Health Score),0,SUM(SEARCH("(",{Pi Health Score - History}), -1))) =ROUND(Pi Health Score,2),{Pi Health Score - History},CONCAT(IF(ISBLANK(Pi Health Score),"-",ROUND(Pi Health Score,2))," (",$$NOW,") | ",{Pi Health Score - History})),1000))

I had to add some extra logic to the history field because I was getting blank values captured by the formula originally.

I've included an example from the test record I used during development.

[Doug_Den_Hoed__AtAppStore]

My pleasure Jason,

I'm impressed with how you were able to leverage it across multiple fields; particularly the SUM approach you used to pluck the Pi health score - prior out of the History -- very creative! And as sidebar, a shout-out to the Custom Data architects at Workfront for making the logic smart enough to resolve sequential calculations like these that have dependencies within separate calculations on the same custom form (e.g. Prior, then Last Change, then Change, then History).

In a related story, though, some caution about this technique...

These types of calculations -- as you mentioned -- often take some extra logic (and testing), and once they're working, life is good. However: if at some date you decide to change the logic, there is a high probability that the existing data will be cleared and lost. Permanently.

So, some mitigation strategies:

- don't change the formulas (from the "Doc, my arm hurts when I move it like this" department)

- if you must change them, consider introducing a new custom field along side the old one

- for critical situations, consider adding a text-based field along side the data and periodically transferring the data from the calculated field to the text-based field as backup, either manually, using Fusion, or using our Excel Updater solution (e.g. pick a day and back up ALL such fields on mass)

- as an extra precaution (suspecting you'll appreciate the cross-over to our parallel offline conversation), you might also consider periodically backing up all of your Workfront data using our Workfront Snapshot solution

Regards,

Doug