Smart Assessor Automated Data Integration

Smart Assessor Automated Data Integration

What it does:

This is a tool Smart Assessor has developed to allow our clients to send to us Excel data via FTP, which we will then take and import into our system.

How it works:

The Customer exports 3 lots of data from there MIS to Excel, one containing learner information, one containing employer information and then one containing assessor information they will then set up a scheduler that automatically transfers this data to our server as often as they choose, e.g. Hourly, Daily or Weekly.

Smart Assessor then receives this data and first of all imports the employer data, checking for any new employers and creating these and updating any existing employer data as well.

We will then Import the assessor data, checking for any new assessors and creating these or updating any existing data.

We will then import the learner data, checking again for any new learners and creating them and updating any existing ones, we will also attach qualifications to the learners based on the LAD code. If we find any data where we do not have internal ID’s stored for but there are similarities e.g. company EDRS number and Postcode match we will send these to a storage area in Smart Assessor where a person will have to match the data up via an interface in Smart Assessor.

Fields included in the Import:

Mandatory elements highlighted

MISLearnerID String – 32 char

UserID String – 128 char

Forename String – 50 char

Surname String – 50 char

ULN String – 10 char

StudentID String – 20 char

DOB Date (DD/MM/YYYY)

Sex String – 6 char

NINumber String – 9 Char

Domicile String – 2 Char

Email String – 128 Char

TelNum String – 50 char

Mobile String – 50 char

LDisability LDifficulty

HomeAddressLine1 String – 50 char

HomeAddressLocality String – 50 char

HomeAddressTown String – 50 char

HomeAddressCounty String – 50 char

HomeAddressPostCode String – 50 char

MISEmployerID String – 32 char

InitialAssessmentNumeracy string - 10 Char InitialAssessmentLiteracy string - 10 Char

InitialAssessmentICT string - 10 Char

Partner string - 95 Char

Courses Element grouping Courses together

Please Note: While Email and Mobile is imported upon the first import of a learner, they are never updated by the data import.

CourseEndDate Date

InternalCourseID String - 20 Char

MISAssessorID String – 32 char

MISAssessorID2 String – 32 Char

Status

Pass the following Abbreviations:

AI – Awaiting Inductions IT – In Training CM – Completed TS – Training Suspended EL – Early Leaver TR – Transferred CR – Certified

PathwayCode string - 250 Char

Please note: MISAssessorID – although this isn’t a mandatory field, if you do not pass this the Assessors will not be attached.

Employer elements:

Mandatory Elements Highlighted

CompanyName String – 500 Char

BusinessDepartment String – 400 Char BusinessLocation String – 400 Char

BranchCode String – 400 Char

EDRS String – 20 Char

MISEmployerID String – 32 char AddressLine1 String – 200 Char AddressLine2 String – 200 Char

AddressTown String – 200 Char

AddressCounty String – 200 Char AddressPostCode String – 200 Char

Telephone String – 20 Char

KeyContactName String – 50 Char KeyContactEmail String – 300 Char

Assessor Elements:

Mandatory Elements Highlighted

AssessorForename String – 50 Char AssessorSurname String – 50 Char AssessorUsername String – 50 Char AssessorPassword String – 50 Char

Region String – 400 Char

MISAssessorID String – 32 char EmailAddress String – 256 Char

PhoneNum String – 50 Char

MobNum String – 50 Char

The Names of the Excel files must be as follows:

Learner Excel:

Learner.xls OR Learner.xlsx

Employer Excel:

Employer.xls OR Employer.xlsx

Assessor Excel:

Assessor.xls OR Assessor.xlsx

FTP Details:

Each client will get their own FTP Login Details and their data will be stored in its own folder

away from all other clients’ data and they will not have access to any other folders on the

system.

Your username and password will be sent over separately, please keep these secure and

safe.

Further details on the import process:

Create/Update employer:

We will receive a full list of employers and will look to see if we have already got the employers created in the system based on the MISEmployerID, see flow diagram below:

Store in Employer match up table for later use

Create new employer Update Record in SA

Does EDRS number or Postcode Exist in SA?

Yes No Employer XML table received No Does MISEmployerID Exist in SA? Yes

Create/Update learner:

We will look to see if we have already got the learner in SA based on MISLearnerID, see flow diagram below. Yes No Update Record in SA Store in Learner match up table

Create new Learner Does MISLearnerID exist in SA? Yes No Does ULN or Student ID and MISEmployerID exist? Learner XML table received

The learner table will also contain the MISEmployerID this will be used to link the learner to the employer and will have to assign the course and assessor to the learner as well.

The process for this will be:

Check MISEmployerID against the employer table

Attach employer to learner Don’t create learner and

record in error log

Does MISEmployerID

exist?

No

For attaching courses:

For attaching assessors:

Check Internal Course Codes

Does it match to our system?

Attach Course to learner Yes

No

Check Lad Codes

Does it match to our system? Yes Don’t assign course and record in error log report No Check MISAssessorID Yes No Do they match our system?

Create/Update Assessor

At any point during the import – if the XML contains any duplicate learners then that learner is not imported and sent to the error log.

Assessor XML table received Update Record in SA Store in assessor match up table Create new Assessor Does MISAssessorID exist in SA? Yes No Does Forename and Surname exist in SA? No Yes