31:I[74874,["/_next/static/immutable/chunks/3w2-tt8ecm9mz.js","/_next/static/immutable/chunks/2i9bgs6t6hfua.js","/_next/static/immutable/chunks/0z-q5l1xa28vs.js","/_next/static/immutable/chunks/0kwz_u4_tz_5s.js","/_next/static/immutable/chunks/41l3031ny9poo.js"],"PdfViewer"] 33:I[51060,["/_next/static/immutable/chunks/3w2-tt8ecm9mz.js","/_next/static/immutable/chunks/2i9bgs6t6hfua.js","/_next/static/immutable/chunks/0z-q5l1xa28vs.js","/_next/static/immutable/chunks/0kwz_u4_tz_5s.js","/_next/static/immutable/chunks/41l3031ny9poo.js"],"Helpful"] 34:I[58681,["/_next/static/immutable/chunks/3w2-tt8ecm9mz.js","/_next/static/immutable/chunks/2i9bgs6t6hfua.js","/_next/static/immutable/chunks/0z-q5l1xa28vs.js","/_next/static/immutable/chunks/0kwz_u4_tz_5s.js","/_next/static/immutable/chunks/41l3031ny9poo.js"],"TableOfContents"] 35:I[26800,["/_next/static/immutable/chunks/3w2-tt8ecm9mz.js","/_next/static/immutable/chunks/2i9bgs6t6hfua.js","/_next/static/immutable/chunks/0z-q5l1xa28vs.js","/_next/static/immutable/chunks/0kwz_u4_tz_5s.js","/_next/static/immutable/chunks/41l3031ny9poo.js"],"TrendingSidebar"] 32:T35de,

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.

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

Log into SQL.
Right-click edf_college.tblUsers > select top 1000 rows.
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

Log into SQL.
Right-click edf_college.tblActivities > select top 1000 rows.
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

Log into SQL.
Right-click edf_college.tblEmployers > select top 1000 rows.
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

Log into SQL.
Right-click edf_college.tblStudentGroups > select top 1000 rows.
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

Right-click the table and select Copy with Headers.
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.

