Targeted Auditing Proof Of Concept | Community
Skip to main content
Doug_Den_Hoed__AtAppStore
Community Advisor
Doug_Den_Hoed__AtAppStoreCommunity Advisor
Community Advisor
February 25, 2019
Solved

Targeted Auditing Proof Of Concept

  • February 25, 2019
  • 30 replies
  • 9237 views

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

    Best answer by Doug_Den_Hoed__AtAppStore

    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

    30 replies

    Heather_Kulbacki
    Community Advisor
    Heather_KulbackiCommunity Advisor
    Community Advisor
    April 4, 2019

    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
      Community Advisor
      Doug_Den_Hoed__AtAppStoreCommunity AdvisorAuthorAccepted solution
      Community Advisor
      April 4, 2019

      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
      JamesM-MERGE
      Level 4
      August 24, 2023

      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
      Community Advisor
      Doug_Den_Hoed__AtAppStoreCommunity AdvisorAuthor
      Community Advisor
      August 24, 2023

       

      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
      Community Advisor
      Heather_KulbackiCommunity Advisor
      Community Advisor
      April 5, 2019
      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
      Community Advisor
      Doug_Den_Hoed__AtAppStoreCommunity AdvisorAuthor
      Community Advisor
      April 5, 2019

      Huzzah!

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

      Regards,

      Doug

      S
      StevenHi2
      Level 3
      June 6, 2019

      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
      Community Advisor
      Heather_KulbackiCommunity Advisor
      Community Advisor
      June 6, 2019

      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.

        S
        StevenHi2
        Level 3
        June 6, 2019
        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
        Community Advisor
        Heather_KulbackiCommunity Advisor
        Community Advisor
        June 7, 2019
        I also initially tried to do it all in one field and struggled with that. Glad it's working for you!
        S
        StevenHi2
        Level 3
        June 10, 2019

        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
        Community Advisor
        Doug_Den_Hoed__AtAppStoreCommunity AdvisorAuthor
        Community Advisor
        June 11, 2019

        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

          Show more replies
          Terms & ConditionsAccessibility statement

          Loading footer...