Connecting Your College MIS to Navigate via SQL

This guide is for MIS and Data teams. It covers connecting your college MIS to SQL, enabling and validating the import table feeds, using the export tables for reporting, and the basic SQL commands you will need.

Contents

• Connecting to SQL and whitelisting your college IP
• Enabling import table feeds: TblUsers, TblActivities, TblEmployers, TblStudentGroups
• Reporting with SQL export tables and the Admin App
• Basic SQL commands and exporting data to Excel

Connecting college MIS to SQL

Your connection details:

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

TblUsers rules for importing student and staff data

TblUsers is where you insert student and staff data. Data is processed at 5am daily. Linked in the source document you will find the TblUsers schema and sample SQL scripts for EBS, Pro and a linked server. With the connection, you also gain access to a set of export data tables covering employer engagement, enrichment, careers and placement hours, which you can use to build management reports or populate the learner's ILR.

Adding students:

• Set active = 1.
• Set dateupdated = current date.
• Set dateprocessed = NULL (this flags the data 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 will automatically be given staff accounts. These instructions are for adding 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, dateupdated = current date, 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 set dateprocessed = NULL to reprocess.

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

We highly recommend testing the feed with a handful of student and staff accounts first, so Navigate can pick up any errors before importing the entire 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.

Validating TblUsers in SQL

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

What to look out for. Data in fields must follow these rules:

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

Additional checks:

• Each student should appear once. To update a record, update the existing row and set dateprocessed = NULL. Do not insert a new row.
• Tutor fields must not be blank. If you have no student-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 processing, it is usually because dateprocessed is not NULL.
• If attribute data is not processing, you may have entered it in the attributexcode column instead of the attributex column, or the attributes are not yet named in the Admin App by Navigate. Naming them immediately creates filters and displays the data on the live site.

Validating TblActivities in SQL

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

Data in all fields must follow these 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 / TownCity: varchar
• ActivityDate: date (cannot be empty)
• HoursSpent: decimal
• DateAdded: datetime, current date (cannot be empty)
• DateUpdated: datetime
• DateProcessed: set to NULL; updates once processed

If the import fails: a failure message appears in the DateFailed and ErrorMessage columns. Fix the errors and set dateprocessed and datefailed to NULL to reprocess. You can clear out TblActivities at any time, as we do not process deletes; this will not impact 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), where the standard Attribute1-6 columns will not work.

Validating TblEmployers in SQL

1. Log into SQL.
2. Right-click edf_college.tblEmployers > select top 1000 rows.
3. Remove TOP (1000) and click Execute.

Data in all fields must follow these rules:

• EmployerName: varchar (cannot be empty)
• AddressLine1-3, TownOrCity, County, Postcode, Telephone, 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 (link back to internal systems)
• ContactTitle, ContactFirstName, ContactSurname, ContactEmail, ContactJobTitle, ContactTelephone: varchar / email
• Active: bit 0/1 (cannot be empty)
• DateAdded: datetime (cannot be empty)
• DateUpdated: datetime
• DateProcessed: set to NULL; updates once processed

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

Validating TblStudentGroups in SQL

1. Log into SQL.
2. Right-click edf_college.tblStudentGroups > select top 1000 rows.
3. Remove TOP (1000) and click Execute.

Data in all fields must follow these 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, indicating success or failure
• ImportID: populated by us once processed

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 group they are joining.
• Do not edit StudentGroupName data — 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 the figures from a column:

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

To execute commands, click Execute at the top left of your 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 word for word; SQL will bring up matching columns. Then type, in inverted commas, the value to search for. Add more than one WHERE clause using AND, and exclude data using AND NOT. For example, count placements where the student is active, the end date is on or after 1 September 2023, and the placement is not in the awaiting-details status. Each college will have different inclusion/exclusion requirements.

4. Group data:

SELECT count(*), column_to_group_by FROM [edf_ucg].[Export_WorkPlacementsDetailed] GROUP BY column_to_group_by

This counts placements grouped by the chosen column, for example by campus (which on the site may be attribute1). You can incorporate WHERE clauses, and group by other attributes or by placement coordinator simply by replacing the column name.

5. Inner join two tables:

Use an inner join when you want information from two tables in one. A common case is merging TblEmployers and TblEmployerContacts so you can view all employers alongside the contacts listed against each record:

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;

Columns prefixed emp. come from Export_Employers and columns prefixed con. come from Export_EmployerContacts. 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 which employer they belong to via the same EmployerID. An organisation can appear more than once in the result if it has more than one contact; 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

You also have access to a set of SQL export tables, which MIS can use to create your own SLT dashboards and reports. The full list of export tables your college has access to is included in the attached document.

Source document attached below.