Insane or I'm a dufus, either or both can be true.

Insane or I'm a dufus, either or both can be true.

by Sphere X -
Number of replies: 27

This seems crazy, so for instance:

Employee emails me today and says he got course reminder emails. Swears he finished all 20 courses, asks me to see what he has left to complete.

The way it is now, I have to click all 20 courses individually, search his name, find out which sections of each course are incomplete and then document it, paste it into an email and send it back to him. That’s an insane amount of clicks to find out that information. If 100 employees asked me to do that, then turn around and ask me to recheck after they supposedly completed more, holy crap. =) 

Seems like a no brainer function of an LMS, has to be a way to do that. I can’t be the first want this feature in 20 years of Moodle.

Ideas, thoughts? Looking for something that looks like this:



Average of ratings: -
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
I have students, not employees, and the grade book lets students know what they have completed. So I never get this question. But you seem to be in a different situation. Might I ask you, what do you look for when an employee asks you this question? In other words, what tells "you" that a student has completed a section that somehow they do not see? Moodle does have an "Activity Completion" report, but I am not sure if you are using this Moodle feature.
Average of ratings: Useful (1)
In reply to Rick Jerz

Re: Insane or I'm a dufus, either or both can be true.

by Mary Cooch -
Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Testers Picture of Translators
In addition to what Rick says, if you have course completion enabled then on the students' Dashboard they should see a percentage bar of completion and it is also possible to add a Course completion status block to the course which shows the student/employee what they have completed and what they still have to do - so I do wonder if you have some customised Moodle install ? What  version of Moodle do you have and who hosts it?
Average of ratings: Useful (1)
In reply to Mary Cooch

Re: Insane or I'm a dufus, either or both can be true.

by Sphere X -
I've directed them to use the the dashboard (list mode) to see what they have and haven't completed. Again, these are not students but adult employees (adults are worse). There has to be a way I can just pull up a list and say "Jim, you're still missing 4 courses!", then they can use the dashboard to figure out what activities in each course are left to complete. I just care about overall mass course completion. Make sense? It does in my head. =)
Average of ratings: -
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Sphere X -

Something like this...instead of single course and multiple activities, just the courses and whether they are complete or not.


Average of ratings: -
In reply to Rick Jerz

Re: Insane or I'm a dufus, either or both can be true.

by Sphere X -
So, I'm dealing with employees vs students. We have 20 courses (2 or 3 activities each) and 250 staff. When my boss asks me, where are we in regards to staff training, I have to click each course, click view course report (as seen below), scroll down and click export CSV, import into Excel, create a table, sort by completed, pull out those that haven't completed, and send a list to the boss. Simply put, I need something similar to the attached image but instead of showing a single course with each activity completion, I need each course completion listed in the columns.


Average of ratings: -
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Sphere X -

I guess I may have to try to pull the info out via a mysql query although I'd rather not have to write some php code to accomplish what I need. It could get kinda hairy for something who's not good at that sort of thing. I can almost view what I need from here, but I need to map the userID with the actual username and the courseID with the course name.


Average of ratings: -
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
You are making some progress. I don't use course completions, so I am not familiar with this particular table.

In SQL-like language, you want to select all records from this table where time completed is null. Right?
However, you also said that you want to know about activities status. Right?

In SQL, finding all who have not completed courses takes the form of:
SELECT * FROM mdl_course_completions
where timecompleted is  null

Well, this is an entry into writing SQL. To do this well, of course, you need to know SQL. Or you need to find someone who does, like a college student or someone in your organization. With SQL, you can get to your desired data, and you can export it to csv or Excel.

If you want a pretty report, you need to go deeper. But first, I suggest getting to the data.
Average of ratings: Useful (1)
In reply to Rick Jerz

Re: Insane or I'm a dufus, either or both can be true.

by Sphere X -

So I have this code now...and it spits out userid, course, and a completed time or null as you suggested. I need to somehow, translate userID into username from mdl_user and same for the courseID. Getting close!


Average of ratings: -
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
I am not a php programmer, so I can't help with your code.

You will need to join tables to get to other information. For example:

SELECT cc.course, cc.userid, cc.timecompleted, u.lastname, u.firstname
FROM mdl_course_completions cc
join mdl_user u on u.id = cc.userid
where timecompleted is null

I am not sure what database interface product you are using, if any.
Average of ratings: Useful (1)
In reply to Rick Jerz

Re: Insane or I'm a dufus, either or both can be true.

by Sphere X -

Got the join working for the ID/Name replacement. Haven't figured out how to do the same for the courseID/CourseName yet since I'd be technically adding multiple JOINS to the same FROM statement. MYSQL gets angry. Gonna head home and sleep on it. Thanks for the tips so far guys, much appreciated!


Average of ratings: -
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Now add the code to join this to courses, as in:

SELECT cc.course, cc.userid, cc.timecompleted, u.lastname, u.firstname, c.fullname
FROM mdl_course_completions cc
join mdl_user u on u.id = cc.userid
join mdl_course c on cc.course = c.id
where timecompleted is null

By now, you might be seeing the function of "where". I just happened to use "null" for those courses not completed. But you might want to remove this if you want to see all records. You also have timestarted and timeenrolled available to you. And you have other information from the users table and courses table that you might want to add. You are probably getting the hang of this SQL.  Perhaps you are even starting to enjoy this.

If you put the results into a csv file, you can then open the csv file in Excel, do a pivottable, and get close to the kinds of reports that you desire.

You might need to reformat the "time" fields to suit your needs.

