1. Brian Lewis Avatar Brian Lewis
  2. Untitled project
  3. pineapples
  4. Issues

Support upload of SchoolInspections from Kobo toolbox

Issue #297 resolved
Brian Lewis repo owner created an issue

Allow a custom-designed Kobo toolbox form, representing a school report, to be loaded and reported from the EMIS system.

This requires the capacity to store and retrieve arbitrary collection of fields as defned by the creator of the report - will use an Xml "blob" on the SchoolInspection table for this purpose

Comments (3)

  2. Brian Lewis reporter

    2.3 KOBO TOOLBOX INTERFACE

    This feature allows custom school inspections to be implemented as Kobo Toolbox forms, and loaded into SIEMIS. Of course, this facility can apply to any Pacific EMIS installation, and can work with Excel spreadsheets of conforming layout, regardless of whether or not those spreadsheets originated with Kobo.

    Set Up

    First step in Set up is to create in SIEMIS a School Inspection Type to represent the custom inspection type. This can be done either using the Admin function in desktop SIEMIS, or the new /tables maintenance functions in Pacific EMIS. Next, design the form in Kobo toolbox. There are just four requirements: - The form must have a column with Id inspectionType (case-sensitive). This should be a calculated field, and its value should be the new inspection type code set up in SIEMIS. - The form must have a column schoolNo . It doesn’t matter how this is implemented; for example, as a straight text box, as a dropdown list, or even as a calculation of some sort. The value in this column is mandatory and must be a valid SIEMIS School No. - The data columns should have simple, consistent names. Kobo will assign a column name by using the input prompt for that column, replacing spaces with underscores. You should remove these generated names and replace them with simpler ones. These column names becomes the field names you will manipulate in SIEMIS, so don’t get stuck with unwieldy ones. Note also that a column name must be a valid Xml Attribute identifier. Specifically it cannot begin with a number, and must contain only letters, digits, hyphens, underscores, and periods. - The form must have columns start and end recording the start and end date/time that the survey took place. These are always included in Kobo Toolbx forms, but if you are getting the Excel from another source, or creating it by hand, you will need to ensure these fields are present.

    Loading

    A form meeting this design can be loaded into SIEMIS using the kobo/upload page. First, download the form data from Kobo as an Excel file. IMPORTANT: The Kobo Excel file as downloaded has a slightly non-standard format, so open it in Excel and immediately save it to straighten this out. You may also review the downloaded Excel and make any corrections you think necessary. When you load the file into SIEMIS, the uploader writes a new school inspection record of the custom type for each school. The date of the inspection is taken from the Kobo start and end fields (present in every Kobo survey). The custom fields in your survey are stored as a single Xml “blob” on the inspection record.

    Reporting

    To report on the inspection type, create a new view in the SIEMIS database with the name pInspectionRead.<inspectiontype> i.e. for inspection type ENVER this view is pInspectionRead.ENVER The job of this view is to dig your custom fields out of the school inspection’s Xml. This sample shows how:

    ALTER VIEW [pInspectionRead].[ENVER]
WITH VIEW_METADATA
AS
Select S.*
, S.extraData.value('(/row/@Index)[1]','int') rowIndex

, S.extraData.value('(/row/@M0)[1]','int') M0
, S.extraData.value('(/row/@F0)[1]','int') F0
, S.extraData.value('(/row/@C0)[1]','int') C0

, S.extraData.value('(/row/@M1)[1]','int') M1
, S.extraData.value('(/row/@F1)[1]','int') F1
, S.extraData.value('(/row/@C1)[1]','int') C1

, S.extraData.value('(/row/@M2)[1]','int') M2
, S.extraData.value('(/row/@F2)[1]','int') F2
, S.extraData.value('(/row/@C2)[1]','int') C2

, S.extraData.value('(/row/@M3)[1]','int') M3
, S.extraData.value('(/row/@F3)[1]','int') F3
, S.extraData.value('(/row/@C3)[1]','int') C3

, S.extraData.value('(/row/@M4)[1]','int') M4
, S.extraData.value('(/row/@F4)[1]','int') F4
, S.extraData.value('(/row/@C4)[1]','int') C4

, S.extraData.value('(/row/@M5)[1]','int') M5
, S.extraData.value('(/row/@F5)[1]','int') F5
, S.extraData.value('(/row/@C5)[1]','int') C5

, S.extraData.value('(/row/@M6)[1]','int') M6
, S.extraData.value('(/row/@F6)[1]','int') F6
, S.extraData.value('(/row/@C6)[1]','int') C6

, S.extraData.value('(/row/@M7)[1]','int') M7
, S.extraData.value('(/row/@F7)[1]','int') F7
, S.extraData.value('(/row/@C7)[1]','int') C7

, S.extraData.value('(/row/@M8)[1]','int') M8
, S.extraData.value('(/row/@F8)[1]','int') F8
, S.extraData.value('(/row/@C8)[1]','int') C8

, S.extraData.value('(/row/@M9)[1]','int') M9
, S.extraData.value('(/row/@F9)[1]','int') F9
, S.extraData.value('(/row/@C9)[1]','int') C9

, S.extraData.value('(/row/@M10)[1]','int') M10
, S.extraData.value('(/row/@F10)[1]','int') F10
, S.extraData.value('(/row/@C10)[1]','int') C10

, S.extraData.value('(/row/@M10)[1]','int') M11
, S.extraData.value('(/row/@F10)[1]','int') F11
, S.extraData.value('(/row/@C10)[1]','int') C11

