Recurring Tasks

A SharePoint Online tutorial by Peter Kalmström

SharePoint iconSharePoint has no simple way to handle recurring tasks, but by letting an Access query upate a To Do list you can still make it easy to both remember recurring tasks and to verify that the tasks have been done.

In the first demo below Peter Kalmström, CEO and Systems Designer of kalmstrom.com Business Solutions, exports an Excel table with info about tasks, assigned people and due dates to a SharePoint list. This list is called Every Month and shows tasks that have to be done repeatedly each month. It has columns for What, Who and Day of Month.

Then Peter creates a To-Do list and copies the What and Who data from the Every Month list into the Task Name and Assigned To fileds in the To Do-list.



With the method shown above the Due date column cannot be copied as easily as the other columns, and you have to copy and paste recurring tasks for every month.

A better solution is to let Microsoft Access handle the update with a query. That is what Peter shows in the demo below, where he creates a query that takes data from the Every Month list and the UserInfo list and appends it to the To Do list.

The UserInfo list is a hidden SharePoint list that becomes visible when you link Access to the SharePoint site. When you select to use a list that has an Assigned To field, Access creates a linked UserInfo table from the UserInfo list as well, even if you don't check the box for it.
 

Create Access queries that add recurring tasks into a SharePoint list

  1. Open a blank desktop database and close the default table.
  2. Under the EXTERNAL DATA tab, click on More and select SharePoint list, to link the two recurrent tasks lists to Access. Give the URL to the site, and use the Linked table option. Click on Next.
  3. Check the Every Month list and the To Do list. Click OK, and two new tables with the same names and content as the lists will be created in the database. A linked UserInfo table will also be created.
  4. Under the CREATE tab, click on the Query Design button. Add the Every Month table, and select the What and Who fields. (Add by double clicking on the field or by clicking on the Add button.)
  5. Under the DESIGN tab, click on the Append button. Append the What field in the Every Month table to the Task Name in the To Do table.
  6. Right click in the query pane and select Show Table... Add the UserInfo table.
  7. Connect the Who field in the Every Month table to the ID of the UserInfo. This will be shown in the second column in the Query, and you can append the Assigned To field.
  8. To have the Due Date info into the To Do table, select the Every Month table and click on the Paramenters button under the DESIGN tab.
  9. Enter Year and Month. Both should be Integers.
  10. Click on the Builder button under the DESIGN tab.
  11. Select Functions, Built-in Functions, Date/Time and Date Serial.
  12. Still in the Builder, select the query you just created and the parameters Year and Month.
  13. For the day, select the Every Month table and Day of Month. Click on Value and then OK.
  14. Now the expression you have built will be filled out in the third query column, and you can change the text "Expr1" into Due Date and append the Due Date field.
  15. Click on the Run button under the DESIGN tab to run the query. You will now be asked to enter values for the parameters Year and Month. Use numbers for both.
  16. Run the query for every month and year, to have the SharePoint To Do list updated with all the recurring tasks.




Learn more



---------------------------------------------------------------------------------------







Always the latest news in the kalmstrom.com blog



Follow kalmstrom.com on LinkedIn Facebook, Google+  or Twitter!

back icon next icon