Smart Assessor Web API

Smart Integrator Bridge Tool for One-way Data Transfer 

(MIS > Smart Assessor)

 

To send data securely from an MIS System to the Smart Assessor site. The Bridge Tool is based off 4 specifically written SQL-views from within the MIS Department.

 

PART 1 - Installing the .MSI file

NOTE: Administrator access will be required for full set up of the Bridge Tool. 

Install the bridge tool on the same location as the MIS database (which holds your student/assessor records). Remote Desktop connection to the server might be necessary. 

The set-up of the Bridge Tool is very simple, with only a few steps needed to complete. During installation, it’s advised to use your local windows account (as shown below).

Once installed, the files required within the application are located at the below pathway on your PC: 

C:\Program Files (x86)\Smart Assessor\Client Integrator

This will also install a service on your PC called “Smart Assessor Client Integrator”, please leave this turned off whilst setting up the bridge tool.

 

PART 2 - The configuration File

Within the above pathway, the Configuration file will need to be set up so the Bridge Tool can be linked to your Database. There are numerous sections within the CONFIG file that need to be set up. 

 

Interval <!-- Scheduling Application Keys -->

This is the setting configured to state how often data will run from your MIS system. The key Scheduler.IntervalSeconds will need to be amended to state how often you would like data to run from your MIS system to your Smart Assessor site. We advise a starting interval value of 7200 seconds (two hours). 

 

API Details (<!-- API Access Application Keys -->)

Smart Assessor will provide you with the individual site API key to be add to the CONFIG file. If you do not have these details, please contact your Smart Assessor Client Account Manager. The format of this are as follows:  

Usernameapi.smartassessor@[clientdomainname]

Password: Randomly Generated 32-bit GUID

Client Key: Randomly Generated 32-bit GUID

This information needs to be entered into the CONFIG File as below: 

<add key="Api.Username" value="{SETTING VALUE REQUIRED}"/>

<add key="Api.Password" value="{SETTING VALUE REQUIRED}"/>

<add key="Api.ClientKey" value="{SETTING VALUE REQUIRED}"/>

Replace the “SETTING VALUE REQUIRED” with the information. 

 

Mapping Configuration Location (<!-- Mapping Application Keys -->)

The location of the Mapping files will need to be added to the CONFIG file within the key Mapping.ConfigurationsLocation. The mapping files will be covered later in this guide. The location for these files are as follows:

C:\Program Files (x86)\Smart Assessor\Client Integrator\Content\MappingConfigurations\Current

 

 

 

Connection to your Database <connectionStrings>

Configuration is possible to MSSQL or Oracle Database programmes. Use whichever string is relevant for you. Below is the connection string to a MSSQL database:

connectionString="Data Source={SETTING VALUE REQUIRED};Initial Catalog={SETTING VALUE REQUIRED};Persist Security Info=True;User ID={SETTING VALUE REQUIRED};Password={SETTING VALUE REQUIRED};Pooling=True;Max Pool Size=500;"

 

The following information is required in the connection string so the Bridge Tool can connect to your database: 

Data Source: The name of the SQL server

Initial Catalog: The name of the Database

UserID and Password: Your login details to access SQL server

 

Other Useful Configurations

Other configurations that need to be configured are located in the Integrator Application Keys section of the CONFIG file. A key exists for the below information:

Learner.UpdateEmail: Can be set to true or false depending on whether you would like email addresses updating from your MIS system. 

Learner.UpdateMobile: Can be set to true or false depending on whether you would like Learners’ mobile numbers updating from your MIS system.

Assessor.CreateEmailonCreation: This will automatically generate an email to new assessors created via the Bridge Tool, with their login details to access Smart Assessor. 

Learner.CreateEmailOnCreation: This will automatically generate an email to new learners created via the Bridge Tool, with their login details to access Smart Assessor. 

 

 

 

 

 

 

 

 

 

PART 3 – The Mapping Files

Four mapping files are needed to map your specifically written SQL views into the Bridge tool. These mapping files link the fields in your views to the fields within Smart Assessor. Each file contains the data you can map in to send over to Smart Assessor from your MIS system. 

Before looking at the Mapping Files, if not known already:

Four views will need to be created by your MIS team, the views that are needed surround information about the following: 

Assessors

Employers

Learners

Qualifications attached to Learners (Enrolment)

All Mapping Files that the Bridge Tool caters for are located in the below pathway: 

C:\Program Files (x86)\Smart Assessor\Client Integrator\Content\MappingConfigurations\Master

There are FOUR files we will need to copy over; these are named below:

MasterLearnerDatabaseMapping

MasterEnrollmentDatabaseMapping

MasterEmployerDatabaseMapping

MasterContactsDatabaseMapping

MasterAssessorDatabaseMapping

Please copy these files into the below pathway: 