, S.extraData.value('(/row/@M10)[1]','int') M12
, S.extraData.value('(/row/@F10)[1]','int') F12
, S.extraData.value('(/row/@C10)[1]','int') C12

, S.extraData.value('(/row/@M10)[1]','int') M13
, S.extraData.value('(/row/@F10)[1]','int') F13
, S.extraData.value('(/row/@C10)[1]','int') C13

, S.extraData.value('(/row/@M10)[1]','int') M14
, S.extraData.value('(/row/@F10)[1]','int') F14
, S.extraData.value('(/row/@C10)[1]','int') C14

, S.extraData.value('(/row/@Photo0_1)[1]','int') Photo0_1
, S.extraData.value('(/row/@Photo0_2)[1]','int') Photo0_2
, S.extraData.value('(/row/@Photo0_3)[1]','int') Photo0_3

, S.extraData.value('(/row/@Photo1_1)[1]','int') Photo1_1
, S.extraData.value('(/row/@Photo1_2)[1]','int') Photo1_2
, S.extraData.value('(/row/@Photo1_3)[1]','int') Photo1_3

, S.extraData.value('(/row/@Photo2_1)[1]','int') Photo2_1
, S.extraData.value('(/row/@Photo2_2)[1]','int') Photo2_2
, S.extraData.value('(/row/@Photo2_3)[1]','int') Photo2_3

, S.extraData.value('(/row/@Photo3_1)[1]','int') Photo3_1
, S.extraData.value('(/row/@Photo3_2)[1]','int') Photo3_2
, S.extraData.value('(/row/@Photo3_3)[1]','int') Photo3_3

, S.extraData.value('(/row/@Photo4_1)[1]','int') Photo4_1
, S.extraData.value('(/row/@Photo4_2)[1]','int') Photo4_2
, S.extraData.value('(/row/@Photo4_3)[1]','int') Photo4_3

, S.extraData.value('(/row/@Photo5_1)[1]','int') Photo5_1
, S.extraData.value('(/row/@Photo5_2)[1]','int') Photo5_2
, S.extraData.value('(/row/@Photo5_3)[1]','int') Photo5_3

, S.extraData.value('(/row/@Photo6_1)[1]','int') Photo6_1
, S.extraData.value('(/row/@Photo6_2)[1]','int') Photo6_2
, S.extraData.value('(/row/@Photo6_3)[1]','int') Photo6_3

, S.extraData.value('(/row/@Photo7_1)[1]','int') Photo7_1
, S.extraData.value('(/row/@Photo7_2)[1]','int') Photo7_2
, S.extraData.value('(/row/@Photo7_3)[1]','int') Photo7_3

, S.extraData.value('(/row/@Photo8_1)[1]','int') Photo8_1
, S.extraData.value('(/row/@Photo8_2)[1]','int') Photo8_2
, S.extraData.value('(/row/@Photo8_3)[1]','int') Photo8_3

, S.extraData.value('(/row/@Photo9_1)[1]','int') Photo9_1
, S.extraData.value('(/row/@Photo9_2)[1]','int') Photo9_2
, S.extraData.value('(/row/@Photo9_3)[1]','int') Photo9_3

, S.extraData.value('(/row/@Photo10_1)[1]','int') Photo10_1
, S.extraData.value('(/row/@Photo10_2)[1]','int') Photo10_2
, S.extraData.value('(/row/@Photo10_3)[1]','int') Photo10_3

, S.extraData.value('(/row/@Photo11_1)[1]','int') Photo11_1
, S.extraData.value('(/row/@Photo11_2)[1]','int') Photo11_2
, S.extraData.value('(/row/@Photo11_3)[1]','int') Photo11_3

, S.extraData.value('(/row/@Photo12_1)[1]','int') Photo12_1
, S.extraData.value('(/row/@Photo12_2)[1]','int') Photo12_2
, S.extraData.value('(/row/@Photo12_3)[1]','int') Photo12_3

, S.extraData.value('(/row/@Photo13_1)[1]','int') Photo13_1
, S.extraData.value('(/row/@Photo13_2)[1]','int') Photo13_2
, S.extraData.value('(/row/@Photo13_3)[1]','int') Photo13_3

, S.extraData.value('(/row/@Photo14_1)[1]','int') Photo14_1
, S.extraData.value('(/row/@Photo14_2)[1]','int') Photo14_2
, S.extraData.value('(/row/@Photo14_3)[1]','int') Photo14_3

FROM pInspectionRead.SchoolInspections S
WHERE S.InspTypeCode = 'ENVER'
GO

    Here the M0, F0, C0 etc fields correspond to the custom field names created in the Kobo designer. extraData in this view represents the Xml blob; the value function extract the fields by name. The second part of the value function specifies the data type of the extracted value – use int for whole numbers, nvarchar(50) for text, float (or decima) for numbers that may contain fractions. These same names are used for the resulting columns in the output, as shown here:

    This data could be used to source a Jasper report, a pivot table, or other reporting output. Further the stored procedure pinspectionRead.ReadInspection <inspectionID> will return the data for a single inspection – using the appropriate query for the inspection type (if an override exists).

    This could be built upon in future development to allow custom user interface by inspection type within Pacific EMIS.

  4. Log in to comment
Assignee
Type
enhancement
Priority
major
Status
resolved
Votes
0
Watchers
1
Jira: the preferred issue tracker for Bitbucket. Join the team!