Forum Discussion

Gordon_Smith's avatar
Gordon_Smith
Copper Contributor
Aug 26, 2023

Using logic to drive task column values

Good afternoon,

 

I have a column which is set to a binary state, Active/Inactive as Text 2 (named Status, which is different than the Project Status field).  I also have Tasks with two indented subtasks (Project -> Subproject  -> Task).  Rather than me manually setting everything to Active/Inactive, can I use some logic to make updates?  i.e., If a Task is set to Active, the SubProject and Project should also be active.  Alternately, if all the tasks are inactive, the SubProject should also go inactive.

 

Can I also "lock" a task by Indention level (i.e. not directly set the Project Active/Inactive level, but rather only through its subtasks Active/Inactive settings)

 

I see there is a VBA component, which means my (rusty) Excel VBA should apply.  I suspect I could use some guidance on the syntax, however...

  • John-project's avatar
    John-project
    Aug 27, 2023

    Gordon_Smith 

    How about a simpler approach. You mentioned you want a quick-look filtered view. How about a simple custom filter. Here's a sample plan with [performance]task status (Text2) selected via the dropdown.

    Now here's a filter for active tasks with the option to show related summary levels

    Applying the filter gives this

    Does that do what you need?

     

    Buuuuuut, if that doesn't quite do everything you need, here is the macro code to implement all your criteria, at least if I understand it correctly.

    John

     

  • John-project's avatar
    John-project
    Silver Contributor
    Gordon_Smith,
    First off, what version of Project are you using?

    You show your hierarchy as, Project > Subproject > Task but I suspect it's not quite as you describe it. In Project, a subproject is a separate file inserted into a master. It can either be via dynamic linking or static (i.e. copied). A screen shot of your Gantt Chart view would help to clarify exactly what you have.

    You are also using the terms "active" and "inactive". In Project those task attributes have specific meaning and probably not what you are doing. Again, the screen shot would help.

    On the surface, yes it should be possible to do what you want. It may or may not require VBA. Let's see what you've got and then go from there.
    John
    • Gordon_Smith's avatar
      Gordon_Smith
      Copper Contributor

      John-project, Thanks for the kind reminder that I need to think in terms of Microsoft Project, and less the way we approach projects within Microsoft Project, particularly when asking for aid.  Allow me to try again 🙂

       

      I am using Project Professional, via Microsoft Project Online Desktop Client, running on a local computer.

       

      I have a variety of Tasks within a single MPP file, with Indented (sub)Tasks:

      Indent 1: the overall contract (861 shown below)

      Indent 2: a subaspect of the contract that is largely independent of other Indent 1 subaspects

      Indent 3: A particular request of the client

      Indent 4: tasks that support the request

       

       

      I have created a column (Text 2) as a pulldown having the options of Active/Inactive so that I can quickly see if this is something we need to work on, or is paused/delivered.  If I change the value of Text 2 for a particulate Task to "Active" I would like to ensure that Text 2 for all the preceding Indentions follow suit.   This would give me an easy way to create a filtered view of projects that require our attention this week.

       

      So, if I update Task (Indent 4) to Active (in Text 2)...

      • Indent 3/Text 2 should also be updated to Active (but not other Indent 3 tasks) because if the Indent 4 task is active, so should the parent Indent 3 Request
      • Indent 2/Text 2 should also be updated to Active (but not other Indent 2 tasks) because if the Indent 3 Request is active, so should the Indent 2 SubAspect "DKGP"
      • Indent 1/Text 2 should also be updated to Active (but not other Indent 1 tasks) because if the Indent 2 SubAspect "DKGP" is active, so should the Indent 1 Contract 861.

      The intent of this would be ensuring that we are paying attention to the Task, (while also ensuring we are providing the context of the Request, the SubAspect, and the Contract)

       

       

      If, however, I update Task (Indent 4) to Inactive in Text 2...

      • Any other Indent 4/Text 2 Tasks under the same parent Indent3 should not update
      • If and only if all Indent 4/Text 2 values are Inactive for a particular parent Indent 3, then the parent Indent 3/Text 2 should update to Inactive. (if all the child indents are inactive, it makes sense for the parent to be inactive)
      • If and only if all Indent 3/Text 2 values are Inactive for a particular parent Indent 2, then the parent Indent 2/Text 2 should update to Inactive. (if all the child indents are inactive, it makes sense for the parent to be inactive)
      • If and only if all Indent 2/Text 2 values are Inactive for a particular parent Indent 1, then the parent Indent 1/Text 2 should update to Inactive. (if all the child indents are inactive, it makes sense for the parent to be inactive)

       

      I thought about using % complete on the tasks, but felt there would be administrative confusions when reporting since a lot of times things change around on the fly, and I didn't want to deal with the relative percentages of the overall projects and explaining up above why/how those numbers kept changing.  Really was just interested in a binary statement

       

      I have tried SubProject/Master MPPs before, and they quickly become difficult for my team to utilize properly, so I am trying a single MPP file approach this time.

       

      Hopefully this didn't make it more confusing.  Thanks!

       

      • John-project's avatar
        John-project
        Silver Contributor

        Gordon_Smith 

        How about a simpler approach. You mentioned you want a quick-look filtered view. How about a simple custom filter. Here's a sample plan with [performance]task status (Text2) selected via the dropdown.

        Now here's a filter for active tasks with the option to show related summary levels

        Applying the filter gives this

        Does that do what you need?

         

        Buuuuuut, if that doesn't quite do everything you need, here is the macro code to implement all your criteria, at least if I understand it correctly.

        John

         

Resources