Creating the Cascading Courses Input Control

In this step of the Cascading Input Controls case study, we will create an Input Control for selecting Courses that is dependent on the selected Course Categories from the previous Input Control created in the previous step. Once a value or values are selected in the Course Categories Input Control, the Cascading Courses Input Control will  live-update, and only allow the user to choose from Courses within the selected Course Categories.

The process of creating this Cascading Input Control is similar to the process of creating a Standard Input Control, except for the parameter referenced in the query —here, the query will reference the parameter name of the Course Category Input Control. 

1. When logged into Zoola, follow this URL (https://zoola.io/app/flow.html?_flowId=addInputControlFlow&ParentFolderUri=%2FReports) to be taken to the Create Input Control workflow.
   
   
   
   
2. In the Type field, select Multi-Select Query.
3. In the Prompt Text field, enter "Cascading Course".
4. In the Parameter Name field, enter the Parameter ID for the Course Categories ID filter in your Report: "course_id_1".
5. After the optional Description field, ensure that the Visible option is checked.
6. Click the Next button. The Locate Query page appears:
   
   
7. Select Define a Query in the next step, as you will be entering a new unique query instead of selecting an existing one.
8. Click the Next button. The Name the Query page appears:
   
   
   
   
9.  Enter "Cascading Courses" in the Name field (the Resource ID field will auto-populate).
10.  Click the Next button. The Link a Data Source to the Query page appears:
    
    
    
    
11. Select the Select data source from Repository option.
12. Click Browse and identify the Totara data source (Public > Moodle/Totara > Data Connections > Totara), then click Select.
    
13. Click Next. The Define the Query page appears:
    
    
    
    
14. From the Query Language list, select SQL.

15. In the Query String field, enter the following query:

    
    

    select id, shortname, fullname
    from mdl_course
    where $X{IN, category, course_category_id_1}
    order by sortorder

    The where clause $X{...} means that you want to select values where mdl_course.category match course_category_id_1
    
    

16. click the Save button. The Save Parameters page appears:
    
    

    1. In the Value Column field, enter "id". This enables the Input Control window to present values based on the ID fields from the mdl_course_categories table.

    2. In the Visible Columns field, enter "fullname" and click the Add button. This enables the Input Control window to present the ID fields by their associated name. In this case, this will display the Course Categories by their proper names instead of their IDs.

17. Click the Submit button.

Proceed to Adding the Input Controls to the Report.