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‚ 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!!!!!
Views
Replies
Total Likes
@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
win win!!! Awesome.. just awesome! thank you again!
Views
Replies
Total Likes
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
Views
Replies
Total Likes
@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.
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
Views
Replies
Total Likes