How To Detect Copied Projects (aka "Project Genealogy")

[Doug_Den_Hoed__AtAppStore]

Hi folks,

NEW (and improved) as of March 5, 2025 3pm Mountain

With thanks to @Sven-iX for this thought inspiring post, I am pleased to share this solution to the years-old "detect copy" challenge using a derivative work of my Targeted Auditing technique:

• create a custom form (e.g. Project Genealogy, as below) that you will attach it to all Projects of interest; or if you prefer, skip this step and instead repeat the remainders on your existing common Project Custom Form(s) of interest
• add two calculated parameters ("Project Genealogy" and "Project Original Or Copy", respectively; both Text format) to the custom form
• save the form (so Workfront "knows" that those parameters now exist)
• set the NEW Project Genealogy formula as follows (and as below):

IF(ISBLANK({DE:Project Genealogy})
    ,CONCAT("Created on ",{entryDate}," (",{referenceNumber},") as '",{name},"' by ",{enteredBy}.{name})
    ,IF(CONTAINS(CONCAT(" (",{referenceNumber},") "),{DE:Project Genealogy})
        ,{DE:Project Genealogy}
        ,CONCAT("Copied on ",{entryDate}," (",{referenceNumber},") as '",{name},"' by ",{enteredBy}.{name}," | ",{DE:Project Genealogy})
    )
)

• set the Project Original Or Copy formula as follows (and as below):

IF(LEFT({DE:Project Genealogy},6)="Copied","Copy","Original")

• save the form
• create a view and do some tests similar to mine (as below)

With the "cheater" caveat aside for today, I invite those of you wanting to detect (and prevent) Copied Projects to try this in your environments, and am interested in hearing how you make out.

Regards,

Doug

NEW

[KierstenKollins]

Genius @Doug_Den_Hoed__AtAppStore and so simple to set up!

[AdamCnote]

This is very cool, points to Doug!!  Thanks!

[Sven-iX]

Very cool @Doug_Den_Hoed__AtAppStore  Really dig the project genealogy! That's really slick. I offer the below so we don't have to rely on the project:name starting with "Copy of "

Here I use a 30 sec gap after creating the project. (I didn't replicate the genealogy)

Same caveat applies as above: If the data is cleared, we don't have anything to compare to. If anyone has ideas outside of Fusion, I'd love to hear them.

How it works:

2 custom fields 

DE:projID

IF( $$OBJCODE="PROJ",IF(SUB($$NOW,{entryDate})/1000>=30,{ID},{DE:projID}))

and DE:project origin

IF(SUB($$NOW,{entryDate})/1000<30,IF(ISBLANK({DE:projID}), "Created","Copied"),IF(ISBLANK({DE:project origin}), "Unknown",{DE:project origin})
)

DE:projID waits for 30 seconds before replacing its value

During that window, DE:project origin does the checking and sets its value. The window is 30sec because the project copy will depend on # docs, tasks etc and could run long.

The logic is

• if projID is blank we assume the project was not created as a copy (we're not able to account for the user intentionally clearing custom data)
• if projID is NOT blank, that means it was copied over from the source project
• if we're outside the 30s window and the DE:project origin is still blank we set it to Unknown

[Doug_Den_Hoed__AtAppStore]

Oooh! Very nice, @Sven-iX! 

Your "30 second window" technique is indeed an Excellent Refinement: duly noted for future consideration!

Regards,

Doug

[Doug_Den_Hoed__AtAppStore]

Hi again folks,

After one colleague spotted a bug in my original version, and another colleague suggested some excellent improvements, I invite you to switch to the NEW (and improved) as of March 5, 2025 3pm Mountain version that I just updated on the main post, above, which fixes the bug and includes the Ref Num parenthetically and the name of the project in the Project Genealogy.

To switch to this NEW version (asap, I'd suggest, and noting that this is a "going forward" situation):

• edit the Project Genealogy custom form (or its equivalent, in your environment)
• CLEAR the Project Genealogy formula, check off the Apply to Existing Calculations option, and click the Apply button (effectively resetting all of the data)
• paste the new formula above, again (superstitiously) check off the Apply to Existing Calculations option, then Save and Close the form
• test to confirm all is well (e.g. similar to the ones I showed in my updated screenshot)

I am certain there are other Excellent Refinements that could be made to this technique -- including expanding the concept to any other object with a RefNum and custom data, as one of my colleagues pointed out -- so go nuts, have fun, and thanks for tuning in!

Regards,

Doug

CC: @KierstenKollins, @AdamCnote, @AmyReilly, @Sven-iX, and anyone who used my original version