Home | Buy | Search | Support | Free Downloads | Tutorial | Testimonials | FAQ | KB Home

TM2005: Generating Sub-Tasks Automatically 

Applies to: Orbisoft Task Manager 2005.  

Novice | Intermediate | Advanced

SUMMARY

Some users want a way to generate a batch of standard sub-tasks for a new project or client job based on a pre-defined set of tasks. That is, they want project templates from which different sets of sub-tasks can be automatically generated.

This article describes one method you can use to achieve automatic sub-task generation if you have a copy of Microsoft Access® installed on your computer or installed on at least one networked computer.

Note that this is a separate topic from Task Quick Entry Templates which populate task fields based on pre-defined Task Description or Task Name templates.

STEPS

To set up automatically generating sub-tasks, follow these steps:

Step 1: Manually create the sub-tasks

1. View the main/sub-task FAQ for a background on how to create sub-tasks grouped together for a project.
    
2. Enter one set of standard sub-tasks for one "project" with the same Task Name but different Task Descriptions.
   
   Example
   
   In this example, XYZ Company produces plastic cubicle partitions for use in schools, sports clubs and stadiums.
   
   

   Task Name	Task Description
   Partition (Type A)	Create Shop Drawing
   Partition (Type A)	Approve Shop Drawing
   Partition (Type A)	Determine Color
   Partition (Type A)	Determine Configuration
   Partition (Type A)	...
   Partition (Type A)	.

    

3. Enter the word "Template" in the Notes field for each task.
    
4. For each of the task date fields (e.g. Date Received, Date Allocated, Deadline Date) accept the default values.
    
5. For the other task fields, enter the normal information that you want for each task.
    
6. After you have entered all of the sub-tasks, close them off by selecting Tasks>>, Close Off from the Task Manager 2005 top menu bar. 

 
Step 2: Create a query

1. Create a new Microsoft Access® database. Name it Project-Generator.mdb
    
2. Select File>>, Get External Data>>, Link Tables... from the Microsoft Access top menubar.
    
3. Link to the Task Manager 2005 data file (Data.mdb) located in the C:\Program Files\Task Manager 2005 folder.
     
4. Create a table connection to the table named "tblJobs" then select Link.
    
5. Create a new query named "Create Tasks".  
    
6. Select View>>, SQL View from the top menubar of Microsoft Access.
    
7. Copy and paste the following SQL text into the SQL window:
    
   INSERT INTO tblJobs (ClientName, ClientSortName, Description, Notes, TeamMemberInitials, DateRecd, DateDeadline)
   SELECT [Enter New Project Name], tblJobs.ClientSortName, tblJobs.Description, tblJobs.Notes, tblJobs.TeamMemberInitials, Date() AS a, Date()+90 AS b
   FROM tblJobs
   WHERE (((tblJobs.ClientName)=[Enter Existing Project Template Name]) AND ((tblJobs.Notes)="Template"));
    
8. Save and close the query.
    
9. In the Microsoft Access database window, drag the query to your Windows Desktop.
    

Step 3: Use the query

Now, every time you want to generate a new project with those sub-tasks, simply:

1. Click the query icon on your desktop.
    
2. A pop-up dialog will ask you to enter a template name to copy from.
    
3. Enter the name of the project template that you created in point 2 of Step 1 above.
    
4. The result will be new live tasks added to the Task Manager 2005 database under the same project name.

______________________________

ADDITIONAL OPTIONS

For advanced use, in addition to the steps outlined above, you may want to include the following functionality for your sub-tasks:

• Set sub-task date values automatically
   

• Update 'downstream' task dates if a task's completion is late or you wish to re-schedule a task
   

• Use Business Dates (e.g. dates excluding weekends, group and personal holidays) rather than calendar dates when calculating date values.
   

Set sub-task date values automatically

When a new set of sub-tasks is created (as has been described in the above steps), you may want to have each sub-task's Date Received, Date Allocated, Deadline Date or other date fields set at standard pre-defined day intervals. 

For example:

To set a date then in this way, follow these steps (in this illustration, the Date Received date field will be used):

Step 1: Set each sub-task's Date Received in your project template

In your project template of sub-tasks, go through each sub-task and set the Date Received value at an offset from your first task in the series. 

For example, using the illustration above, if your first sub-task's Date Received value is June 1, 2005, enter June 7, 2005 for your second sub-task's Date Received value, June 8, 2005 for your third sub-task's Date Received value, and so on. 

