People - Bulk Uploads

Owned by Ashley Ransoo
Last updated: 16 Feb 2022 by Yafang Deng
8 min read

Page content:

Background

This page summarises the MVP work that was done to create a bulk upload facility for People (trainees and other roles) to TIS.

This page also captures the post-MVP discussions and business needs to have a facility to efficiently and consistently be able to:

  • Update People records on TIS,

  • Add trainers, amend their approval status, start and end dates for which they are approved

  • Update roles assigned to People (Leave.Approver.NonAdministrator, SelfService.LeaveApplicant) that feeds into Accent Leave Manager

  • Update visa details on TIS by the immigration team.

https://hee-tis.atlassian.net/browse/TISNEW-3857

 

Bulk People Upload

The TIS People Upload allows you to onboard new trainees onto TIS with a limited level of updating people records where they already exist on TIS. To summarise it allows you to:

  • Creates a new or Updates an existing person record where matching criteria are met

  • Create their programme membership and add the curriculum (up to 3)

    • Where the Programme name, number, type, start and end dates matching exactly to an existing Programme membership on the Person, no duplicate Programme membership

    • Where at least 1 of the fields (Programme name, number, type, start and end dates) does not match, a new programme membership is created

  • Creates their qualification record

  • Updates existing address records - If provided on template, All 4 Lines of addresses on TIS are cleared and updated with the 3 lines of addresses on TIS.

  • Updates personal and sensitive details for the matching person or adds them for the new person record being created

  • Updates Rotation for the matching person and matching programme membership, or add Rotation to the new programme membership being created

  • Sets the Training Status to Current/Inactive based on Programme Membership dates

  • Roles are added onto the existing roles, approval tab becomes available if one of the 3 categories of roles (Educational Supervisor/Clinical Supervisor/Line Manager) is added with

  • If the person is existing, the people upload act as an update and add any new role from the spreadsheet; if one of the role is (ES/CS/LM), then trainer approval tab becomes available

  • Note: You cannot add the trainer approval details from this template, e.g. start/end date, trainer status. You'll need to use the Person Update template for this. 

 

Bulk People Upload Column Headers

Row no.

Column heading

ESR bi-directional Update/overwrite

*Subject to IG approval

Add/Update/Matching Criteria

Mandatory

Rules

Questions/Notes for discussions

Internal/Target Entity

Row no.

Column heading

ESR bi-directional Update/overwrite

*Subject to IG approval

Add/Update/Matching Criteria

Mandatory

Rules

Questions/Notes for discussions

Internal/Target Entity

1

Forenames *

No

Update

Y

 

ContactDetails

2

Surname *

No

Matching criteria/Add

Y

 

ContactDetails

3

OrielApplicantID (NEW)

No

Update

 

  • Up to 8 Digit numeric type

  • If none provided on template, ignore, no overwrite

  • If a value already exist on TIS:

    • Either flag in downloadable report?

    • Or, Overwrite with value on template?

  • If no value exists on TIS, overwrite with value provided on template

Not currently on TIS.

Person - Potentially

4

GMC Number

No

Matching criteria/Add

Y - Conditionally

  • Either a GMC number, GDC number or Public Health number must be entered

  • Matching Criteria for updates: Value must match exactly the GMC of a Current or Inactive existing Person record on TIS. Both surname and GMC number for each row must match exactly a single TIS person record

  • Matching Criteria for Updates: Where more than 1 registration numbers (GMC/GDC/PH) have been provided, all the provided values should match to a single person record on TIS.

  • Add - Add as a new Person record where GMC number does not already exist on TIS

  • Add - Accepts ‘UNKNOWN’ or ‘N/A’ as a value

 

GmcDetails

5

GDC Number

No

Matching criteria/Add

Y - Conditionally

  • Either a GMC number, GDC number or Public Health number must be entered

  • Matching Criteria for updates: Value must match exactly the GDC of a Current or Inactive existing Person record on TIS. Both surname and GMC number for each row must match exactly a single TIS person record

  • Matching Criteria for Updates: Where more than 1 registration numbers (GMC/GDC/PH) have been provided, all the provided values should match to a single person record on TIS.

  • Add - Add as a new Person record where GDC number does not already exist on TIS

  • Add - Accepts ‘UNKNOWN’ or ‘N/A’ as a value

 

GdcDetails

8

Public Health Number

No

Matching criteria/Add

