Navigate MIS Import Tables: Column Schema Reference

This reference describes the four MIS import tables Navigate provides, with the full column schema for each. Use these tables to bulk add and maintain learners, activities, employers and student groups.

tblActivities

Allows colleges to bulk add activities to Navigate.

• LearnerUserName — varchar(150), required. The learner's username as stored in Navigate (tblUsers); must be valid or the row will fail.
• ActivitySubType — varchar(255), required. Must match a valid sub type on your site or the row will fail.
• ActivityName — varchar(255), required. The activity title/name.
• ActivityDescription — varchar, unlimited, optional. The activity details/description.
• EmployerActivity — bit, required. Specifies the activity type: TRUE for employer activity, otherwise enrichment.
• Location — varchar(100), optional. Name of the location where the activity took place.
• TownCity — varchar(50), optional. Town or city where the activity took place.
• ActivityDate — date, required. The date of the activity.
• HoursSpent — int, optional. Number of hours spent on the activity.
• DateAdded — datetime, optional. Defaults to current date and time.
• DateUpdated — datetime, optional. Typically set when updating an existing record.
• DateProcessed — datetime, optional. Must be NULL for records to be picked up.
• DateFailed — datetime, optional. Set by the import routine if the row fails; must be NULL to reprocess.
• ErrorMessage — varchar, unlimited, optional. Set by the import routine if the row fails.
• ImportID — int, optional. Identity column, automatically generated.
• ActivityID — uniqueidentifier, optional. The unique identifier for the activity record.
• DeleteActivity — bit, optional. Set to 1 (TRUE) to logically delete the activity record.

tblEmployers

Allows colleges to bulk add employers to Navigate.

• EmployerName — varchar(255), required.
• AddressLine1-3 — varchar(255), optional.
• TownOrCity — varchar(100), optional.
• County — varchar(100), optional.
• Postcode — varchar(20), optional.
• Telephone — varchar(50), optional.
• EmailAddress — varchar(255), optional.
• Compliant — bit, required. 0 = FALSE, 1 = TRUE.
• ComplianceExpiryDate / ComplianceApprovalDate — date, optional.
• HealthAndSafetyExpiryDate / HealthAndSafetyApprovalDate — date, optional.
• HealthAndSafetyNotes — varchar(MAX), optional.
• NavigateEmployerID — uniqueidentifier, optional. The Navigate EmployerID to update if you have it; otherwise we match on EmployerName and Postcode and write the EmployerID back once processed.
• InternalEmployerReference — varchar(50), optional. Their internal employer reference.
• ContactTitle — varchar(50), optional.
• ContactFirstName / ContactSurname — varchar(100), optional.
• ContactJobTitle — varchar(100), optional.
• ContactEmail — varchar(255), optional.
• ContactTelephone — varchar(50), optional.
• Active — bit, required. 0 = FALSE, 1 = TRUE.
• DateAdded — datetime, optional. Auto-populated on insert.
• DateUpdated — datetime, optional. Typically set to GETDATE() when the record is updated.
• DateProcessed — datetime, optional. Must be NULL for the record to be processed.
• ImportID — int, optional. Auto-populated identity column.
• NavigateEmployerContactID — uniqueidentifier, optional. The Navigate Employer Contact ID.
• DateFailed — datetime, optional. Set by the import routine if the row fails.
• ErrorMessage — varchar, unlimited, optional. Set by the import routine if the row fails.
• InternalEmployerContactReference — varchar(50), optional. An internal reference for the employer contact.

tblStudentGroups

Allows colleges to create student groups. Useful when you have students that need to be attached to multiple groups.

• StudentGroupName — varchar(255), required. This data must not be edited; editing creates a duplicate Student Group that can't be removed using SQL.
• GroupOwnerUsername — varchar(255), required. Ensure this aligns with the username format in your system.
• StudentUsername — varchar, required. Students must be live on the Navigate platform or an error will occur.
• AddOrRemove — bit, required. Takes values A (Add) or R (Remove).
• DateAdded — datetime, optional. Auto-populated on insert with the current timestamp.
• DateUpdated — datetime, optional. Auto-updated on record modification.
• DateProcessed — datetime, optional. Must be NULL for the record to be processed.
• Outcome — varchar, optional. Any errors will be reported here.
• ImportID — int, optional. Auto-populated identity column.
• DeleteStudentGroup — bit, required. Set to 1 (TRUE) to logically delete the entire student group record.

tblUsers

Lists every learner that has a Navigate account, including their attributes 1-6. This is where MIS teams add or remove learners and edit existing learner records to be updated on Navigate. Example attributes include department, course, level, gender, ethnicity and SEND.

• StudentNumber — varchar(225), required. Unique student identifier used across Navigate and imports.
• StudentFirstName — varchar(225), required. Student's given/first name.
• StudentSurname — varchar(225), required. Student's family/last name.
• StudentEmail — varchar(225), required. College email address.
• StudentUsername — varchar(225), required. Must match the email address; used for SSO.
• TutorFirstName — varchar(225). Primary tutor's first name. Cannot be NULL.
• TutorSurname — varchar(225). Primary tutor's last name. Cannot be NULL.
• TutorEmail — varchar(225). Primary tutor's email address. Cannot be NULL.
• Attribute1-6 — varchar(225), optional. Custom attribute fields (site-defined).
• Attribute1-6Code — varchar(225), optional. Code corresponding to each attribute.
• ExternalID — varchar(225), optional. External reference to link a record back to your system.
• DateAdded — datetime, optional. Defaults to the current date/time when the record is created.
• DateUpdated — datetime, optional. Updated when the record is modified.
• DateProcessed — datetime, optional. Set to NULL to be processed; populated once the record has been picked up.
• Active — bit, required. 1 = Active, 0 = Inactive.
• ImportID — int, optional. Auto-populated identity column on import.
• EHCPStatus — varchar(225), optional. Indicates if the student has an Education, Health & Care Plan. Must be ACTIVE, CLOSED, or left as NULL (no action).
• DateFailed — datetime, optional. Populated if an import attempt failed.
• ErrorMessage — varchar, unlimited, optional. Populated if the import fails, containing error details.

Source document attached below.