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]

Glad you like it Sherri,

When there is a lot of bracketing in a formula such as this one, I often move it into a text editor so I can confirm everything is lined up correctly. In this case (noting that you also need to first add your Target and Target History parameters, save the form so it is "aware" they are available, and then paste in the self-referencing formula), I did so using my original Status / Status History version above, then substituted in your Target / Target History instead, and got the version below, which I invite you to try.

Regards,

Doug

LEFT(

IF(LEFT({DE:Target History}

,LEN(IF(ISBLANK({DE:Target})

,"-"

,{DE:Target}

)

)

)

=Target

,Target History

,CONCAT(

Last Updated By.Name

," - "

,(

IF(ISBLANK({DE:Target})

,"-"

,{DE:Target}

)

)

," ("

,$$NOW

,") | "

,{DE:Target History}

)

)

,2000)

[Sheri_Whitten]

Oh my goodness, THANK YOU, Doug!! I never would have thought to add a calculated field without the calculation, save the form, then go back into it to add the calculation. Once the error kicked out, I kept backing out of creating the field altogether. Using this trick will be way more intuitive than the status timestamp I currently use, which is never recalculated properly. Let the happy dance commence 😄

[Doug_Den_Hoed__AtAppStore]

My pleasure Sherri, And yes, it’s definitely one of those Makes Sense Now That I See It techniques. Regards, Doug

[GregWi]

@Doug Den Hoed‚ & @Heather Kulbacki‚ First up - thanks! Finally got around to implementation just before the Holidays.

In the spirit of community I thought I would share an enhancement to the formula which will also include the time of the action. Maybe it's my understanding of WF but I could not seem to be able to format $$NOW to display as date & time - but hacked around it by extracting and concatenating Hour and Minute from $$NOW (note extra code to handle minutes 0-9 to insert the leading 0.

Below is my 'enhanced' version which will include the time (hh:mm) - with the addition highlighted in bold.

LEFT(IF(LEFT(Status History,LEN(IF(ISBLANK(Current Status),"-",Current Status)))=Current Status, Status History,CONCAT(IF(ISBLANK(Current Status),"-",Current Status)," (",$$NOW," ", IF(LEN(MINUTE($$NOW))>1, CONCAT(HOUR($$NOW),":",MINUTE($$NOW)), CONCAT(HOUR($$NOW),":0",MINUTE($$NOW)))," by ", Last Updated By.Name , ") | ",Status History)),2000)

Greg

[Doug_Den_Hoed__AtAppStore]

Glad you like it, Greg, and nice improvement: thanks for sharing!

Regards,

Doug

[NuriaMunguia]

First thank you so much for sharing this code!! I have been testing this out and works really well.. Have a question though, instead of capturing the person who updated the record last - how can I capture whoever is making the change. I want to see who actually updated the record, whoever is logged in and clicking on the status or target field captured in the history field. Whoever updated the record last may not necessarily be the same person updating the status/target field.

Thanks in advance for your help. I tried $$USER.ID instead of Last Updated By.Name, but didnt work.

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)

[GregWi]

@Nuria Munguia‚ What we are tracking here is the name of the person updating the record, but limiting capture to a change in status.

It sounds you are looking to track other things beyond just the Status? Which could result in this getting very complicated very quickly!

I'd suggest creating a report based on Notes - have a play with the fields available, and you can filter this down to see the only the types of changes / updates, when they were made, by whom etc. Then create a Dashboard, Add the Report to a Dashboard, then add this new Dashboard to the relevant users group as a Custom Section on the Project - and bingo - a really clear history of who made what changes to the project.

In fact, I liked the idea above so much I've just implemented it across all of our projects 😃.

[NuriaMunguia]

Hi !

Thank you for responding to this!. That is a great suggestion, i actually do that to audit information on different workstreams. When i run the code you provided ( very helpful by the way), it tracks the status change, and returns the person who last updated the record, but not the person who updated the status field. The name returned by Last Updated by could have been a resource adding an update to the updates tab, but not necessarily the same person who updated the status field. Thats why I was trying to replace Last Updated By.Name with $$USER.ID. Please see the screenshots,

Thanks in advance for your help

LEFT(IF(LEFT(Status History,LEN(IF(ISBLANK(Current Status),"-",Current Status)))=Current Status, Status History,CONCAT(IF(ISBLANK(Current Status),"-",Current Status)," (",$$NOW," ", IF(LEN(MINUTE($$NOW))>1, CONCAT(HOUR($$NOW),":",MINUTE($$NOW)), CONCAT(HOUR($$NOW),":0",MINUTE($$NOW)))," by ", Last Updated By.Name , ") | ",Status History)),2000)

[GregWi]

@Nuria Munguia‚ As the field calculates when the status changes, the the last update should be the user who made that change. The only thing I can think of is that in the time between the status changing and the formula calculating another update was made to the item? Do you have any processes that run via Fusion perhaps that might be making updates to your task on status change by any chance? I've checked my results against the data in the updates 'feed' and they match.

