Enabling Moodle course and activity completions en masse for Moodle 2.7

This week I am going to talk about a method to enable course completions en-masse across a library of existing Moodle courses.

Stupid Moodle Tricks!

This is my pet name for these little tips, #stupidmoodletrick anyone? Think Letterman's stupid human tricks (video below).  These are generally one-off tips, tricks, hacks to manipulate Moodle, generally as a time saver or to accomplish something Moodle isn't generally known to do.

So last week, a client asked me if I could help save them some time in converting some old Moodle 1.9 courses. Actually the more honest version is my lovely and dear wife, Michelle, volunteered that I could totally save the client hours of work by "changing a few database settings." And it turned out she was right!

The following assumes you a have already enabled course completion tracking in your site administration settings. Be aware all of these changes are accomplished by running the below queries directly on your Moodle database. As always when making these sort of changes, be sure you have a good backup and that you know how to recover it to make a working site. Things can and do go wrong and in unexpected ways. I also recommend you use a test site first to become familiar with the technique.

This method was tested on Moodle version 2.7.1+, and 2.7.2+. As with any method that directly interacts with the Moodle database, things almost certainly will change with new versions. It probably will also work with older Moodle 2 versions, but again test first.

Enable course completions on all courses

First we need to enable completion tracking for all courses. 

update mdl_course set enablecompletion = 1 where 1;

Turn on activity completion for all course modules (ie activities and resources)

This query enables the setting for all course modules across the site. It sets 'Completion Tracking' to 'Show activity as completed when conditions are met.' It also sets 'Require view' as the criteria, as illustrated in the screenshot below.

update mdl_course_modules set completion = 2, completionview = 1 where 1;

In contrast, if you wanted to set the activity completion to be marked manually by the student, you would run the following query instead.

update mdl_course_modules set completion = 1 where 1;

Which results in the following settings

Limiting which type of modules are set

Let's say we only want to enable completion tracking for all quiz modules. We would first need to have a look at the mdl_modules table and find the id assigned to the quiz module. We can use the following query.

SELECT * from mdl_modules;

Results from my test site are shown below.

We can see that the quiz module id is set to 25. Be aware this will be different for different sites. For example, I tried this on another test site and the quiz id was set to 16.

So now we can create a query to only modify quiz modules.

update mdl_course_modules set completion = 2, completionview = 1 where module=25;

Turn on grade required completion element for all quiz modules

The completion grade requirement is stored by setting a value of 0 in the completiongradeitemnumber field. Knowing this we can set the value and combined with the where clause above to limit to only quizzes. This can be a very useful technique since many compliance courses will use a quiz grade as their completion criteria.

update mdl_course_modules set completiongradeitemnumber=0 where module=25;

Note that this method only changes the setting to require a grade. It doesn't require a specific grade which need additional manual setup in the Moodle grade book. I think setting a specific grade is considerably less trivial. Let me know in the comments if you have a query / method to do this.

The overall result of applying this technique for the requesting client was to save 800 course setting edits, and about 2,500 activity setting edits which probably would have taken several days to a week to do manually.

A note about method

When developing a #stupidmoodletrick, I use a throw away test site. I usually just use the Moodle all-in-one installer to create the site. I then setup the test site with whatever test users / courses, and settings which are needed for the scenario. I then have a look at the database. The all-in-one installer includes phpMyAdmin which is an easy way to have a look at the database structure in a graphical environment. Moodle is good about using descriptive names for its tables and fields. I try to identify which table I think will be used to save the information and which are likely fields. Then, I go into Moodle and manually change the setting manually. After this, I go back to the database and look at the changes made and attempt to determine what is getting set.

Next, I attempt to manually edit the database using the information I have learned. I make the edit and then I purge all caches in Moodle. Finally, I access Moodle again to confirm that I have achieved the desired result. I also look to see if anything appears to be broken. I repeat the process until I have a working method that doesn't break anything.

Note a more thorough method is to trace the actual Moodle queries in the source code, but I find I normally don't have to do this. In particular think of settings that may impact more than one table (related tables and fields), we want to avoid introducing later hard to find problems.