What would be the best way to auto generate tasks for different stages of a project?

[Alex Quinlan]

Hi,

 

Im trying to figure out the best way to set up a checklist/task style table for my projects. Each Project has different stages of development; I would like to have a list of tasks for each stage.

 

Currently each new project record defaults to enquiry stage. I would like this to populate a list of tasks automatically and then when the user changes the stage of development to "appointment", for example, a new set of tasks would be generated.

 

My initial thoughts are that I need a big script which creates a bunch of related records in the tasks table each time the stage is changed. 

 

I was hoping for some input before I start making the script.

 

 

Any help appreciated.

 

 

[eos]

My initial thoughts are that I need a big script which creates a bunch of related records in the tasks table each time the stage is changed. 

 

“script which creates a bunch of related records in the tasks table” is correct, but it needn't really be “big” – if you create an appropriate structure that the script can use; namely, two related “blueprint” tables in which you define the stages and tasks that can occur with a project. Like …

 

Projects --< Tasks (actual) >-- Stages ( actual [those related via Tasks actual] / definition [all] ) --< Tasks (definition [all] )

 

Then you can

 

1. give stages an numerical sort field

2. assign tasks to stages (and give them numeric order, too)

3. define which task's completion is required for stage completion

4. check the status of any required task of the current stage to allow or disallow entering/adding the next stage

5. read a list of related tasks (per #2) whenever you enter the next stage (as per the order defined in #1)

6. process that list to create the related task records

 

Now your script only needs to perform a check and, if that has been passed, perform the creation of tasks (records) related to current project.

[Alex Quinlan]

Thanks for the help,

 

Forgive me but i'm going to, hopefully, repeat exactly what you just said to me.

 

The tasks(actual) table will hold the task records for the projects and will hopefully be generated by the script.

The stages will hold the different stages of development.

The tasks (definition) will hold records of what those auto generated tasks can be and which stage they are related to.

 

Is this correct?

 

So I will be able to have a script which will generate tasks in Tasks (actual) from the records in Tasks (definition) for the correct Stage.

 

I will start making a test database with this and see how i go.

[Alex Quinlan]

So I managed to put together a script and, amazingly, it appears to be working. I'm surprised how big a grin appeared on my face when i pressed that button.

 

I'm sure it is very ugly.

Freeze Window
Set Variable [ $stage; Value:Projects::Stage ]
Set Variable [ $project; Value:Projects::id ]
Go to Layout [ “Task_list” (Task_list) ]
Perform Find [ Specified Find Requests: Find Records; Criteria: Task_list::id_stage: “=$stage” ] [ Restore ]
Set Variable [ $taskcount; Value:Get ( FoundCount ) ]
Set Variable [ $taskNumber; Value:1 ]
Loop
Go to Layout [ “Task_list” (Task_list) ]
Perform Find [ Specified Find Requests: Find Records; Criteria: Task_list::id_stage: “=$stage” ] [ Restore ]
Go to Record/Request/Page [ $tasknumber ] [ No dialog ]
Set Variable [ $taskname; Value:Task_list::Task_list Name ]
Go to Layout [ “Tasks Records” (Tasks) ]
New Record/Request
Set Field [ Tasks::Task Name; $taskname ]
Set Field [ Tasks::id_projects; $project ]
Set Field [ Tasks::id_stages; $stage ]
Set Variable [ $taskNumber; Value:$taskNumber + 1 ]
Exit Loop If [ $tasknumber > $taskcount ]
End Loop
Go to Layout [ original layout ]

I attached the database - any feedback on script or structure of the databse would be appreciated. Im going to apply this to my project now.

 

Edit - Is there a line i can add to stop doubling up on tasks? 

Checklist_test.fmp12.zip

[eos]

Have a look at the modified file, and see how to check, collect & create with less effort – compared with your method of jumping back & fro within a loop.

 

Also note the modified relationships.

Checklist_test_eosMOD.fmp12.zip

[Alex Quinlan]

Thanks Eos.

 

I'm struggling to understand parts of the script.

ExecuteSQL ( " SELECT id FROM Tasks WHERE id_stage = ? " ; "" ; "" ; $selectedStageID )

I have no SQL knowledge at all - so at first I thought you were writing me a message. I assume this is creating a list of ids for the tasks that are going to be generated. 

Let ( $i = $i + 1 ; $i > ValueCount ( $listOfTaskIDs ) )

This exit loop part I guess is making sure all the tasks have been generated before exiting.

GetValue ( $listOfTaskIDs ; $i )

How does this work?

 

Finally - what is the reason for setting the project id field at the end of the script?

 

Thanks again for the help.

 

 

Edit.

I remembered you had explained $i to me before as a merge variable within the space of a portal and that its part of a conditional formatting calculation. I don't really understand what the a merge variable within the space of a portal means nor can I find the conditional formatting in this instance.

[eos]

ExecuteSQL ( " SELECT id FROM Tasks WHERE id_stage = ? " ; "" ; "" ; $selectedStageID )

I have no SQL knowledge at all - so at first I thought you were writing me a message. I assume this is creating a list of ids for the tasks that are going to be generated. 

 

More to the point: a list of all tasksID that have the foreign ID of the stage you just clicked on (WHERE). You could also use a global field and an additional relationship to get this list.

 

Let ( $i = $i + 1 ; $i > ValueCount ( $listOfTaskIDs ) )

This exit loop part I guess is making sure all the tasks have been generated before exiting.

 

 

Or – read differently – It ensures that the loop finishes when the list has been processed (and doesn't go on infinitely).

 

GetValue ( $listOfTaskIDs ; $i )

How does this work?

 

Get the nth value of the list; since n is our loop counter $i, you get as many results as there are elements in the list: one with each iteration.

 

Finally - what is the reason for setting the project id field at the end of the script?

 

Refresh the relationship (and thus the portal that is based on it) without having to use the costly Refresh Window [ Clear Join Caches ] step.

 

I remembered you had explained $i to me before as a merge variable within the space of a portal and that its part of a conditional formatting calculation. I don't really understand what the a merge variable within the space of a portal means nor can I find the conditional formatting in this instance.

 

These two have nothing to do with each other; and the name of a variable doesn't inherently determine its purpose.

 

You can of course employ a naming scheme that tells you the purpose you use it for. i, j, k are typical names for loop (and other) counters and short-lived variables in general that don't merit a more meaningful name.

[Alex Quinlan]

Thanks EOS