Dependency Logic

JeremiahHorstick

I wonder:

Is 'Row A' predecessor to any other?

Which rows?

Is there a formula that will accomplish this?

-J

 

Paul Newcome

Are you able to provide more detail as to what you are trying to accomplish?

JeremiahHorstick

Sure. 

I have a schedule. On each row I want to create a formula column that answers the questions:

1. Is this task a predecessor to any other task? (Y/N)

2. What is this task a direct predecessor to? 

3. Are all predecessors to this task checked as Done? (Y/N)

Thanks,

-J

 

Paul Newcome

1. =IF(NOT(ISBLANK(Predecessors@row)), "Y", "N")

 

2. This could either be really simple or really complicated based on a few things. Are there multiple predecessors? Do you have lag/lead time? Do you specify Start to Finish/Finish to Start/Start to Start/Finish to Finish?

 

3. Complexity of this directly correlates to the complexity of #2 along with how you would want a specific situation (if necessary) handled.

 

Three rows have a predecessor of row 7. Would you want all three to be checked as "ready" when row 7 is complete or just one? If just one, do you have a priority established?

 

Also may need some type of unique row id established depending on how everything works out.

JeremiahHorstick

Paul

1. The proposed formula will answer the question: "Does this task have predecessors?'. The real challenge is to answer the question: "Is this task a predecessor to any other task?". Maybe a formula that searches the entire range and adds every time the row number appears in the predecessor column of another task will work. 

2. There are multiple predecessors with lags & leads and different relationships SS,SF and FF. 

3. When all of a task's predecessors have the status 'complete' or the 'Done' checkbox is checked the formula would indicate that all predecessors are complete.

I appreciate your help. I can work out the formula. The real essence of my inquiry is how to generally get a variety of predecessors in a formula and I plan to write this out with the formulas I create when we have it figured out to be a resource to anyone. 

Are we essentially going to be parsing text for row numbers and building logic from that? I'm hoping there is a more eloquent solution. 

All task have a unique ID. Searching for a column which contains the row# as a predecessor won't work because of the low range. (Searching all predecessor columns for row '50' will return results for '150','250','350'..... Maybe there's a way to do it, like filling Row 1-99 with null. 

-J

Paul Newcome

Paul

1. The proposed formula will answer the question: "Does this task have predecessors?'. The real challenge is to answer the question: "Is this task a predecessor to any other task?". Maybe a formula that searches the entire range and adds every time the row number appears in the predecessor column of another task will work. 

My apologies. I misread your original question. Looking for the row number itself will have the same drawback as what you mentioned below where "50" will be triggered by "150", "250", etc. We would need to find a way to reference the unique row id's to be able to accurately pull this information.

.

2. There are multiple predecessors with lags & leads and different relationships SS,SF and FF. 

This can get really ugly, really fast.

.

3. When all of a task's predecessors have the status 'complete' or the 'Done' checkbox is checked the formula would indicate that all predecessors are complete.

Got it.

.

I appreciate your help. I can work out the formula. The real essence of my inquiry is how to generally get a variety of predecessors in a formula and I plan to write this out with the formulas I create when we have it figured out to be a resource to anyone. 

Are we essentially going to be parsing text for row numbers and building logic from that? I'm hoping there is a more eloquent solution. 

Unfortunately... Parsing is the best solution.

.

All task have a unique ID. Searching for a column which contains the row# as a predecessor won't work because of the low range. (Searching all predecessor columns for row '50' will return results for '150','250','350'..... Maybe there's a way to do it, like filling Row 1-99 with null. 

-J

.

The solution I am leaning towards (just an idea at this point) is parsing out your predecessors and leveraging that to determine the predecessors' unique row id's. We would also need to build additional logic to account for lag/lead time (not too terrible) and your SS/SF/FF relationships (not sure on the details of how to accomplish this portion).

 

I am thinking PLENTY of helper columns to parse out each detail of each predecessor. Once we get it all broken down, the logic shouldn't be too hard to work out. It's the parsing that is a pain.

 

How exactly is your unique row id established? How much flexibility do you have with this?

JeremiahHorstick

I was hoping this wasn't the case. I will procrastinate on this for a long while. Let you know when I come up with something. 

JeremiahHorstick

@Paul Newcome FYI, I did solve this & call it "Smart Successors" 😁

We just used the API to do it.

Thanks for your help.

Paul Newcome

Unfortunately I don't have much knowledge when it comes to the API, but I'm glad you were able to find a working solution.

Jon Weiss

@JeremiahHorstick we are encountering a similar requirement. Would you be willing to share your code or point me in the right direction? Thanks!

JeremiahHorstick

@Jon Weiss You can look me up and contact me directly. I'm the only Jeremiah Horstick in the world. 😉