This is a perfect opportunity to make use of QuickBase's ability to create Formula Fields, which can change their value based on your data. We'll create a new Formula - Work Date field that says "If this task is complete, my value is the Actual Finish Date, but if this task isn't complete, then my value is the Calculated Finish Date.". Then, we'll change the Predecessor field to use this new formula field.
To start off with, I'm assuming that you have some sort of Project Management app already. It sounds like you do, but let's briefly go over the fields that are involved (note that your field names might be a little different):
* Predecessors, a predecessor field* Status, a multiple-choice text field, with the choices "Not Started", "In Process", "On Hold", and "Complete"* Start Date, a Work Date field that says when the task starts. If this task has predecessors, this date cannot be earlier than the latest end date of all of this task's predecessor tasks* Estimated Duration, a Numeric field telling how many days this task isas supposed to take* Estimated Finish Date, a Formula - Work Date field that calculates the finish date from Start Date + Estimated Duration* Actual Finish Date, a Date field that has the date the task was actually completed (if it has been completed)
If you go to Settings > Fields and go into the field properties for the Predecessors field, you'll see it has some special options. A predecessor is told to look at two fields, the start and end date field. Both of these must be Work Date fields. The predecessor says "The value of the start field in a task must be on or after the latest date in the end date field of any of its predecessor tasks".
In this case, we want to have an end date field that changes based on the Status field. Predecessors don't let you do this directly, so we'll have to create a new formula field and use it as the end date field for the predecessor.
In your app, go into the Settings area for your Tasks table. Go to the Fields page, and add a new field. Name this field "End Date for Predecessor", and for Field Type pick "Formula - Work Date" (it's down at the bottom of the list of types). Hit Add Fields and the new field should show up in the list of fields. Click on your new field in the list to edit its properties.
Under the section titled "Formula - Work Date field options", select the option "Edit formula using a text field". Enter this formula into the text box:
If([Status] = "Complete", ToWorkDate([Actual Finish Date]), [Estimated Finish Date])
Your fields might have slightly different names than mine do, so make sure to put the right names inside the square brackets in this formula. Now this gets us closer to what we want - if the task is not complete, this field will just be the estimated finish date (which is calculated from duration and start date). However, if the task is complete, this field will take the value of the Actual Finish Date.
Since this is a "behind the scenes" field, you will probably want to scroll down to the Advanced Options section, and uncheck the checkbox for "Add this field to all new reports".
Save your changes to this field, and go back to the field list. Click on the Predecessors field to edit its properties, find the "End Date Field" option, and change it to our newly-created formula field, "End Date for Predecessor". Save your changes, and you should be all set. Now, if your task is incomplete, future tasks that depend on it will not start until the date in the Estimated Finish Date field. When the task is completed, future tasks will start after the Actual Finish Date instead.
If that was a little bit hard to follow, please let me know and I'd be happy to gather up some screenshots and post them here!
Jeff