C:\Program Files (x86)\Smart Assessor\Client Integrator\Content\MappingConfigurations\Current

 

Structure of the Mapping Files

Each mapping file should be opened as Administrator with Notepad. At the top of each mapping file, various inputs are shown. See below example: 

<mapping mapping-type="Assessor" input-to-SA="false" output-from-SA="false" enabled="false">

Whilst setting up the files, leave “input-to-SA” as False, and make sure that the “enabled” value is also false, so the mapping files so not attempt to run. 

 

 

The last item is the database connection string. The string is the same in each mapping file and is shown below: 

<database type="MSSQL" connection-string-name="Client.MSSQLDatabase" get-query="SELECT * FROM Table" input-stored-procedure=""/>

Enter a SELECT statement for your view into the query value within this string of text. This will tell the Bridge Tool which view is being used. 

 

MasterAssessorDatabaseMapping File

The assessor Mapping file contains information relating to Assessor information, follow the above steps to link your Assessor view to the Mapping File. The following information and their data types are listed below: 

Field Name

Data Type

Mandatory?

Example from Database

Name (Assessors Username)

String

Yes

JohnSmith123

Email

String

Yes

John@gmail.com

FirstName

String

Yes

John

LastName

String

Yes

Smith

Phone

String

No

01478497584

Mobile

String

No

07457841396

MISAssessorID

String

Yes

JSMITH12

 

To map the fields into the Mapping file, for each field use the source-column-index. The number conventions start from 0. So, for example, if FirstName was the first item selected in your View, the source-column-index would be 0, and so on.

MasterEmployerDatabaseMapping File

The employer mapping file is for imported Employer records into Smart Assessor so these can link to learners. The following information and their data types are listed below:

Field Name

Data Type

Mandatory?

Example from Database

MISEmployerID

String

Yes

JSAUTO1

Name

String

Yes

John Smith Automobiles

Phone

String

No

01478497584

Website

String

No

www.jsauto.co.uk

EDRS Number

String

No

457848757

Address1

String

Yes

JSAUT01-01

Address2

String

Yes

Paul Street Site

Address3

String

No

Paul Street

Address4

String

No

 

PostCode

String

No

 

Health and Safety Date

String

No

 

Health and Safety Renewal Date

String

No

LE1 3WE

Liability Insurance Renewal Date

String

No

Any standard Database Format Date can be used. Example: 2017-05-25

Coordinator Name

String

No

Any standard Database Format Date can be used. Example: 2017-05-25

Coordinator Email

String

No

Any standard Database Format Date can be used. Example: 2017-05-25

MISEmployerSiteId

String

No

JSAUTO1-1

EmployerSiteEDRSNumber

String

No

456165461

EmployerSiteBusinessDepartment

String

No

Accounting

EmployerSiteBusinessLocation

String

No

 

EmployerSiteBranchCode

String

No

 

EmployerSiteWebsite

String

No

www.business.co.uk

EmployerSiteAddressLine1

String

No

 

EmployerSiteAddressLine2

String

No

 

EmployerSiteAddressCity

String

No

Leicester

EmployerSiteAddressCounty

String

No

Leicestershire

EmployerSiteAddressPostCode

String

No

LE10 2DS

EmployerSiteAddressTelephone

String

No

 

EmployerSiteHealthAndSafetyAssessmentDate

String

No

 

EmployerSiteHealthAndSafetyAssessmentRenewalDate

String

No

 

EmployerSiteLiabilityInsuranceRenewalDate

String

No

 

 

To map the fields into the Mapping file, for each field use the source-column-index. The number conventions start from 0. So, for example, if MISEmployerID was the first item selected in your View, the source-column-index would be 0, and so on. 

To include multiple sites then simply provide additional rows in your Employer View, only the first instance will create a single employer, all subsequent rows will create or amend site data.

An initial Site will always be created from the first record.

 

MasterContactsDatabaseMapping File

The contact mapping file is for imported employer contact records into Smart Assessor so these can link to learners. The following information and their data types are listed below:

Field Name

Data Type

Mandatory?

Example from Database

MISEmployerID

String

Yes

JSAUT01-01

EmployerSiteContactTypeName

String

Yes

See below codes in the Types section

EmployerManagerUsername

String

Yes

John.Smith123

MISContactId

String

Yes

JohnSmith1454

FirstName

String

Yes

John

LastName

String

Yes

Smith

Telephone

String

No

01214545645

Mobile

String

No

07807455545

Email

String

Yes

John.smith@smartapprentices.com

JobTitle

String

No

Head of IT Operations

MISLearnerIDs

String

No

45154214 | 78454214 | 01223214

 

 

 

MISLearnerIDs

The MISLearnerIDs field within the EmployerContact Mapping file stores all the MISLearnerIDs (that are held in the learner file) that need to be assigned to the Employer Contact account record. These are separated using a Pipe Delimiter (|). 

