MIS Guide: Connecting Your College MIS to Navigate via SQL

A complete guide for MIS and data teams (Pro Solution) on connecting your college MIS to Navigate via SQL, managing import tables, and building reports.

Contents

• Connecting to SQL - whitelisting college IP, connecting college MIS to SQL, enabling import table feeds
• Import tables - TblUsers, TblActivities, TblEmployers, TblStudentGroups
• Reporting - SQL export tables, export via the Admin App, basic SQL commands, exporting data from SQL to Excel

Connecting your college MIS to SQL

Your connection details:

• Server name: navigatedbserver.database.windows.net
• Database: Navigate_ExternalFeeds. If you use SQL Server Management Studio you will need to specify this database name in the connection properties tab.
• Username: XXXX-college
• Password: see Privnote. This link will self-destruct on opening.

The first table is TblUsers, where you will insert student and staff data. Data is processed at 5am daily. Alongside the connection you also receive a set of export data tables containing all Navigate data about your students' employer engagement, enrichment, careers, and placement hours. Use these to build your management reports or to populate the learner's ILR.

You are provided with: the TblUsers schema; a sample SQL script for EBS to TblUsers; a sample SQL script for Pro to TblUsers; and a sample linked server script.

TblUsers - rules for importing student and staff data

Adding students:

• Set field active = '1'.
• Set dateupdated = current date.
• Set dateprocessed = NULL (this flags the record for us to process).
• Each student should only ever have one record in TblUsers - ensure there are no duplicate rows.

Adding staff: Any staff already linked to a student in the tutor fields are automatically given staff accounts. These instructions are for staff who are not tutors or directly assigned to students (student support, careers, placement coordinators, SLT, etc.). Append their records to the same TblUsers.

• Ignore all student fields: studentnumber, studentfirstname, studentsurname, studentemail, studentusername, attributes 1-6 and attribute1-6code.
• Populate tutorfirstname and tutorsurname.
• Set active = '1'.
• Set dateupdated = current date.
• Set dateprocessed = NULL.

Editing existing student records: Edit the relevant fields in the existing record (do not add a new record). Set dateupdated = current date and dateprocessed = NULL to reprocess.

Deactivating student records: Edit active = '0' on the existing record (do not add a new record) and set dateprocessed = NULL to reprocess.

We highly recommend testing the feed with a handful of student and staff accounts first, so Navigate can catch any errors before you import the whole cohort. Let Navigate know as soon as you have added data to TblUsers - we need to enable the feed on our end before the data can be processed.

Checking TblUsers data in SQL

1. Log into SQL.
2. Right click edf_college.tblUsers and select top 1000 rows.
3. Remove 'TOP (1000)' from the query and click Execute to view all rows.

Field format rules:

• StudentNumber: varchar
• StudentFirstName: varchar
• StudentSurname: varchar
• StudentEmail: email address (cannot be empty)
• StudentUsername: email address (cannot be empty)
• TutorFirstName: varchar (cannot be empty)
• TutorLastname: varchar (cannot be empty)
• TutorEmail: email address (cannot be empty)
• Attribute1-6: where MIS specify attribute data
• Attribute1-6Code: varchar (non-compulsory; for mapping data back to internal systems, not displayed on the site)
• ExternalID: varchar (non-compulsory; for mapping back to internal systems)
• DateAdded: datetime (current date)
• DateUpdated: datetime (current date)
• DateProcessed: set to NULL (updates to datetime once we process the record)
• Active: bit (1/0)
• ImportID: identity column, read only (updates once we process the record)

Also check:

• Each student should appear only once. To update a record, edit the existing row and mark dateprocessed as NULL - do not insert a new row.
• Tutor fields must not be blank. If you have no student-to-tutor assignment yet, use a placeholder tutor (e.g. collegename@collegedomain.ac.uk, first name = placeholder, last name = tutor).
• If a student's details are not being processed, it is usually because dateprocessed is not set to NULL.
• If attribute data is not being processed, you may have put the attribute info in attributexcode instead of the attributex columns, or the attributes have not yet been named in the Admin App by Navigate. Naming them immediately creates filters and displays the attribute data on the live site.

TblActivities

1. Log into SQL.
2. Right click edf_college.tblActivities and select top 1000 rows.
3. Remove 'TOP (1000)' and click Execute to view all rows.

Field format rules:

