none
Elegant ways of identifying parent plans in filtered lists of tasks RRS feed

  • Question

  • Hi folks

    We have lots of projects, which each have a unique identifier (a three-digit numeric).

    When we report, we create a static master, embed all the sub-projects, and <do reporting stuff>. That often involves filtering, and some of the filters don't show the task rollups (because they get in the way). Therefore it's hard to see which task belongs to which project.

    A couple of us have got into the habit of prefixing all our tasks with the numeric (eg "123|Here's the task name"), which is fine once you get into the habit of doing it. And even easier if you have a template and then make liberal use of find-replace.

    We're doing some process standardisation, and before we roll the use of a task prefix out as a thing that everyone has to do, has anyone got a better way of doing it? We could create a custom field and stick the ID in that, but that seems more work and much less obvious when it's been forgotten. I wondered if you could show the subproject filename as a field or something like that...

    Cheers!

    Rob

    PS: Project Standard 2013 across everyone.

     

    Thursday, August 23, 2018 1:51 PM

Answers

  • Rob,

    You will need to set up each subproject with the BeforeClose Event macro as shown below. For each project that will be part of your static master, open the VB Editor and double click the ThisProject Object in the Project Explorer window on the left. Copy and insert the Private Sub code into the code window on the right. This simple code assumes the Text1 field is empty. If not, clear any contents. I could have done that with the code but then updates to the file would take longer as it would need to re-populate all tasks each time the file was closed. This way it populates all tasks with the code the first time the file is closed, thereafter, it only populates the Text1 field of tasks that were added.

    Once the code is in place in each file, it will automatically set the Text1 field.

    And yes, it is possible to configure the contents of Text1 any way you need but remember, if you decide you want a different identifier you will need to go to each project file and make the change. With an Event macro you can't simply make the change in one file and then use the Organizer to transfer that to each of the other files.

    Okay, well that's one way to do it. Here is another approach that might be a little easier. If you create a temporary dynamic master, I can write a macro that when run from the master, will populate the Text1 field of all inserted subprojects. So if you have 100 to 150 subprojects, this approach will save a whole lot of time. You will not need to save or maintain a dynamic master. Interested?

    And here's another thought. You don't mention how you create your static master but you could create a dynamic master, run the macro to update subproject ID values, then de-link the master to create the static master and finally save all files. For example, if you just want to know which task came from which subproject in a static master, you can run this code on a dynamic master, then de-link to create the static master and save all files. The code will copy the subproject name into the subproject's Text1 field so it is still available once the dynamic master is converted.

    Sub SubprojIdent()
    Dim t As Task
    Dim sp As Subproject
    For Each sp In ActiveProject.Subprojects
        For Each t In sp.SourceProject.Tasks
            If Not t Is Nothing Then
                t.Text1 = t.Project
            End If
        Next t
    Next sp
    End Sub

    Hope this helps.

    John

    Saturday, August 25, 2018 12:03 AM

