ProSolution Student Update and Deactivate Script Templates

Template scripts for keeping ProSolution student data in sync with Navigate. Includes an Update script that pushes changed records through, and a Deactivate script that switches off accounts no longer present in the export.

What these scripts do

These are template scripts ([ProSolutionReports].[dbo].[Navigate_StudentUpdate]) which take data from the NavigateStudentsExport view. There are two scripts:

• Update script: Compares student data within ProSolution with Navigate by converting specific columns into a hash value. If the hash values are different, this means a change has occurred in ProSolution, and the student is passed through as an update to the Navigate database, resetting the Date Processed field.
• Deactivate script: If the student does not show up in the NavigateStudentsExport, this script deactivates their account on Navigate, should they be found in the Navigate database.

Version: 1.0  |  Created by: Navigate  |  Created date: 22/03/25 (Initial Development).

Important notes

• Replace [Insert your specific college schema here] with your specific college schema name.
• The hash comparison uses MD5: if any of the listed columns change, the hash value differs and the update is triggered.
• DateProcessed is reset to NULL on both update and deactivate — this is important.
• To deactivate an account, Active is set to 0.

Update script

UPDATE DEST --Destination
SET
	[StudentNumber] =  src.[StudentNumber]
	,[StudentFirstName] = src.[StudentFirstName]
	,[StudentSurname]	= src.[StudentSurname]
	,[StudentEmail]		= src.[StudentEmail]
	,[StudentUsername]	= src.[StudentUsername]
	,[TutorFirstName]	= src.[TutorFirstName]
	,[TutorSurname]		= src.[TutorSurname]
	,[TutorEmail]		= src.[TutorEmail]
	,[Attribute1]		= src.[Attribute1]
	,[Attribute1Code]	= src.[Attribute1Code]
	,[Attribute2]		= src.[Attribute2]
	,[Attribute2Code]	= src.[Attribute2Code]
	,[Attribute3]		= src.[Attribute3]
	,[Attribute3Code]	= src.[Attribute3Code]
	,[Attribute4]		= src.[Attribute4]
	,[Attribute4Code]	= src.[Attribute4Code]
	,[Attribute5]		= src.[Attribute5]
	,[Attribute5Code]	= src.[Attribute5Code]
	,[Attribute6]		= src.[Attribute6]
	,[Attribute6Code]	= src.[Attribute6Code]
	,[ExternalID]		= src.[StudentNumber]
	,[DateUpdated]		= GETDATE()
	,[DateProcessed]	= NULL -- Reset the date process field. Important.
	,[Active]			= src.[Active]
	,[EHCPStatus]		= src.[EHCPStatus]
FROM [NAVIGATE].[Navigate_ExternalFeeds].[Insert your specific college schema here].[tblUsers] AS DEST --Destination
INNER JOIN (
	SELECT vSE.*
	FROM [NAVIGATE].[Navigate_ExternalFeeds].[Insert your specific college schema here].[tblUsers] as Nav --External Database
	LEFT JOIN [ProSolutionReports].[dbo].[vNavigateStudentsExport] as vSE
	ON -- Converts the below to MD5 hash as if any change to columns, the hash value is different and will trigger update.
	HashBytes('MD5', CONCAT(
				ISNULL(LTRIM(RTRIM(vSE.[StudentNumber])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[StudentFirstName])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[StudentSurname])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[StudentEmail])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[StudentUsername])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[TutorFirstName])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[TutorSurname])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[TutorEmail])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[Attribute1])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[Attribute1Code])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[Attribute1Code])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[Attribute2])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[Attribute2Code])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[Attribute3])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[Attribute3Code])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[Attribute4])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[Attribute4Code])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[Attribute5])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[Attribute5Code])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[Attribute6])),'')
				,ISNULL(LTRIM(RTRIM(vSE.[Attribute6Code])),'')
				,ISNULL(LTRIM(RTRIM(CONVERT(varchar(10), vSE.[Active]))),'')
				,ISNULL(LTRIM(RTRIM(vSE.[EHCPStatus])),'')
				)) !=
	HashBytes('MD5', CONCAT(
				ISNULL(LTRIM(RTRIM(Nav.[StudentNumber])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[StudentFirstName])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[StudentSurname])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[StudentEmail])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[StudentUsername])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[TutorFirstName])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[TutorSurname])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[TutorEmail])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[Attribute1])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[Attribute1Code])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[Attribute1Code])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[Attribute2])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[Attribute2Code])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[Attribute3])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[Attribute3Code])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[Attribute4])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[Attribute4Code])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[Attribute5])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[Attribute5Code])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[Attribute6])),'')
				,ISNULL(LTRIM(RTRIM(Nav.[Attribute6Code])),'')
				,ISNULL(LTRIM(RTRIM(CONVERT(varchar(10), Nav.[Active]))),'')
				,ISNULL(LTRIM(RTRIM(Nav.[EHCPStatus])),'')
				))
				AND vSE.[ExternalID] = Nav.[ExternalID]
				WHERE vSE.[ExternalID] IS NOT NULL -- Where student is stored in Navigate Table.
) src -- Source
ON src.[ExternalID] = DEST.[ExternalID]

Deactivate script

-- Deactivate Student Accounts
UPDATE Nav
SET
	 Nav.[DateProcessed] = null  -- Reset the date process field. Important.
	,NAV.[Active] = 0 -- Set to 0 to deactivate
FROM [NAVIGATE].[Navigate_ExternalFeeds].[Insert your specific college schema here].[tblUsers] as Nav
		LEFT JOIN [ProSolutionReports].[dbo].[vNavigateStudentsExport] as vSE
			ON vSE.[ExternalID] = Nav.[ExternalID]
WHERE
vSE.[ExternalID] IS NULL
AND NAV.[ExternalID] IS NOT NULL
AND NAV.[Active] = 1

Source document attached below.