© 2018 Capita Business Services Ltd. All rights reserved.

Capita Education Software Solutions is a trading name of Capita Business Services Ltd. Our Registered office is 30 Berners Street, London, W1T 3LR and our registered number is 02299747. Further information about Capita plc can be found in our legal statement.

Local API - Multi School Training Data

Introduction

Multi school training data is not offered by ESS. It may however be possible for TIs to modify training data (at their own risk) to enable multi school testing.

What needs to be updated?

Key IDs apply to SIMS.SIMS_Person and Base Groups

 

Table

Field

Decription

1

SIMS.SIMS_Person

External_ID

The unique identifier for a person

2

SIMS.SIMS_BASEGROUP

External_ID

The unique identifier for a group

 

Recommendation

We would recommend perhaps the use of colours to identify the clones.

School

Boys Forename

Girls Forename

Red Abbey

Rory

Scarlet

Blue Abbey

Roman (Chelsea FC owner)

Jade

Yellow Abbey

Jaun

Lemon

 In this way, you can clearly see which school the person came from and yet it still looks like a real(ish) school.

The following SQL will set Red Abbey but the unique IDs will still need to be fixed up.

update sims.sims_person set forename = 'Rory' where gender = 'M'
update sims.sims_person set forename = 'Scarlett' where gender = 'F'
UPDATE SIMS.sims_school SET school_name = 'Red Abbey' WHERE status = 'S'

Fixing up the GUIDs

In essence what is needed is a lookup table.

Green Abbey

Red Abbey

Blue Abbey

Pink Abbey

Guid 1A

Guid 1B

Guid 1C

Guid 1D

Guid 2A

Guid 2B

Guid 2C

Guid 2D

 

 

 

 

Store this is some form of suitable way… Possibly in another database.  Possibly an XML document.

One possible way is to insert a column say old_externalID and copy the Green Abbey GUID in to it for each of the tables identified.

You then need to identify all of the new GUIDs that are not in your lookup add them to the lookup and assign new guids for each clone.

Then simply update the proper GUID to the lookup value.

Repeat for the other tables.

 

NB: It almost doesn’t matter if this is slow!  It will be run once for each clone 3 times a year.