Y - Conditionally

  • Either a GMC number, GDC number or Public Health number must be entered

  • Matching Criteria for updates: Value must match exactly the PH umber of a Current or Inactive existing Person record on TIS. Both surname and PH number for each row must match exactly a single TIS person record

  • Matching Criteria for Updates: Where more than 1 registration numbers (GMC/GDC/PH) have been provided, all the provided values should match to a single person record on TIS.

  • Add - Add as a new Person record where PH number does not already exist on TIS

  • Add - Accepts ‘UNKNOWN’ or ‘N/A’ as a value

 

Person

9

Role

No

Add

 

N

  • Must match to one of TIS Reference values and is CURRENT

  • Allows for multiple Semicolon ( ; ) separated values

  • Adds new values to existing person record

To make a new Trainer.

  • Current implementation delimiter is currently comma (,) need to make consistent for every uploads to use semicolon)

Person, TrainerApproval

10

Programme Name

No

Matching criteria/Add

N

  • Combination of Programme Name and Number match exactly to a CURRENT programme name on TIS

  • If Programme Name has been provided on the template, then Programme number must also be provided, cannot be left blank.

  • Where the Programme name, number, type, start and end dates matching exactly to an existing Programme membership on the Person, no duplicate Programme membership

  • Where at least 1 of the fields (Programme name, number, type, start and end dates) does not match, a new programme membership is created

 

ProgrammeMembership

11

Programme Number

No

Matching criteria/Add

N

  • Combination of Programme Name and Number match exactly to a CURRENT programme name on TIS

  • If Programme Name has been provided on the template, then Programme number must also be provided, cannot be left blank.

  • Where the Programme name, number, type, start and end dates matching exactly to an existing Programme membership on the Person, no duplicate Programme membership

  • Where at least 1 of the fields (Programme name, number, type, start and end dates) does not match, a new programme membership is created

 

ProgrammeMembership

12

Programme Membership Type

No

Matching criteria/Add

N

  • Must match to a CURRENT value in Reference > Programme membership type

@Sebastian Kwok (Unlicensed) - looks like this was broken as part of generic upload refactor, please raise a ticket and add here.

ProgrammeMembership

13

Programme End Date

No

Matching criteria/Add

N

  • DD/MM/YYYY format

  • Valid Dates only accepted

  • Cannot be before Curriculum #1 start date (Programme Start Date)

 

ProgrammeMembership

14

Rotation

No

Update

N

  • Has to match to a CURRENT Rotation on TIS

 

ProgrammeMembership

15

Curriculum #1

No

Add

N

  • Has to match to a CURRENT curriculum on TIS

 

ProgrammeMembership

16

Curriculum #1 End Date

No

Add

N

  • DD/MM/YYYY format

  • Valid Dates only accepted

  • Cannot be before Curriculum #1 start date

 

ProgrammeMembership

17

Curriculum #1 Start Date

No

Matching criteria/Add

N

  • DD/MM/YYYY format

  • Valid Dates only accepted

  • Cannot be after Curriculum #1 End date

This is also the Programme Start Date and therefore will populate this field.

ProgrammeMembership

18

Curriculum #2

No

Add

N

  • Has to match to a CURRENT curriculum on TIS

 

ProgrammeMembership

19

Curriculum #2 End Date

No

Add

N

  • DD/MM/YYYY format

  • Valid Dates only accepted

  • Cannot be before Curriculum # 2 start date.

 

ProgrammeMembership

20

Curriculum #2 Start Date

No

Add

N

  • DD/MM/YYYY format

  • Valid Dates only accepted

  • Cannot be after Curriculum #1 End date

 

ProgrammeMembership

21

Curriculum #3

No

Add

N

  • Has to match to a CURRENT curriculum on TIS

 

ProgrammeMembership

22

Curriculum #3 End Date

No

Add

N

  • DD/MM/YYYY format

  • Valid Dates only accepted

  • Cannot be before Curriculum # 3 start date.

 

ProgrammeMembership

23

Curriculum #3 Start Date

No

Add

N

  • DD/MM/YYYY format

  • Valid Dates only accepted

  • Cannot be after Curriculum #3 End date

 

ProgrammeMembership

24

Title

Yes

Update

N

  • Has to match to one of CURRENT Title in Reference

 

ContactDetails

25

Known As

No

Update

N

  • Must match FE/BE validation :

 

ContactDetails

26

Date of Birth

Yes

Update

N

  • DD/MM/YYYY format

  • Valid Dates only accepted

  • Must match FE/BE validation :

 

PersonalDetails

27

NI number

Yes

Update

N

  • Must match FE/BE validation :

 

PersonalDetails

28

Email Address

No

Update

N

  • Must be in Email address format

Review the rules to update along with Trainee UI discussions