Being able to write SQL with your Moodle tables is an extremely powerful feature, and benefit of using Moodle.  Can't do it this easily with the other major LMSs. 
Average of ratings: Useful (1)
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Melanie Scott -
Picture of Particularly helpful Moodlers
Have you considered using Ad-Hoc Reports or Configurable reports modules? Rather than tampering with MySQL in the background, you build the report and run it on the front end. And...you can make it possible for your boss to access it directly when he wants to see the results. It sounds like you've got some sql skills and can probably put together a report that pulls all the data.

Another option would be a 'final' class that includes prerequisite course completion requirements (assuming you use course completion) for the 20 courses they have to do. if they reach all 20, you set up a certificate or something that indicates completion. Now that I'm thinking of it...that would probably be much easier than a sql report...
Average of ratings: Useful (2)
In reply to Melanie Scott

Re: Insane or I'm a dufus, either or both can be true.

by Sphere X -
Very interesting, a class report (if there is such a thing or a module for it) that included all said courses would be exactly the kind of thing I'm looking for. I'm not interested in any data other than, what employees are left that have not completed all required courses so I can reach out to each of their respective managers to assure they complete it.

Lots of smart folks on this forum!

This is what I have so far, which is at least doable. Haven't figured out how to translate course ID into the course name yet but at least I can paste into excel and cross reference the ID to a printed list of which courses are what ID, thanks to Rick Jerz's tips.

https://lms.atc.systems/creport.php
Average of ratings: -
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Melanie Scott -
Picture of Particularly helpful Moodlers
See, that is where a single course requiring all courses be complete would save you time. The course completion report would show all the required courses and whether they were complete or not. Then you would download one report and see all completions. No extra sql and table crazy required.
Average of ratings: -
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
A couple things, Sphere X.

Melanie is not only the smart one, she is also the good looking one. Consider her advice.

Above, I gave you the SQL that would include the Course Name.

For your "time completed" column, you can consider revising your SQL to something like this:

 SELECT cc.course as CourseID,
       cc.userid as UserID,
       from_unixtime(cc.timecompleted,'%Y %b %d') AS 'Date_Completed',
       u.lastname as LastName,
       u.firstname as FirstName,
       c.fullname as CourseName
FROM mdl_course_completions cc
JOIN mdl_user u ON u.id = cc.userid
JOIN mdl_course c ON cc.course = c.id
WHERE timecompleted IS NULL
Average of ratings: Useful (1)
In reply to Rick Jerz

Re: Insane or I'm a dufus, either or both can be true.

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
And on the last line, you could also add:

order by c.fullname, u.lastname, u.firstname

or

order by u.lastname, u.firstname, c.fullname
Average of ratings: Useful (1)
In reply to Rick Jerz

Re: Insane or I'm a dufus, either or both can be true.

by Sphere X -

Rick, it's almost perfect. Not getting date stamps in the column for some odd reason (tried a few changes, still working that issue). Last thing is to show even the NULL dates so I can see who hasn't completed. 


Average of ratings: -
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Sphere X -
Showing me NULL value rows now but can't get the value of the date completed to show in last column still. Will keep plugging away.
Average of ratings: -
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Sphere X -
And there's the infamous punctuation that always goes missing.

Think we're almost there!

https://lms.atc.systems/creport2.php
Average of ratings: -
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Sphere X -
Sooooo close I can feel it in my bones! For whatever reason, it's not cross referencing the "enrolled" courses (didn't actually think about that until now after I said I wanted to see ALL course, cause I'm awesome). This user is not actually enrolled in the highlighted courses so obviously it shows an empty completed value as it should for both unenrolled courses and incompleted courses. I need to filter out courses not enrolled in, gonna have to bring in another type of FROM JOIN statement I think.


Average of ratings: -
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Shirley Gregorczyk -
Picture of Particularly helpful Moodlers
%%FILTER_COURSEENROLLEDSTUDENTS%%

Note: this normally works well, but I sometimes have a problem because I use cohort sync. That object has no start/end dates and causes me much grief when I create a query.
Average of ratings: -
In reply to Shirley Gregorczyk

Re: Insane or I'm a dufus, either or both can be true.

by Sphere X -
That's what I'm looking for Shirley (just gotta translate that from a Moodle report to a SQL statement)! Thanks.
Average of ratings: -
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
I think that you are very close to what you desire. As you can see, the thing about writing SQL is that one question always leads to another. The job (meaning the query) is never finished. But you are getting the hang of it.
Average of ratings: -
In reply to Sphere X

Re: Insane or I'm a dufus, either or both can be true.

by Melanie Scott -
Picture of Particularly helpful Moodlers
Also on pulling only enrolled...if the student is enrolled more than one way (was manually or self enrolled and later added to a cohort or part of two cohorts...), sometimes it will pull the same course twice and you may have to account for that. But maybe that doesn't happen for you.
Average of ratings: -
In reply to Rick Jerz

Re: Insane or I'm a dufus, either or both can be true.

by Melanie Scott -
Picture of Particularly helpful Moodlers
Rick, you gave me a smile there. I needed a smile this week, so thank you.
Average of ratings: -
In reply to Melanie Scott

Re: Insane or I'm a dufus, either or both can be true.

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Here's another... "You are the best, Melanie." (Can't wait to see you again at either MountainMoot or another event this year.)


Average of ratings: -
In reply to Rick Jerz

Re: Insane or I'm a dufus, either or both can be true.

by Melanie Scott -
Picture of Particularly helpful Moodlers
You deserve a Helpful and Makes People Smile badge, Rick! I'm really looking forward to the Mountain Moot, too. In person, virtual...whatever! I love moots. Look forward to seeing you *there*.
Average of ratings: -