If you check the updates for the test task screenshot above - do you see D...... R.... changed the status to XXXX. Apr 20, 2021 5:03 pm or are you seeing a different username?

[NuriaMunguia]

Hi Greg! thanks so much for looking into this, and yes I am seeing a different name. For example, if I log in and look at a record i see D.R shows in the last updated by. When i go in and change the status value, it shows me that DR changed the status not me. No fusion integration, I am on the sandbox and is just me testing this out. See attached screenshots showing the process i am following to make the update and the results i get. thanks again for your help.

[GregWi]

@Nuria Munguia‚ All out of ideas - all I can tell you is that for me - if in updates I see that Greg Wilson changed a status @ at date and time this is correctly reflected in the formula..... as below.

Task Updates - small snippet:

Status History - full

I'd don't suppose in the examples you've been working on you've been logged into WF as a different users while checking something else first? Not the first time I have had an issue like that! 😔

Request: Under Review (19/04/21 11:39 by Greg Wilson) | Activate: Completed/Launched (19/04/21 11:37 by Greg Wilson) | Build: In Progress (16/04/21 11:00 by Greg Wilson) | Preparation: Assets Due (13/04/21 13:46 by Greg Wilson) | Request: New (13/04/21 13:46 by Greg Wilson) |

[NuriaMunguia]

😢 same place as you, not sure why is not working. What you and Doug have suggested should work but system doing something different... No i was not logged in as someone else, that's a great suggestion though. Greatly appreciate all the time and effort you vested on helping me with this.

Thank you!

[Sheri_Whitten]

Finally got Greg's addition of time to work in my field. Any way to limit the amount of characters that show in a report column? I've tried using usewitdths and modifying @NRYN R - inactive‚ 's “Shortened Description” code, but I can't get it to work. I'd like to limit the number of characters to 35 characters so we don't see a huge amount of data here, we're only interested in the most recent update showing. Any ideas?

[GregWi]

@Sheri Whitten‚ - had to do the very same thing myself last week :-). Rather than display the whole custom field (e.g. myStatusField) in the view or report, fire up Advanced Text Mode and change your valuefield line to valueexpression = LEFT( {myStatusField} , 35) - however I prefer to be a bit fancier 😃 so I can see in the report if I have shortened the value - by adding ... to the end of the 35 characters if I have 'reduced' it.

IF ( LEN({myStatusField}) , 35, CONCAT LEFT ( {myStatusField}, 35) , '...', {myStatusField} )

Note - not double-checked all my commas and brackets in the above but you get the idea!

[Sheri_Whitten]

Perfect! It worked just as I hoped! Thank you so much 😄

[Doug_Den_Hoed__AtAppStore]

Hi Nuria, Because it is a formula, I suspect you can get it working by adding the curly brace syntax (eg {lastUpdatedBy}.{name}, etc), as above. Regards, Doug

[NuriaMunguia]

Thank you Doug! for some reason replacing "Last Updated By.Name" by {$$USER}.{ID} or any other combination didn't work. :(

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)

[Doug_Den_Hoed__AtAppStore]

Hi Nuria,

I expanded all the threads in this post so I could refresh my memory, and if I'm now following, think that either my "Glad you like it Sherri (long version)" or Greg's "First up - thanks! (hh:nn version)" might actually work for you As Is.

The nature of Targeted Auditing is that it is checking for situations when the target (Status, in both examples) has changed from what it was to something different in the edit that is in progress. When such a change occurs, by definition, it is the logged in person (i.e. $$USER) who made the change, and is therefor also the "Last Updated By" at that moment in time.

Given that, the Status History will show a Targeted Audit of each Status Change, When it was made, and By Whom...which I believe is what you seek.

Regards,

Doug

[NuriaMunguia]

Thanks so much Doug, somehow that is not what I am seeing 😔 . I understand what you are saying and technically, thats how it should work. see attached.

[Doug_Den_Hoed__AtAppStore]

Hi Nuria,

Just to double check...

• what is the object type for each of the rows in your screenshot?
• is Current Status a custom dropdown parameter on a custom form attached to each such row?
• is Status History a calculated custom parameter on that same custom for attached to each such row?
• what formula are you using within the Status History calculated custom parameter?

Regards,

Doug

[NuriaMunguia]

Thank you Doug!!! here are my responses:

• what is the object type for each of the rows in your screenshot? Issue records
• is Current Status a custom dropdown parameter on a custom form attached to each such row? No is a free text field in a custom form.
• is Status History a calculated custom parameter on that same custom for attached to each such row? Yes
• what formula are you using within the Status History calculated custom parameter?

LEFT(IF(LEFT(Status History,LEN(IF(ISBLANK(Current Status),"-",Current Status)))=Current Status, Status History,CONCAT(IF(ISBLANK(Current Status),"-",Current Status)," (",$$NOW," ", IF(LEN(MINUTE($$NOW))>1, CONCAT(HOUR($$NOW),":",MINUTE($$NOW)), CONCAT(HOUR($$NOW),":0",MINUTE($$NOW)))," by ", Last Updated By.Name , ") | ",Status History)),2000)