All replies

  • Rob,

    I appreciate your use of the term "static master", it tells us exactly what file structure you have.

    I don't quite understand your statement about task rollups "getting in the way". Please elaborate.

    I'm sure you are aware that there is a Project field showing the parent project name in a dynamic master but unfortunately that field doesn't help with a static master. However, have you considered the WBS field? That seems like a perfect place for your 3 digit unique identifier. However, if you already use that field for a true work breakdown identifier you could use one of the 30 extra text fields or one of the 20 number fields, since your identifier is numeric. Personally I'd elect to use a text field so you can add an alpha to the identifier (e.g. P123, X124, etc.).

    If you are concerned about users "forgetting" to add identifiers when they add a new task to their respective subproject, a simple Open Event macro can take care of that automatically. The macro could set up the initial sequence in each file and and then update it if and when changes are made.

    Some thoughts.

    Johh

    Thursday, August 23, 2018 2:54 PM
  • Hi John, thanks for the thoughts.

    An example of the rollups getting in the way (which admittedly is partly based on future practice where everyone is using the same template). We have a custom text field for milestone IDs, along with a standard set of milestones for all projects (to which PMs can then add others). In the static master, if we filter on milestone 008, we get a list of the "all deliverables complete" milestone for every project. We have about 100-150 projects running concurrently (some are very small), so the difference between seeing a single line for each, and seeing three (Project Summary task, the Summary task for the Deploy stage, and then the milestone) is significant. Therefore the report view hides the Summaries.

    Whilst WBS isn't widely used in our team, some do, so I don't want to steal that.

    However, if we could use a macro to quietly populate a text field in the background, that would work. I'll have a rummage around the internet to refresh my memory.

    Friday, August 24, 2018 7:31 AM
  • Rob,

    You're welcome and thanks for the feedback.

    I can write the macro for if you want. What text field would you like to use and what is the structure of the unique identifier? An example would be very helpful.

    John

    Friday, August 24, 2018 1:15 PM
  • Even better; thanks a lot.

    Ideally, something like:

    • On close (because then it'll sort out stuff if they're adding lines to the plan after opening it)
    • If there are lines in the schedule which have a non-empty task name but a blank in Text1:
    • Prompt user for ID using MsgBox
    • Write that ID to all the blank Text1 fields
    • End If
    • Continue to exit project.

    I can then go from there to adjust the Text field referenced. My VBA was OK, but now a) it's rusty and b) some of what worked in Access and Excel apparently doesn't work in Project (Application.FileDialog was yesterday's exciting bit of Googling).

    Cheers!

    Friday, August 24, 2018 1:43 PM
  • Rob,

    I'd be happy to write the macro for you.

    Do you really want to pop up a user message box? If the ID is a fixed sequence (e.g. 123, 124, 125, etc.) or a fixed ID for a given project file (e.g. P123 for all tasks in subproject 1, P124 for all tasks in subproject 2, etc.) then the macro can automatically generate the next number or appropriate ID needed for added tasks. With a user input (i.e. MsgBox) there is always the possibility of human entry error, but obviously it's your choice.

    I'm "booked up" this morning, so unless you are available later today or over the weekend, you won't get the macro until Monday. If you want to contact me directly, use the address below.

    John

    jmacprojataticlouddotdotcom

    (remove obvious redundancies)
    Friday, August 24, 2018 2:35 PM
  • Hi John

    Entirely at your convenience, so no rush!

    If you can take it from the filename that would be excellent. Format is "CRF0000 project schedule [possible padding].mpp", with the 0000 being the identifier.

    For future-proofing, is it possible to use everything after "CRF", but before the first space? That would let us use 1234A, 1234B etc for subprojects.

    Thanks, and have a good weekend.

    Rob

    Friday, August 24, 2018 3:05 PM
  • Rob,

    You will need to set up each subproject with the BeforeClose Event macro as shown below. For each project that will be part of your static master, open the VB Editor and double click the ThisProject Object in the Project Explorer window on the left. Copy and insert the Private Sub code into the code window on the right. This simple code assumes the Text1 field is empty. If not, clear any contents. I could have done that with the code but then updates to the file would take longer as it would need to re-populate all tasks each time the file was closed. This way it populates all tasks with the code the first time the file is closed, thereafter, it only populates the Text1 field of tasks that were added.

    Once the code is in place in each file, it will automatically set the Text1 field.

    And yes, it is possible to configure the contents of Text1 any way you need but remember, if you decide you want a different identifier you will need to go to each project file and make the change. With an Event macro you can't simply make the change in one file and then use the Organizer to transfer that to each of the other files.

    Okay, well that's one way to do it. Here is another approach that might be a little easier. If you create a temporary dynamic master, I can write a macro that when run from the master, will populate the Text1 field of all inserted subprojects. So if you have 100 to 150 subprojects, this approach will save a whole lot of time. You will not need to save or maintain a dynamic master. Interested?

    And here's another thought. You don't mention how you create your static master but you could create a dynamic master, run the macro to update subproject ID values, then de-link the master to create the static master and finally save all files. For example, if you just want to know which task came from which subproject in a static master, you can run this code on a dynamic master, then de-link to create the static master and save all files. The code will copy the subproject name into the subproject's Text1 field so it is still available once the dynamic master is converted.

    Sub SubprojIdent()
    Dim t As Task
    Dim sp As Subproject
    For Each sp In ActiveProject.Subprojects
        For Each t In sp.SourceProject.Tasks
            If Not t Is Nothing Then
                t.Text1 = t.Project
            End If
        Next t
    Next sp
    End Sub

    Hope this helps.

    John

    Saturday, August 25, 2018 12:03 AM
  • Works like a charm :). And avoids having to have macros in the child projects, which are vulnerable to people blocking them...

    Final question; how do I convert the dynamic master into a static one? I'm assuming something like looping through the subprojects and changing the setting?

    Code I'm working with is at https://pastebin.com/GWiL8RUf. That embeds all the projects within a (user-specified*) folder into the static master, but I've commented out the bin in line 46 that was importing them in that way. Your code then runs lines 51-59, and ideally I now want a final bit at the end to break the links to the source files.

    *The reason we're having to do it this way is that the files live on SharePoint, so our PMO are going to have to download copies weekly and then run this report on the copies. 

    Monday, August 27, 2018 10:40 AM
  • Rob,

    I'm glad the first part worked out for you. This macro will un-link all subprojects in a dynamic master, then save the master to a new name and close. You can modify the save and close to meet your needs.

    John

    Sub UnlinkSubs()
    Dim sp As Subproject
    For Each sp In ActiveProject.Subprojects
        sp.LinkToSource = False
    Next sp
    FileSaveAs Name:="AAAReport"
    FileCloseEx
    End Sub

    Monday, August 27, 2018 3:42 PM
  • Dropped in the sp-linktosource = False as part of the existing loop and that's now working as I intended it to.

    I'm still getting some scheduling warning messages popping up (where people have got projects working as unofficial resource pools mainly) so I need to fine-tune the suppression on those, but it's all Google-able stuff from here.

    Thanks for your help on all this John. Made my life much easier!

    Cheers

    Monday, August 27, 2018 3:53 PM
  • Rob,

    You're very welcome and thanks for the feedback.

    John

    Monday, August 27, 2018 4:31 PM