SUCCESSORS Testing

Paul NewcomePaul Newcome ✭✭✭✭✭
edited 10/21/21 in Best Practice
10/21/21 Edited 10/21/21

I have seen in the past quite a few people asking how we can leverage predecessors for alerts so that when (for example) the preceding task is complete, a notification goes out automatically to the owner of the next task letting them know it is ready to start. The best approach to this was unfortunately parsing out the predecessors across multiple columns (if you had multiple predecessors).


I have been playing with the new SUCCESSORS function, and think I have almost found a way (with certain limitations). The limitations with this would be if you specified "SS", "SF", "FS", or "FF". I think I have some ideas on how to accommodate that but want to do some more testing before putting it out here for everyone.


In the mean time... Please feel free to take a look at the below to get started . I also welcome feedback and ideas on how to adjust for the other variables such as lag/lead time, "SS"/"SF"/etc., and any other "catch" people can think of.

First we insert an Auto-number column with no special formatting (called "Auto").

Next we insert a text/number column (called "Row") and use the following column formula:

=MATCH([email protected], Auto:Auto, 0)


Then we insert a multi-select dropdown column (called "Next Tasks") and used this column formula:

=JOIN(SUCCESSORS([Task Name]@row), CHAR(10))


From here we you can start to deviate and adjust as needed, but the below formula (put into another text/number column) will grab the % Complete for the previous tasks, add them up, then compare it to how many previous tasks there are. If those two numbers match, then the task on this row is ready to start.


=IF(AND([email protected] <> "", [% Complete]@row = 0, SUMIFS([% Complete]:[% Complete], [Next Tasks]:[Next Tasks], HAS(@cell, [email protected] + "")) = COUNTIFS([Next Tasks]:[Next Tasks], HAS(@cell, [email protected] + ""))), 1)


.


In the below screenshot, you will see that Row 5 is ready to start because its predecessor (row 4) is at 100% complete, and row 5 has not yet been started. Row 9 is not yet ready because it needs both rows 7 & 8 to be completed first and 8 is only at 50%. You will also see that Row 8 is not marked as ready to start because even though its predecessor is completed, the task on Row 8 has already ben started (% Complete greater than zero).


thinkspi.com

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 10/26/21

    To output the task name(s) for whatever is coming next (formula is in [Next Task Name] column):


    =IF([Next Tasks]@row <> "", JOIN(COLLECT([Task Name]:[Task Name], Row:Row, HAS([Next Tasks]@row, @cell)), CHAR(10)))



    thinkspi.com

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Vivien Chong Has a much cleaner solution if all you need is to pull the next task names.

    =JOIN(SUCCESSORS([Task Name]@row), ", ")


    I imagine this could also be adapted for pulling upcoming dates and whatnot as well...

    thinkspi.com

  • Vivien ChongVivien Chong ✭✭✭✭✭

    @Paul Newcome Thanks for sharing this. I have not tested on scenario where there's more than 1 predecessor and have not tested with all the SS, SF, FF, lag... Will be following your thread for any updates. Thanks for sharing your insight.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Vivien Chong If you do get around to testing it before I do, please feel free to share!

    thinkspi.com

  • Vivien ChongVivien Chong ✭✭✭✭✭

    @Paul Newcome sure! but looking at the expertise level, I think you will find out the solution quicker than me. Good to have you around.

  • @Paul Newcome and @Vivien Chong - You just made my day! Thank you for your insightful help!🤗

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    To pull the task name(s) of the actual predecessors, we would use the above instructions on generating the row number on the sheet. Next we would insert a multi-select dropdown column and enter

    =SUBSTITUTE([email protected], ", ", CHAR(10))


    Doing this allows us to "parse" those predecessors out into individual numbers as opposed to a text string. Finally we would use formula such as this to pull a listing of all of the predecessors:

    =JOIN(COLLECT([Task Name]:[Task Name], Row:Row, HAS([email protected], @cell)), CHAR(10))

    thinkspi.com

Sign In or Register to comment.