Alternatively, you may prefer to create a new user-defined field to handle the offset dates numerically; to do so, create a user-defined field named Start Day, and set the sub-task values for the field (in the illustration shown, at 1,7,8,9,20 and 35 respectively).

Step 2: Amend the sub-task creation query to account for dynamic Date Received values

• Copy the following SQL text in place of the query text shown at point 7 of Step 2 in the instructions shown at the top of this page:
   
  INSERT INTO tblJobs ( ClientName, ClientSortName, Description, Notes, TeamMemberInitials, DateRecd, DateDeadline )
  SELECT 
  [Enter New Project Name] AS Expr1, 
  tblJobs.ClientSortName, 
  tblJobs.Description, 
  tblJobs.Notes, 
  tblJobs.TeamMemberInitials, 
  DateValue([Enter Project Start Date])+([DateRecd]-(DMin("[DateRecd]","tblJobs","[ClientName]='"+[Enter Existing Project Template Name]+"'"))) AS a,
  Date()+90 AS b
  FROM tblJobs
  WHERE (((tblJobs.Notes)="Template") AND ((tblJobs.ClientName)=[Enter Existing Project Template Name]));
   
  

• If you are using the alternate user-defined Start Day method as described above, set your SQL text to (assuming Start Day is the first user-defined field set up, thereby named [UserDefined01]):
   
  INSERT INTO tblJobs ( ClientName, ClientSortName, Description, Notes, TeamMemberInitials, DateRecd, DateDeadline )
  SELECT 
  [Enter New Project Name] AS Expr1, 
  tblJobs.ClientSortName, 
  tblJobs.Description, 
  tblJobs.Notes, 
  tblJobs.TeamMemberInitials, 
  DateValue([Enter Project Start Date])+[UserDefined01] AS a,
  Date()+90 AS b
  FROM tblJobs
  WHERE (((tblJobs.Notes)="Template") AND ((tblJobs.ClientName)=[Enter Existing Project Template Name])); 
   

• In these examples, a default deadline of 90 days for each sub-task is used. To include a relative deadline for each sub-task, taking into account existing sub-task deadline lengths, use the following SQL text:
   
  INSERT INTO tblJobs ( ClientName, ClientSortName, Description, Notes, TeamMemberInitials, DateRecd, DateDeadline )
  SELECT 
  [Enter New Project Name] AS Expr1, 
  tblJobs.ClientSortName, tblJobs.Description, 
  tblJobs.Notes, tblJobs.TeamMemberInitials, 
  DateValue([Enter Project Start Date])+([DateRecd]-(DMin("[DateRecd]","tblJobs","[ClientName]='"+[Enter Existing Project Template Name]+"'"))) AS a,
  DateValue([Enter Project Start Date])+([DateRecd]-(DMin("[DateRecd]","tblJobs","[ClientName]='"+[Enter Existing Project Template Name]+"'")))+([DateDeadline]-[DateRecd]) AS b
  FROM tblJobs
  WHERE (((tblJobs.Notes)="Template") AND ((tblJobs.ClientName)=[Enter Existing Project Template Name]));
  
  

Update 'downstream' task dates if a task's completion is late or you wish to re-schedule a task

In addition to the above, you may, at a later point in time, want to change a Date Received or Deadline Date value for a sub-task from a project you have automatically generated. If you ascertain that this will have an affect on your subsequent sub-tasks' Date Received or Deadline Dates, you may want a way to refresh all of these dates at once. 

In the following example, the alternate method described above is used with a user-defined field (UserDefined01) used to hold the days from the start of the project until each sub-task's deadline. Another user-defined field (UserDefined02) is also used to hold the order for each sub-task in the project.

Create the following two queries as named:

Name: Update Downstreams Sub 01

SELECT tblJobs.JobID,
tblJobs.ClientName, 
tblJobs.Description, 
tblJobs.UserDefined01 AS LateOne_Offset, 
tblJobs.DateCompleted AS LateOne_DateCompleted, 
tblJobs.UserDefined02 AS LateOne_Order
FROM tblJobs
WHERE (((tblJobs.JobID)=[Enter Late Sub-Task ID]));


Name: Update Downstreams

UPDATE [Update Downstreams Sub 01]
INNER JOIN ([Update Downstreams Sub 01].ClientName = tblJobs.ClientName)
SET tblJobs.DateDeadline=DateAdd("d",[UserDefined01]-[LateOne_Offset],[LateOne_DateCompleted])
WHERE (((tblJobs.UserDefined10)>[LateOne_Order]));