2e:[false,["$","div",null,{"className":"space-y-4","children":[["$","$L31",null,{"documentUrl":"/api/pdf-document?slug=connecting-your-college-mis-to-navigate-via-sql&role=staff","downloadUrl":"https://navigatehelp.attachments8.freshdesk.com/data/helpdesk/attachments/production/47437407932/original/MIS-Guide-All-You-Need-To-Know.pdf?response-content-type=application%2Fpdf&Expires=1791832856&Signature=KAGdGlLjyP~UFlkNyRoCz5-ZspCNc3z7~LDRH7YfuTIcBV-r7nmtS2fK719nJoxAzo0Cvg~YR1u1EVMnA1f86oAwHKzAeFc9cVkQiuE2HyGtgjAh7pZo0JXieWxi~3ecNYIRwxeL6yo3O9WCe5-Gcx~wBMOyhe1vD71dakcaOQY-JfrfGPu0EpcOppoYArlX259jHTKHOEktPJ-dOSkONYOMJN5LcjTgcc9DbdmH1bAlfCsxmOZu6drm5qc2n-OBpwMXQLjmaSh8ZkYSdltSIgAE6PL0OUY304Uzbo~gh0qTUGhYmqQP12ygUYD5jRWI3mS0BzGE4HNglUeXHQv3xw__&Key-Pair-Id=APKAJ7JARUX3F6RQIXLA","label":"PDF"}],"$undefined"]}],false,["$","div",null,{"className":"article-body","dangerouslySetInnerHTML":{"__html":"$32"}}]] 2f:["$","$L33",null,{"slug":"connecting-your-college-mis-to-navigate-via-sql","role":"staff"}] 30:["$","aside",null,{"className":"mt-12 lg:mt-0","children":["$","div",null,{"className":"lg:sticky lg:top-24 space-y-10","children":[["$","$L34",null,{}],["$","$L35",null,{"title":"You may also like","items":[{"id":"e249ed75-2e43-4659-8936-efcdba38d409","title":"Creating the SQL Server Agent Job for Student Export","slug":"creating-the-sql-server-agent-job-for-student-export","type":"guide","categorySlug":"mis-data","roles":["staff"],"tags":["Server_Agent_Job_Creation"],"media":"https://navigatehelp.freshdesk.com/support/solutions/articles/47001301353","thumbnail":"/category-artwork/2026-09-30/staff-mis-data.png","summary":"A SQL Server Agent script that creates a scheduled job to export student data to Navigate every night at midnight. The job runs the update and insert stored procedures in sequence. What the job does The script creates a SQL Server Agent job","videoGuide":false,"theme":"Students","popularity":0,"updatedAt":"2026-06-12T13:07:23Z"},{"id":"1cf98013-26e6-4fb0-93cb-5f6905478897","title":"MIS Guide: Connecting Your College MIS to Navigate via SQL","slug":"mis-guide-connecting-your-college-mis-to-navigate-via-sql","type":"pdf","categorySlug":"mis-data","roles":["staff"],"tags":["T Levels"],"media":"https://navigatehelp.freshdesk.com/support/solutions/articles/47001301367","thumbnail":"/category-artwork/2026-09-30/staff-mis-data.png","summary":"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, en","videoGuide":false,"theme":"SQL & integrations","popularity":0,"updatedAt":"2026-08-11T14:36:56Z"},{"id":"4e079ade-6fe1-4952-917a-a7a85c034f47","title":"Sample SQL Script for EBS","slug":"sample-sql-script-for-ebs","type":"form","categorySlug":"mis-data","roles":["staff"],"tags":["Sample SQL Script for EBS"],"media":"https://navigatehelp.freshdesk.com/support/solutions/articles/47001301414","thumbnail":"https://static.wixstatic.com/media/c23afe_34e50e9c8a8c4a8f9d9a20b0db9173f2~mv2.jpeg","summary":"This is a sample SQL script for colleges integrating EBS with Navigate. It shows how to build a feed of active learners and their tutors from EBS, and how a scheduled SQL job keeps the Navigate tblUsers table in sync each day. Key points Na","videoGuide":false,"theme":"SQL & integrations","displayFormat":"Script","popularity":0,"updatedAt":"2026-06-12T13:07:27Z"},{"id":"5a534349-fcc2-4de3-bc16-3cc86b6d653a","title":"Sample Linked Server Script","slug":"sample-linked-server-script","type":"form","categorySlug":"mis-data","roles":["staff"],"tags":["Sample Linked Server Script"],"media":"https://navigatehelp.freshdesk.com/support/solutions/articles/47001301368","thumbnail":"https://static.wixstatic.com/media/c23afe_a9ac0f51367a40a9b6f2ddb569c4e05a~mv2.jpeg","summary":"A sample SQL script for adding Navigate as a linked server, allowing your college MIS to connect to the Navigate external feeds database. Replace USERNAME_HERE and PASSWORD_HERE with your own connection credentials before running the script","videoGuide":false,"theme":"SQL & integrations","displayFormat":"Script","popularity":0,"updatedAt":"2026-06-12T13:07:24Z"},{"id":"6aa6403b-1cbf-47a5-b43b-50afd2b4a9b4","title":"ProSolution Student Export View (Template)","slug":"prosolution-student-export-view-template","type":"form","categorySlug":"mis-data","roles":["staff"],"tags":["StudentExport_View"],"media":"https://navigatehelp.freshdesk.com/support/solutions/articles/47001301415","thumbnail":"https://static.wixstatic.com/media/77f784_c5929cdc5fe843b29cccd604efce906a~mv2.png","summary":"This is a template Navigate view for colleges running ProSolution. It provides the basic student, tutor and attribute data required for Navigate's external database, and is intended as a starting point that you adapt to your own college's d","videoGuide":false,"theme":"Students","popularity":0,"updatedAt":"2026-06-12T13:07:27Z"}],"role":"staff"}]]}]}]