Depending on Trainee UI developments, this may need to be restricted by this upload?

ContactDetails

29

Mobile

Yes

Update

N

  • Must match FE/BE validation :

Review recent development in TISSS to align the hint/validation for adding and updating.

ContactDetails

30

Telephone

Yes

Update

N

  • Must match FE/BE validation :

Review recent development in TISSS to align the hint/validation for adding and updating.

ContactDetails

31

Address 1

Yes

Update

N

  • If Address 1 provided, then all 4 existing address lines on TIS are cleared and overwritten with the 3 Address fields from the template.

  • cannot be empty if line 2-3 and postcode are entered

 

ContactDetails

32

Address 2

Yes

Update

N

  • If Address 1 provided, then all 4 existing address lines on TIS are cleared and overwritten with the 3 Address fields from the template.

 

ContactDetails

33

Address 3

Yes

Update

N

  • If Address 1 provided, then all 4 existing address lines on TIS are cleared and overwritten with the 3 Address fields from the template.

 

ContactDetails

34

Post Code

Yes

Update

N

  • Should be provided if Address columns are populated

  • Must be provided if Address line 1 is provided.

 

ContactDetails

35

Gender

Yes*

Update

N

Must match to one of TIS Reference values and is CURRENT

 

PersonalDetails

36

Nationality

No

Update

N

Must match to one of TIS Reference values and is CURRENT

 

PersonalDetails

37

Marital Status

No

Update

N

Must match to one of TIS Reference values and is CURRENT

 

PersonalDetails

38

Religious Belief

Yes*

Update

N

Must match to one of TIS Reference values and is CURRENT

 

PersonalDetails

39

Ethnic Origin

Yes*

Update

N

Must match to one of TIS Reference values and is CURRENT

 

PersonalDetails

40

Sexual Orientation

Yes*

Update

N

Must match to one of TIS Reference values and is CURRENT

 

PersonalDetails

41

Disability

Yes*

Update

N

Must match to one of TIS Reference values and is CURRENT

 

PersonalDetails

42

Disability Details

No

Update

N

Freetext

 

PersonalDetails

43

EEA Resident

No

Update

N

Must match to one of TIS Dropdown values

 

RightToWork

44

Permit to Work

No

Update

N

Must match to one of TIS Reference values and is CURRENT

This will be used by the immigration team to upload to TIS.

RightToWork

45

Settled, Permit to Work

No

Update

N

Must match to one of TIS FE values

(YES/NO/No value provided)

 

RightToWork

46

Visa Details/Number

No

Update

N

Freetext

 

RightToWork

47

Visa Issued

No

Update

N

  • dd/mm/yyyy format

  • valid dates only

  • Visa Issued must be before visa valid to

 

RightToWork

48

Visa Valid To

No

Update

N

  • dd/mm/yyyy format

  • Valida dates only

  • Visa Issued must be before visa valid to

 

RightToWork

49

Qualification

No

Add

N

 

  • Consider tidying up at some point

PersonQualification

50

Medical School

No

Add

N

 

  • Consider tidying up at some point


  • Fix validation which is restricting what med schools can be added

PersonQualification

51

Country of Qualification

No

Add

N

  • Must match to one of TIS Country Reference CURRENT value

 

PersonQualification

52

Date Attained

No

Add

N

  • dd/mm/yyyy format

  • Valid dates only

 

PersonQualification

Bulk People Update Column Headers

Row no.

Column heading

ESR bi-directional Update/overwrite

*Subject to IG approval

Add/Update/Matching Criteria

Mandatory

Rules

Questions/Notes for discussions

Internal Entity

Row no.

Column heading

ESR bi-directional Update/overwrite

*Subject to IG approval

Add/Update/Matching Criteria

Mandatory

Rules

Questions/Notes for discussions

Internal Entity

1

TIS_Person_ID*

N/A

Matching Criteria

Y

Must match to an existing Person ID on TIS

 

Person

2

Forenames

No

Update

N

 

 

ContactDetails

3

Surname

No

Update

N

 

 

ContactDetails

4

GMC Number

No

Update

N

Has to be unique and not already existing on TIS against a different Person.

This is required specially to update the UNKNOWNS for Foundation trainees.

GmcDetails

5

GDC Number

No

Update

N

Has to be unique and not already existing on TIS against a different Person.

This is required specially to update the UNKNOWNS for Dental Foundation trainees.

GdcDetails

6

Public Health Number

No

Update

N

Has to be unique and not already existing on TIS against a different Person.

This is required specially to update the UNKNOWNS for Foundation trainees.

Person

7

GMC Status

