Task Scheduling

[sdaigle2]

I would like to be able to schedule tasks and have them appear on the list automatically. Here's how I think we should accomplish it.

Step 1: Prepare Databases

• Create a new table called SheduledTasks
• ScheduledTasks should have all the same Field Names, Data Types, and relationships as the Tasks table except for a few things:

• Primary key is called ScheduledTask_ID instead of Task_ID
• Priority, CompleteDate, ActualHours, is not in this table
• A new field is called Schedule with data type Short Text. Valid values in this are: weekly, monthly, quarterly, biannually, or annually
• A new field is called StartOnDate with data type Date/Time

• Within the Tasks table, create a new Field Name called ScheduledTask_ID with a data type Number, and establish a 1-to-many relationship between ScheduledTasks and Tasks so that each ScheduledTask can have many Tasks. This relationship is optional.

Step 2: Make Front End Interface to Create Scheduled Tasks

• Create a basic form called frmScheduledTasks that allows the user to create, edit, and delete scheduled tasks.
• Limit the Entries to Schedule as weekly, monthly, quarterly, biannually, or annually

Step 3: Create a Macro that runs every day at 5am

Reference This: http://p2p.wrox.com/excel-vba/71210-schedule-vba-macro-run-specific-time.html This macro should do the following for every entry in ScheduledTasks

If Schedule == "weekly" & (Today() - StartOnDate) % 7 == 0 Then
      success = createTask(ScheduledTask_ID)
Else If Schedule == "monthly" & (Today() - StartOnDate) % 30 == 0 Then
      success = createTask(ScheduledTask_ID)
Else If Schedule == "quarterly" & (Today() - StartOnDate) % 91 == 0 Then
      success = createTask(ScheudledTask_ID)
Else If Schedule == "biannually" & (Today() - StartOnDate) % 183 == 0 Then
      success = createTask(ScheudledTask_ID)
Else If Schedule == "annually" & (Today() - StartOnDate) % 365 == 0 Then
      success = createTask(ScheudledTask_ID)
End If

Function createTask(ScheduledTask_ID)
    'check the Tasks table for a task with this ScheduledTask_ID.  
    'If one exists that was already created today, do nothing.  
    'If it does not exist yet, create a new Task with all information in this ScheduledTask this new Task should have ScheduledTask_ID set to the correct Scheduled Task
    'return 1 for success, and 0 for failure
End Function

[smsu07]

If we declare the ScheduledTask_ID as Primary Key then we cannot define the one to many relationship with that. Instead we can create two field one for Primary key and another for Task ID as foreign key.

Also in my point of view one task should have one scheduler. Please suggest.

[sdaigle2]

I think it might make more sense if we rename the ScheduledTasks to TaskScheduler. The idea is that the user creates a TaskScheduler, and then every week, month, year, etc... that TaskScheduler creates a Task. So 1 TaskScheduler will create multiple Tasks for as long as it exists.

A Real Example Our company has a giant tank of water that we use to cool machinery. Every month, we need to drain the water and replace it with new water to get rid of contaminants. We often forget to do this, and then machinery gets broken.

1. Scott creates a TaskScheduler called "Change the water" assigned to Jason
2. On February 1, a Task automatically gets created called "Change the water" with ID=123
3. On February 2, Jason sees the Task. On February 3, he changes the water and marks the Task Complete.
4. On March 1, a Task is automatically created called "Change the water" with ID=124
5. On March 10, Jason sees the Task and completes it.
6. This repeats for many months.
7. On October 10, we buy a fancy new Tank that automatically drains its own water and refills it without anyone's help. Now Scott deletes the TaskScheduler called "Change the water"
8. No more tasks get created called "Change the water"

I recommenced that we implement this in stages. Once you complete Step 1, please commit and push, and I'll check it over and we can discuss before continuing to Step 2.

[smsu07]

Ok, Got it. Also updated to hithub, I have completed step 1 and started step 2. Please check.

[sdaigle2]

Please add the following to ScheduledTasks table Emp_ID (link to Emp_ID in Employees) Job_ID (link to Job_ID in Jobs) Phase_ID (link to Phase_ID in Phases)

Please remove the following from Scheduled Tasks table Completed

[smsu07]

also please let me know the usage of those fields like Emp_ID, Job_ID and Phase_ID, Will i add those in the new Scheduled Tasks form as well.

so when creating task from shceduled task we will input those fields (Emp_ID, Job_ID and Phase_ID) as well, right?

Also will i add scheduled task button in the main form?

[sdaigle2]

• Yes, please add Emp_ID, Job_ID and Phase_ID to the Scheduled Tasks form as well.
• When a Task gets automatically created from a ScheduledTask, all fields (including Emp_ID, Job_ID and Phase_ID) should be copied from the ScheduledTask into the Task.
• Yes, add a scheduled task button in the main form.

[smsu07]

Completed Step-2 and uploaded to github, Please check.

[sdaigle2]

I have a few small change requests before you continue to Step 3.

1. in frmScheduledTasks, create a combo box above cmbjob_ID and under cmbEmp_ID. The new combo box should display the list of customers. Its label should be Customers, and name should be cmbCust_ID. cmbjob_ID should now now only display jobs associated with the customer selected in cmbCust_ID. If nothing is selected in cmbCust_ID, then cmbjob_ID should be empty.
2. I have reopened Issue #10 with a small modification that needs to be made because of what we're doing here. Please look at the last comment in Issue #10 and implement it.

[smsu07]

from where the timer should get executed? from the Scheduled tasks form or just after opening the Application

[sdaigle2]

The timer should get executed when the application opens. https://www.mrexcel.com/forum/excel-questions/275222-vba-run-macro-specific-time.html

[smsu07]

Like to create a new table for the Timer Start Time , currently it is in the form as hard coded which is not code, please let me know your idea.

[sdaigle2]

Yes. If it is the best practice to create it, then yes please do that.

[smsu07]

Completed Step-3 and uploaded to github, Please check. Added another Table -Alarm, set the Alarm Start time according to your requirement also i Alarm Interval, I set for 5 AM and and for every one hour the alarm will start.

[smsu07]

Any Update?

[sdaigle2]

Sorry, I've been busy with other project this week and I haven't had time to look at it. I will in the next week though.

On Tue, Jan 23, 2018 at 7:13 AM, Syed Md Shahid Ullah < notifications@github.com> wrote:

Any Update?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/sdaigle2/pdi-database/issues/14#issuecomment-359786290, or mute the thread https://github.com/notifications/unsubscribe-auth/AJCAbMntmzcRab_5sLUJp2y-F_zcZysOks5tNdsIgaJpZM4RMgc- .

-- Scott Daigle 630-341-1942

[smsu07]

ok, i was thinking that you are still in new year vacation.