Now, simply run the Update Downstreams query to refresh all subsequent sub-tasks in a project. A prompt will ask you to enter the Task ID for the specific late task from which you want subsequent task dates updated.

Use Business Dates (e.g. excluding weekends and holidays) rather than calendar dates when calculating date values

You may want to use Business Dates rather than calendar dates to calculate task dates (e.g. Date Received, Deadline Date). The following Task Manager 2005 Business Date functions can be called from within Task Manager 2005. 

B - Days
Calendar days excluding Saturday and Sunday

C - Days
Calendar days excluding shared holidays. Shared holidays are set up under the Auto Schedule options.

D - Days
Calendar days excluding shared and individual holidays. Shared and individual holidays are set up under the Auto Schedule options. 

Since however your Microsoft Access® queries are outside of Task Manager 2005, you'll need to copy following function code into the Project-Generator.mdb database itself. Copy and paste the following text into a new Module:

Note: The function AppCmdBDays has been provided here for your use. The C-Days and D-Days function code is available only for registered Task Manager 2005 users. If you are a registered user and want to use these functions, email support@orbisoft.com. Please include your registration number, first name, and last name in the e-mail's subject line.

Public Function AppCmdBDays(dtStart As Date, dblNumberofDaysAhead As Double) As Date 

'******************************************************************************* 
'******* (C)Copyright 2002, 2005 Orbisoft Corp.    This code may not  ********** 
'******* be used, copied, modified or distributed without express     ********** 
'******* prior written permission from Orbisoft Limited. Contact      ********** 
'******* sales@orbisoft.com for details.                              ********** 
'******************************************************************************* 

'This function calculates the resultant date of (dtStart) date 
'plus (dblNumberofDaysAhead) non-weekend days ahead. 

On Error GoTo Err_AppCmdBDays 

Dim intDaysToSunday As Integer 
Dim intCalendarDaysAhead As Long 
Dim dtForward As Date 
Dim intCalendarDaysAheadBalance 
Dim i As Integer 

'Push weekend start dates to Monday 
If WeekDay(dtStart) = vbSaturday Then dtStart = DateAdd("d", 2, dtStart) 
If WeekDay(dtStart) = vbSunday Then dtStart = DateAdd("d", 1, dtStart) 

'Calculate 
intDaysToSunday = 7 - WeekDay(dtStart) 
intCalendarDaysAhead = Int((dblNumberofDaysAhead - intDaysToSunday) / 5) * 7 
intCalendarDaysAheadBalance = (dblNumberofDaysAhead - intDaysToSunday) Mod 5 

If intCalendarDaysAhead <= 0 Then 
     intCalendarDaysAhead = 0: intDaysToSunday = 0 
     intCalendarDaysAheadBalance = dblNumberofDaysAhead 
End If 

dtForward = DateAdd("d", (intCalendarDaysAhead + intDaysToSunday), dtStart) 

If intCalendarDaysAheadBalance > 0 Then 
For i = 1 To intCalendarDaysAheadBalance 
     dtForward = dtForward + 1 
     Select Case WeekDay(dtForward) 
     Case vbSaturday, vbSunday 
          dtForward = dtForward + 2 
     End Select 
Next i 
End If 

'Push answers landing on weekend to following Monday 
Select Case WeekDay(dtForward) 
Case vbSaturday, vbSunday 
     dtForward = dtForward + 2 
End Select 
AppCmdBDays = dtForward 

Exit_AppCmdBDays: 
     Exit Function 

Err_AppCmdBDays: 
     MsgBox Err.Description 
     Resume Exit_AppCmdBDays 

'******************************************************************************* 
End Function 

The following SQL text then illustrates how the AppCmdBDays function can be used to replace the standard DateAdd function in the Update Downstreams query shown above.

Name: Update Downstreams

UPDATE [Update Downstreams Sub 01]
INNER JOIN ([Update Downstreams Sub 01].ClientName = tblJobs.ClientName)
SET tblJobs.DateDeadline=AppCmdBDays([LateOne_DateCompleted],[UserDefined11]-[LateOne_Offset])
WHERE (((tblJobs.UserDefined10)>[LateOne_Order]));

MORE INFORMATION

If you need further assistance with automatic sub-task creation, contact Orbisoft Support via email at support@orbisoft.com.

Additional query words: generate template DATA.MDB sub-task project group task create import ODBC