No

Update

N

Must match to one of the CURRENT GMC Statuses within Reference

 

GmcDetails

8

GDC Status

No

Update

N

Must match to one of the CURRENT GDC Statuses within Reference

 

GdcDetails

9

Title

Yes

Update

N

 

 

ContactDetails

10

Known As

No

Update

N

 

 

ContactDetails

11

Date of Birth

Yes

Update

N

 

 

PersonalDetails

12

NI number

Yes

Update

N

 

 

PersonalDetails

13

Email Address

No

Update

N

Review the rules to update along with Trainee UI discussions

Depending on Trainee UI developments, this may need to be restricted by this upload?

ContactDetails

14

Mobile

Yes

Update

N

 

 

ContactDetails

15

Telephone

Yes

Update

N

 

 

ContactDetails

16

Address 1

Yes

Update

N

  • If Address 1 provided, then all 4 existing address lines on TIS are cleared and overwritten with the 3 Address fields from the template.

  • cannot be empty if line 2-3 and postcode are entered

 

ContactDetails

17

Address 2

Yes

Update

 

  • If Address 1 provided, then all 4 existing address lines on TIS are cleared and overwritten with the 3 Address fields from the template.

 

ContactDetails

18

Address 3

Yes

Update

 

  • If Address 1 provided, then all 4 existing address lines on TIS are cleared and overwritten with the 3 Address fields from the template.

 

ContactDetails

19

Post Code

Yes

Update

 

  • Should be provided if Address columns are populated

 

ContactDetails

20

Gender

Yes*

Update

 

Must match to one of TIS Reference values and is CURRENT

 

PersonalDetails

21

Nationality

No

Update

 

Must match to one of TIS Reference values and is CURRENT

 

PersonalDetails

22

Marital Status

No

Update

 

Must match to one of TIS Reference values and is CURRENT

 

PersonalDetails

23

Religious Belief

Yes*

Update

 

Must match to one of TIS Reference values and is CURRENT

 

PersonalDetails

24

Ethnic Origin

Yes*

Update

 

Must match to one of TIS Reference values and is CURRENT

 

PersonalDetails

25

Sexual Orientation

Yes*

Update

 

Must match to one of TIS Reference values and is CURRENT

 

PersonalDetails

26

Disability

Yes*

Update

 

Must match to one of TIS Reference values and is CURRENT

 

PersonalDetails

27

Disability Details

No

Update

 

Freetext

 

PersonalDetails

28

EEA Resident

No

Update

 

Must match to one of TIS Dropdown values

 

RightToWork

29

Permit to Work

No

Update

 

Must match to one of TIS Reference values and is CURRENT

This will be used by the immigration team to upload to TIS.

RightToWork

30

Settled

No

Update

 

Must match to one of TIS values

 

RightToWork

31

Visa Details/Number

No

Update

 

Freetext

 

RightToWork

32

Visa Issued

No

Update

 

  • dd/mm/yyyy format

  • Visa Issued must be before visa valid to

 

RightToWork

33

Visa Valid To

No

Update

 

dd/mm/yyyy format

Visa Issued must be before visa valid to

 

RightToWork

33

Role

No

  • Update and replace all existing values

 

 

  • Must match to one of TIS Reference values and is CURRENT

  • Allows for multiple Semicolon ( ; ) separated values

To make a new Trainer.

 

Person / TrainerApproval

35

Trainer approval start date

No

Update

 

  • dd/mm/yyyy format

  • Trainers approval start date cannot be after end date

 

TrainerApproval

36

Trainer approval end date

No

Update

 

  • dd/mm/yyyy format

  • Trainers approval start date cannot be after end date

 

TrainerApproval

37

Trainer Approval status

No

Update

 

Must match to one of TIS values; CURRENT or INACTIVE

Note: This field will be removed from the template when automated to work based on start and end dates.

TrainerApproval

 

Questions and Assumptions

No.

Question/Assumption

Comment

No.

Question/Assumption

Comment

1.

Implications to Trainee UI and ESR bi-directional updates

 

2.

Trainer Approval status will be removed from the template, once the data leads have updated the trainers records on TIS, populated the dates. This field will then be automated based on the dates

 

3.

Email address - Depending on the process to onboard trainees to Trainee UI, will have to review how this field will work.

 

4.

Fields that will be updated by the ESR bi-directional are being kept on this template, as an alternative to undo not required updates if necessary.

 

 

 

 

 

 

 

 

 

 

Slack: https://hee-nhs-tis.slack.com/
Jira issues: https://hee-tis.atlassian.net/issues/?filter=14213