dependent tasks

[ken_s2007]

This is probably a classic type of example, but I haven't found the answer. How to create relationships for dependent tasks. By this, I mean that a task can have 0, 1, or many tasks that must be completed before it can be started, and it can have 0, 1, or many tasks that can be started after it (the current task) is completed. I start with a table 'Task', which has a primary key field, and a field for the task name.

So far I've tried creating a join table with two fk's representing a prior_task and next_task, and relating the pk of Task to the prior_task fk of the join table, and the pk of the 2nd TO of Task to the next_task fk of the join table. Then I create records in the join table, choosing (and thus relating) each pair of prior and next tasks respectively, using a Drop-down list based on a value list derived from the pk of Task. (I realize I could created the records in a portal.)

I've created a portal in Task showing prior_task from the join table (using the join table to show the related records), I only see the current task's pk. I realize the mechanics of my operations are in error, but first I thought I'd try to work out the proper tables, TOs, and relationships. Thanks.

Ken

[ken_s2007]

I now believe I should be using a self-join (for the Task table). I've reviewed the self-join system used for an employee (manager/managee) system, as described in chp. 7 of "Using FileMaker 8..." by Love, Lane, and Bowers. The difference is: A manager can have multiple managees, while an employee can have no more than 1 manager; yet a task can have have multiple dependent prior tasks, and multiple tasks that are dependent on its completion before they can be started. So I'm thinking maybe a join table...

Ken

[comment]

a join table with two fk's representing a prior_task and next_task

This is certainly the correct approach, at least to begin with. I don't know why you couldn't make this work. You should have two occurences of the join table, one for tasks that depend on the current task, and another for tasks that the current task depends upon. Each occurence also needs its own TO of Tasks, to get the corresponding task's info.

The harder part will be to get the tasks that depend on the tasks that this tasks depends upon (and likewise in the opposite direction). Here is a thread that deals with a very similar problem - but I should warn you this is not a subject suitable for a beginner.

[ken_s2007]

The example (employee: manager/managee) I mentioned has you set the manager for the employee, and a portal shows all managees for each employees. In my experiment so far, I tried using a portal to create new records (prior tasks) for a given task (using 'allow creation of records via the relationship'), but that ends up creating new task records, which I don't want.

I'll review the thread you pointed me to, thanks!

Ken

[comment]

You won't be able to create new tasks directly by typing in the prior tasks portal - because the portal is to the join table. I'd suggest you begin with establishing the correct structure first, and worry about the UI later. Here's what your graph should look like, more or less.

[ken_s2007]

Great, very helpful. I've set up that structure; a few items about what I've done so far / questions (if you have the time, that is)

1) The two Requirements TOs are join tables with foreign keys; the fk's related to the Tasks TO are formatted as Drop-down lists using a value list sourced from the Task TO, TaskID field.

2) The fk's related to the PreTasks and PostTasks TOs are formatted as Drop-down lists using a value list sourced from the Task TO, TaskID field (or the PreTasks and PostTasks TOs?).

3) The PreTasks and PostTasks TOs are based on the Tasks base table -- or are they based on separate base tables?

Thanks, Ken

[comment]

There is only one join table (Requirements) - it has two occurences on the graph. Likewise, there's only Tasks table, with three TO's.

The foreign keys linking to the Tasks TO can be set automatically (just allow creation of new records in both Requirements TO's from Tasks). The other foreign key, linking to PreTasks/PostTasks can be set to use a value list. So you only need to select the "other" task, in order to create a join record.

As long as the value list is NOT set to use only related values, it doesn't matter much which TO of the Tasks table you choose. I would pick the "main occurence", i.e. Tasks.

[ken_s2007]

I believe I have everything set up as described, however I'm currently stumped as to how to create the join records. (I spent several hours diligently working on this before posting again;)

In a layout based on the Requirements base table (the join table), I create a new record. I choose a PostTaskID from a Drop-down based on a value list from the Tasks table. I've also added a field for the TaskName -- to represent the PostTask on the layout; it populates fine, however this method is incorrect, since I cannot also display the TaskName for the PreTaskName, since it would display the same TaskName.

Both the Requirements TO's are set to allow creation of new records in the table via this relationship. I realize I'm missing something fundamental here... The only way I was able to have task names show up, was to allow creation of records in the Pre and PostTasks TOs (based on the Tasks base table), which of course is not what is called for here, I believe.

I believe, according to the directions, I need to be creating the joins using a portal in a layout based on the Tasks TO, however I was starting with the join layout to get a better understanding; I'm not had success using a portal in Tasks yet, although I may not have tried every permutation of "Show related records from...", and fields (including "Display data from...").

Thanks, Ken

[comment]

There is no single correct method to create the join records. You can be on a layout of Requirements, create a new record and fill in both foreign keys. Or, you can be on a layout of Tasks, and create the records in one of the two portals. In this case, one of the two foreign keys is filled automatically by the relationship. But you can just as well have a script create the join record, allowing the user to select the participating task/s from a portal, or from a list view - whatever suits your idea of a convenient UI.

If you are selecting from a value list:

- you can see which task you are selecting, by including the task name in the value list as the second field;

- You can see which task you have selected, by placing a related field from the correct TO of Tasks on the layout.

See the attached file.

Note that in version 8 and above, if you define the value list to show only the second field, and the selection field is formatted as a popup, you don't need the related field/s: the selection field will display only the task's name, both during and after selection.

PreRequisites.fp7.zip

[ken_s2007]

Brilliant example file, that made all the difference in the world. I had many mistakes related to which TO the fields were displaying data from, and I had the TOs of the portals on the Tasks layout reversed, i.e., I thought PreTasks (PreRequisites) belonged to Requirements 2, because PreTaskID was related to Tasks; however it is the other way around, because PreTaskID of the Requirements TO is related to TaskID of the PreTasks TO (and vice versa). I will continue to study this until I get it well planted.

Thanks a million for your very clear help and your time!

Ken