none
MSP 2010 Custom Field formula RRS feed

  • Question

  • Hello folks,

    I'm trying to get a custom field to show task availablity of yes, when all predecessors are complete.  I want this so I can highlight when tasks are truely available for work as modeled in the schedule. 

    I think it is an if/then statement, but it might need to be VBA coded as I think it is more complicated than what a formula can handle.

    In layman terms it would be: If no predeccessor, or if all task predecessors are complete, display yes, otherwise display no.

    Something like this, but I think it is impossible to do it in one line.

    IIF(Predecessors) <>1, IIF (Duration)>0,"Yes","No")

    I think there is a way, right? Or am I out of luck? 

    TIA

    CW

    Friday, October 19, 2012 5:57 PM

Answers

  • Hi,

    Yes, it has to be VBA coded.

    Following does it (if you cannot implement a VBA procedure, look up www.masamiki.com)

    Sub FreePred

    dim Job as Task

    dim Pred as task

    for each job in activeproject.tasks

    if not job is nothing then

    job.flag1=true

    for each pred in job.predecessortaks

    job.flag1=job.flag1 and (pred.percentcomplete=100)

    next pred

    end if 'Nothing

    next job

    end sub

    -----------------------------------

    Greetings,

    Friday, October 19, 2012 6:13 PM
    Moderator

All replies

  • Hi,

    Yes, it has to be VBA coded.

    Following does it (if you cannot implement a VBA procedure, look up www.masamiki.com)

    Sub FreePred

    dim Job as Task

    dim Pred as task

    for each job in activeproject.tasks

    if not job is nothing then

    job.flag1=true

    for each pred in job.predecessortaks

    job.flag1=job.flag1 and (pred.percentcomplete=100)

    next pred

    end if 'Nothing

    next job

    end sub

    -----------------------------------

    Greetings,

    Friday, October 19, 2012 6:13 PM
    Moderator
  • I am guessing that your situation is something like this.

    When you come to "update" a task which was scheduled to start in the past relative to the status date, you want to know if it has started, and, if so, when it started.

    And then, you want to know how much actual duration it has had since it started, and on which days the actual duration occurred, and then on which days of the actual duration the actual work and actual cost occurred.

    Suppose a task has several FS0 predecessors (for the moment, easier). Suppose you are told that the task has actually started.

    You can't say that a task has actually started (by giving it an actual start date) unless you also check that its predecessors are all marked off as 100% complete, and that they all have actual finish earlier than the reported actual start of the task.

    If any of the predecessors are not actually finished, then you need to check their status and confirm whether the predecessor link is correct or not. This part is tedious and no easy way around it. At least, this is what usually occurs for me.

    This is what I do (no VBA).

    Split the screen and show predecessors and successors in the bottom pane. Then to check each predecessor just double click on it to get the task information for that predecessor.

    Friday, October 19, 2012 11:40 PM
  • You can not get there with just a custom field as custom fields are limited to accessing data about the task they are associated with, or the project summary task. So you must use VBA.

    Here is a macro I wrote to do a similar thing about a decade ago. You can change the 80% to 100% to fit your needs. It also filters out the tasks that are done so they won't clutter your view.

    Sub ReadyToStart() 'This macro will filter to show only tasks that have 'all of their predecessors at least 80% complete 'and which are not already marked complete themselves 'copyright Jack Dahlgren, March 2002 Dim t, pt As Task For Each t In ActiveProject.Tasks If Not t Is Nothing Then If Not t.Summary Then t.Flag1 = False If t.PercentComplete = 100 Then t.Flag1 = True For Each pt In t.PredecessorTasks If pt.PercentComplete < 80 Then t.Flag1 = True End If Next pt End If End If Next t FilterEdit Name:="ImReady", TaskFilter:=True, Create:=True, OverwriteExisting:=True, FieldName:="Flag1", test:="equals", Value:="No", ShowInMenu:=False, ShowSummaryTasks:=False FilterApply Name:="ImReady" End Sub


    Jack Dahlgren blogs at:
    Project and Retrovention
    and rarely Twitter

    Monday, October 22, 2012 10:50 PM
    Moderator
  • This worked out for me once I changed one line. 

    In this answer, the line "for each pred in job.predecessortaks" was changed into "for each pred in job.predecessortasks" and it worked.

    No in my custom column, I can see if a task is available to work based on the relationship model.  I can revise the schedule in case of mistakes, or I can "yell" at them for doing things out of order and not following the schedule, that never happens right?

    Thanks for the help. This place is a great resource.

    May all your projects meet requirements, cost, and time.

    Cheers

    Chris

    Friday, October 26, 2012 12:30 AM
  • You're welcome Chris, and thanks for the encouragement

    Friday, October 26, 2012 8:00 AM
    Moderator