• LearnerUserName: email address (cannot be empty)
• ActivitySubType: varchar (cannot be empty; must match an activity subtype in the college's current activity list - check the Admin App)
• ActivityName: varchar (cannot be empty)
• ActivityDescription: varchar
• EmployerActivity: bit (0/1) (cannot be empty)
• Location: varchar
• TownCity: varchar
• ActivtyDate: date (cannot be empty)
• HoursSpent: decimal
• DateAdded: datetime, current date (cannot be empty)
• DateUpdated: datetime, current date
• DateProcessed: set to NULL (updates once processed)

If the import fails: a failure message appears in the DateFailed and ErrorMessage columns. Fix the errors, then set dateprocessed and datefailed to NULL to reprocess. You may clear out TblActivities at any time, as we do not process deletes; this does not affect activities already processed for the live site.

Other import tables

The college also has access to other import tables:

• TblActivities - bulk add activities.
• TblEmployers - bulk add employer records.
• TblPlacements and TblPlacementDays - bulk create placements.
• TblStudentGroups - create bespoke student groups. Used where a student needs to be assigned to more than one dropdown option under an attribute (e.g. A level students assigned across at least 3 subjects under the 'subject' attribute), as the standard Attribute1-6 columns in TblUsers will not work.

TblEmployers

1. Log into SQL.
2. Right click edf_college.tblEmployers and select top 1000 rows.
3. Remove 'TOP (1000)' and click Execute to view all rows.

Field format rules:

• EmployerName: varchar (cannot be empty)
• AddressLine1-3: varchar
• TownOrCity: varchar
• County: varchar
• Postcode: varchar
• Telephone: varchar
• EmailAddress: varchar
• Compliant: bit (0/1) (cannot be empty)
• ComplianceExpiryDate / ComplianceApprovalDate: date
• HealthAndSafetyExpiryDate / HealthAndSafetyApprovalDate: date
• NavigateEmployerID: unique identifier (filled by us once processed)
• InternalEmployerReference: varchar (for linking back to internal systems)
• ContactTitle, ContactFirstName, ContactSurname: varchar
• ContactEmail: email address
• ContactJobTitle, ContactTelephone: varchar
• Active: bit (0/1) (cannot be empty)
• DateAdded: datetime (cannot be empty)
• DateUpdated: datetime
• DateProcessed: set to NULL (updates once processed)

Each employer should exist on the table only once; more than one record creates duplicates. If a college has more than one contact for an employer, choose one to add to the table and add the remaining contacts manually via the live site once the data is processed. If the import fails: fix the errors and set dateprocessed to NULL to reprocess.

TblStudentGroups

1. Log into SQL.
2. Right click edf_college.tblStudentGroups and select top 1000 rows.
3. Remove 'TOP (1000)' and click Execute to view all rows.

Field format rules:

• StudentGroupName: varchar (cannot be empty)
• GroupOwnerUsername: email address; staff must already exist as a user on Navigate (cannot be empty)
• StudentUsername: email address; student must already exist as a user on Navigate (cannot be empty)
• AddOrRemove: A or R - adds or removes the student from the group (cannot be empty)
• DateAdded: datetime (cannot be empty)
• DateUpdated: datetime
• DateProcessed: set to NULL (updates once processed)
• Outcome: populated by us once processed; tells MIS whether the record succeeded or failed
• ImportID: populated by us once processed

Important notes:

• All student groups created via TblStudentGroups are public for all staff to view.
• To move a student from one group to another, remove them from the group they are leaving and add a new line for the new group.
• Student StudentGroupName data must not be edited - this creates a duplicate student group record and leaves the old record live on your site.
• Remove a student from a group by changing 'A' to 'R' in the AddOrRemove column.
• Delete a student group by adding '1' in the DeleteStudentGroup column. This deletes the entire student group for all students.

Basic SQL commands

You will need to specify the college table name for each query.

1. Count rows:

• SELECT count(*) FROM [edf_college].[Export_tablename]

2. Sum figures from a column:

• SELECT sum(columnname) FROM [edf_college].[Export_tablename]

To execute commands, click Execute at the top left of the screen.

3. Count or sum where the data matches a value:

• SELECT count(*) FROM [edf_college].[Export_tablename] WHERE studentactive = '1'

Specify the column name exactly; SQL brings up matching columns. Add the value to search for in inverted commas. You can add more than one WHERE clause using 'and' between clauses, and exclude data using the 'and not' clause. Each college will have different inclusion and exclusion requirements.

4. Group data:

• SELECT count(*), column you want the data grouped by FROM [edf_ucg].[Export_WorkPlacementsDetailed] GROUP BY column you want the data grouped by

You can incorporate WHERE clauses into grouped queries. To group by other attributes or by placement coordinator, simply replace the column name (e.g. group by attribute 2, attribute 3, or placement coordinator).

The INNER JOIN command

Where the college wants information from two tables in one table, you merge columns from both. This is common for combining TblEmployers and TblEmployerContacts so you can view all employers alongside the contacts listed against each record.

Example query:

• SELECT emp.EmployerID, emp.EmployerName, emp.AddressLine1, emp.AddressLine2, emp.AddressLine3, emp.Town, emp.County, emp.Postcode, con.Title, con.FirstName, con.LastName, con.EmailAddress, con.JobTitle, con.Telephone
• FROM edf_ucg.Export_EmployerContacts AS con
• INNER JOIN edf_ucg.Export_Employers AS emp ON emp.EmployerID = con.EmployerID;

The FROM and INNER JOIN lines tell SQL that columns prefixed with con. come from Export_EmployerContacts and columns prefixed with emp. come from Export_Employers. The final line, ON emp.EmployerID = con.EmployerID, tells SQL how the data is merged: each employer record has a unique EmployerID, and each contact lists the EmployerID of the organisation it belongs to, so the contact is matched against the employer where the EmployerIDs match.

An organisation can appear more than once if it has more than one contact attached. Each row represents an employer contact against the relevant employer organisation.

Exporting data from SQL to Excel

1. Right click the table and select copy with headers.
2. Paste the data into a blank Excel sheet.

Reporting and SQL export tables

MIS has access to a full list of export tables. Use these to create your own SLT dashboards and reports.

Source document attached below.