Types

NOTE: To allow the system to create Employer Manager accounts, you must ensure the “Employer Manager” type is specified in your EmployerType field. 

  • Employer Overview
  • Employer Manager
  • Employer

Other types can be created by using the User Defined list that is available by going into to “Admin” > “User Defined Lists” > Employer Contacts Types

 

 

MasterLearnerDatabaseMapping File

This mapping file holds learner data. The following information that can be imported and their data types are listed below: 

Field Name

Data Type

Mandatory?

Example from Database

MISLearnerID

String

Yes

HILIND1 (Any unique student ref. number can be used).

MISEmployerID

String

Yes

The same EmployerID linked to Employer view. 

FirstName

String

Yes

Hilary

LastName

String

Yes

Indigo

StudentID

String

No

124578985

LoginID

String

Yes

HilaryIndigo1

National Insurance Number

String

No

JB 47 78 75 D

ULN

String

No

12345678

Gender

String

No

M/F

Address1

String

No

 

Address2

String

No

 

Address3

String

No

 

Address4

String

No

 

PostCode

String

No

LE13EW

Mobile

String

No

07487933114

Email

String

Yes

Hilary@Indigo.com

Date of Birth

String

No

Any standard Database Format Date can be used. Example: 2017-05-25

Learning Disabilities

String

No

98

Learning Difficulties

String

No

98

Ethnicity

String

No

White British

Partner

String

No

 

Funding Body Name

String

Yes

SFA

Learner Type

String

Yes

Apprentice

 

To map the fields into the Mapping file, for each field use the source-column-index. The number conventions start from 0. So, for example, if MISLearnerID was the first item selected in your View, the source-column-index would be 0, and so on.

MasterEnrollmentDatabaseMapping File

This mapping file stores information about the learner’s qualifications that they’re on. This file will transfer the qualifications against the learners so the correct learner’s qualifications show on Smart Assessor. The information that can be imported and the data types are listed below:

Field Name

Data Type

Mandatory?

Example from Database

MISLearnerID

String

Yes

The learners MISLearnerID from the Learner View

AimNumber (LADCode)

String

Yes

6011451X

MISAssessorID

String

No

The Assessors MISAssessorID from the Assessor view

MISSecondAssessorID

String

No

The Assessors MISAssessorID from the Assessor view

Actual End Date

String

No

Any standard Database Format Date can be used. Example: 2017-05-25

Course Start Date

String

Yes

Any standard Database Format Date can be used. Example: 2017-05-25

Course End Date

String

Yes

Any standard Database Format Date can be used. Example: 2017-05-25

MISIQAIDs

String

No

Separate IQA’s via pipe delimiter (|) to add multiple IQAs to enrolment

Exclude from Overall Progress

Boolean

No

0/1

Course Status

String

Yes

IT

Break in Learning Date (Date of Status Change)

String

No

Any standard Database Format Date can be used. Example: 2017-05-25

Extended Course End

String

No

Any standard Database Format Date can be used. Example: 2017-05-25

Registration Date

String

No

Any standard Database Format Date can be used. Example: 2017-05-25

Registration Number

String

No

947845784

Episode Code

String

No

1454124

Episode Start Date

String

Np

Any standard Database Format Date can be used. Example: 2017-05-25

Episode End Date

String

No

Any standard Database Format Date can be used. Example: 2017-05-25

 

 

 

 

Course Status:

The course status will need to be brought view in the Enrolment view. The codes that are sent through will need to match the below values:

Course Status

Value Required

Awaiting Induction

AI

In Training

IT

IQA Approved

IA

Completed

CM

Certificated

CR

Training Suspended

TS

Transferred

TR

Early Leaver

EL

Exempt

EX

 

To map the fields into the Mapping file, for each field use the source-column-index. The number conventions start from 0. So, for example, if MISLearnerID was the first item selected in your View, the source-column-index would be 0, and so on.

 

 

PART 4 – Starting to Use the Bridge Tool

Now the mapping files are completed, please inform your Smart Assessor account manager you are ready to test the data files. They will be assign a member of the Smart Assessor support team to assist you with further testing.

Once all the mapping files have been correctly mapped to your views, we shall test the files 1-by-1 to make sure each file is working correctly. Please test the files in the following order 1-by-1: 

  1. Employer
  2. Assessor
  3. Learner
  4. Learner Course Information (Enrolment)

 

How to test a file: 

Open the mapping file and set the “input-to-SA” to True and Enabled to “True”. This will enable the mapping file to begin running. 

Next, start the Smart Assessor Client Integrator Service, this will begin running the data file into your Smart Assessor site. 

 

Checking the error logs: 

The error logs for the Bridge Tool are located at the below pathway on your C:\ drive:  

C:\ProgramData\Smart Assessor

The error logs will inform you of any errors in data going from your MIS system to your Smart Assessor site.