Case Study: Adding Custom User or Course Fields to Data Sources

This case study will cover the process of adding customer User Profile / Course Profile fields to your data sources. For the purposes of this case study, we will add a Derived Table to the Course Enrollments Data Source.

To begin, you will need to copy the Course Enrollments Data Source from the Public folder into your private organizational folder tree. To copy the Course Enrollments Data Source, complete the following steps:

1. In the Repository, navigate to Public > Moodle/Totara > Data Sources.
2. Right-click the Course Enrollments Data Source and select Copy.
3. Navigate to the folder in your private organizational tree where you would like to copy the Data Source.
4. Right-click the folder and select Paste.

Now that you have an editable copy of the Course Enrollments Data Source, you can edit it to add the Derived Table.

To add the Derived Table to your newly copied Course Enrollments Data Source, complete the following steps:

1. Right-click the Course Enrollments Data Source and select Edit. The Edit Data Source page opens.

2. Underneath the Data Source Design heading, click the Edit with Data Source Designer link. The Data Source Designer opens to the Display tab.

3. Navigate to the Derived Tables tab.

4. In the Query ID field, enter the name "der_custom_user_fields" or "der_custom_course_fields" depending on which custom profile field you intend to add.

5. In the Query field, you will need a add an SQL statement. Use one of the following formulas:
   
   Custom User Profile SQL

   SELECT d.userid AS userid,
           MAX(CASE WHEN f.shortname = 'Field1 Short Name' THEN d.data ELSE '' END) AS FieldName1,
           MAX(CASE WHEN f.shortname = 'Field2 Short Name' THEN d.data ELSE '' END) AS FieldName2
   FROM mdl_user_info_data d 
   JOIN mdl_user_info_field f ON d.fieldid = f.id
   GROUP BY d.userid

   
   Custom Course Profile SQL

   SELECT d.courseid AS courseid,
          MAX(CASE WHEN f.shortname = 'Field1 Short Name' THEN d.data ELSE '' END) AS FieldName1,
          MAX(CASE WHEN f.shortname = 'Field2 Short Name' THEN d.data ELSE '' END) AS FieldName2
   FROM mdl_course_info_data d
   JOIN mdl_course_info_field f ON d.fieldid = f.id
   GROUP BY d.courseid

   
   
   The Field Short Name is the short name given to the course/user profile field in Moodle/Totara:
   

   
    

6. Click Run Query.

7. If your query runs successfully, you will need to select from the returned results. Select the entire list by holding Command/Shift/CTRL and selecting each field.

8. Click Save Table. Your new Derived Table will appear in the Available Objects panel with the unique Derived Table / Calculated Field identifier.

9. Navigate to the Joins tab and create one of the following joins, depending on your query:
   

   • From the Left Table, select the id field from the mdl_user table, then from the Right Table select the userid field from the der_custom_user_fields Derived Table. Click the Left Outer Join button.
     

   • Or, from the Left Table, select the id field from the mdl_course table, then from the Right Table select the courseid field from the der_custom_course_fields Derived Table. Click the Left Outer Join button.

10. Navigate to the Display tab.

11. In the Resources panel, select to View as: Join Tree.

12. Expand JoinTree_1, then expand the der_custom_user_fields / der_custom_course_fields table:

13. Select the relevant field(s) and drag it into the appropriate set in the Sets and Items panel.

14. Click the OK button to save your changes in the Data Source Designer and return to the Edit Data Source page.

15. Under the Required Information heading, rename the Course Enrollments Data Source in the Name field, in order to avoid confusing it with the Public Course Enrollments Data Source. We will call this Data Source the "CompanyABC Course Enrollments Data Source".

16. To save your changes to the Data Source, click the Submit button.

Alternative Solution

The first method of getting access to custom profile or course fields is the best approach if you have many custom fields and you need them all as part of your Ad Hoc View. If you only require one or two custom profile fields the below alternative solution may provide slightly better performance. Replace the FieldName# place holder with the information associated with the custom fields you are intending to use. All other instructions are the same as the original custom user profile and course fields, including the requirement to Left Outer Join the mdl_user table to the der_custom_user_fields or der_course_user_fields Derived Table.

Custom User Profile SQL

SELECT u.id AS userid,
      d1.data AS FieldName1,
      d2.data AS FieldName2
FROM mdl_user u
LEFT OUTER JOIN (
   mdl_user_info_data d1
   JOIN mdl_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'Programs'
) ON d1.userid = u.id
LEFT OUTER JOIN (
   mdl_user_info_data d2
   JOIN mdl_user_info_field f2 ON d2.fieldid = f2.id AND f2.shortname = 'Programs1'
) ON d2.userid = u.id

Custom Course Profile SQL

SELECT u.id AS userid,
      d1.data AS FieldName1,
      d2.data AS FieldName2
FROM mdl_user u
LEFT OUTER JOIN (
   mdl_course_info_data d1
   JOIN mdl_course_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'Programs'
) ON d1.userid = u.id
LEFT OUTER JOIN (
   mdl_course_info_data d2
   JOIN mdl_course_info_field f2 ON d2.fieldid = f2.id AND f2.shortname = 'Programs1'
) ON d2.userid = u.id