Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

The original agreement with the Royal Colleges was for a monthly transfer of Continuing Trainee Data from the 4 Nation SEBs (TIS, Turas, Intrepid). This was done through the creation of monthly data which are then uploaded to a secure SharePoint site where Royal Colleges has access to their named folders. For NHSE and NIMDTA, this is a fully automated process from TIS via the NDW. For Scotland and Wales this is managed from relevant colleagues via Turas and Intrepid respectively.

From conversations with the RCGP and NHSE GP administrators the process above was not sufficient due to the following reasons:

  • RCGP e-Portfolio requires data to be maintained by NHSE GP administrators whereas other Royal College e-Portfolios the burden of data entry is on the Trainee. This means there is a far higher amount of double entry by admin staff.

  • The above as well as the frequency of change within individual trainee’s training programmes (Programme/Curriculum end dates, Placements, WTE, etc) meant that the monthly frequency of the Continuing Trainee Data was not sufficient enough.

In light of the above, work has begun to investigate an automated solution where data is mapped from TIS to the RCGP e-portfolio (14Fish) on a more frequent basis. The current proposal is to use the same method as data is transferred to Accent Leave Manager, TIS ETL --> NDW --> NDW Secure Server --> 14Fish. This transfer would take place on a daily basis.James Harris Rob Pink Alan Sayers (RCGP) Lee Male (14Fish) Brook Elgie (14Fish) Lauren Hucker (14Fish)

The original agreement with the Royal Colleges was for a monthly transfer of Continuing Trainee Data from the 4 Nation SEBs (TIS, Turas, Intrepid). This was done through the creation of monthly data which are then uploaded to a secure SharePoint site where Royal Colleges has access to their named folders. For NHSE and NIMDTA, this is a fully automated process from TIS via the NDW. For Scotland and Wales this is managed from relevant colleagues via Turas and Intrepid respectively.

From conversations with the RCGP and NHSE GP administrators the process above was not sufficient due to the following reasons:

  • RCGP e-Portfolio requires data to be maintained by NHSE GP administrators whereas other Royal College e-Portfolios the burden of data entry is on the Trainee. This means there is a far higher amount of double entry by admin staff.

  • The above as well as the frequency of change within individual trainee’s training programmes (Programme/Curriculum end dates, Placements, WTE, etc) meant that the monthly frequency of the Continuing Trainee Data was not sufficient enough.

In light of the above, work has begun to investigate an automated solution where data is mapped from TIS to the RCGP e-portfolio (14Fish) on a more frequent basis. The current proposal is to use the same method as data is transferred to Accent Leave Manager, TIS ETL --> NDW --> NDW Secure Server --> 14Fish. This transfer would take place on a daily basis.

Relevant TIS Tables and Fields for Data Transfer - To Be Confirmed

NDW Table Name

Field Name

Data Type

Field Description

Front End Field

Field Type and Interaction

Mandatory

Validation

Notes

vwPerson

PersonTisID

bigint

Unique Identifier/Primary Key

System field - Automated upon creation of record

Y

vwPerson

Title

varchar

Y

Drop down

Y

restricted to options from title ref table

Initially uploaded from Oriel Application

vwPerson

Forenames

varchar

Y

Free text

character validation (no special characters aside from apostrophes and hyphens)

multiple names allowed

Y

Initially uploaded from Oriel Application

vwPerson

Surname

varchar

Y

Free text

character validation (no special characters aside from apostrophes and hyphens)

multiple names allowed

Y

Initially uploaded from Oriel Application

vwPerson

Gender

varchar

Y

Drop down

Y

restricted to options from Gender ref table

Initially uploaded from Oriel Application

vwPerson

EmailAddress

varchar

Y

Free text

Y

Initially uploaded from Oriel Application

vwPerson

ContactEmail

varchar

N

Free text

N

Uploaded from and editable by the TIS-ESR bi-drectional interface if available.

vwPerson

GMCNumber

varchar

Y

Free text

N

A record must have a valid GMC Number, GDC Number or Public Health Number. UNKNOWN is allowed for doctors not yet on the GMC Registrar

Initially uploaded from Oriel Application

vwPerson

TelephoneNumber

varchar

Y

Free text

N

Initially uploaded from Oriel Application

vwPerson

MobileNumber

varchar

Y

Free text

N

Initially uploaded from Oriel Application

vwPerson

AddressLine1

varchar

Y

Free text

Y

Initially uploaded from Oriel Application.

Editable by the TIS-ESR bi-drectional interface if available.

vwPerson

AddressLine2

varchar

Y

Free text

Y

Initially uploaded from Oriel Application.

Editable by the TIS-ESR bi-drectional interface if available.

vwPerson

AddressLine3

varchar

Y

Free text

Y

Initially uploaded from Oriel Application.

Editable by the TIS-ESR bi-drectional interface if available.

vwPerson

AddressLine4

varchar

Y

Free text

Y

Initially uploaded from Oriel Application.

Editable by the TIS-ESR bi-drectional interface if available.

vwPerson

AddressPostCode

varchar

Y

Free text

Y

Initially uploaded from Oriel Application.

Editable by the TIS-ESR bi-drectional interface if available.

vwProgrammeMembership/vwCurriculumMembership

ProgrammeMembershipTisUuid

varchar

Unique Identifier/ Key

N

System generated upon record creation

vwProgrammeMembership/vwCurriculumMembership

ProgrammeTisID

bigint

Foreign Key

N

Primary Key of Programme reference table

vwProgrammeMembership/vwCurriculumMembership

TrainingPathway

varchar

Career Pathway

Y

Autocalculated based on LeadsToCCT of Curriculum reference table

Drop Down

Editable

Y or N/A

Must be a valid value of

  • CCT

  • CESR

  • N/A

Image Added

vwProgrammeMembership/vwCurriculumMembership

ProgrammeManagingDeanery

varchar

Y

Auto populated when Programme Name is selected

Y

Derived

vwProgrammeMembership/vwCurriculumMembership

ProgrammeName

varchar

Y

Drop down

Y

Must be a valid and Current value in Programme ref table

vwProgrammeMembership/vwCurriculumMembership

ProgrammeStartDate

Date

Y

Calendar picker

Y

date from must be before the ProgrammeEndDate

vwProgrammeMembership/vwCurriculumMembership

ProgrammeCurriculumCompletionDate

date

Y

Derived from latest CurriculumEndDate of the Curricula attached to the Programme Membership

N/A

Derived

vwProgrammeMembership/vwCurriculumMembership

ProgrammeEndDate

date

Y

Calendar picker

Y

date from must be after the ProgrammeStartDate

vwPlacement

PlacementTisID

bigint

Unique Identifier/Primary Key

N

System generated upon record creation

N/A

vwPlacement

StartDate

date

Y

calendar picker

Y

date from must be before the EndDate

vwPlacement

EndDate

date

Y

calendar picker

Y

date from must be before the StartDate

vwPlacement

GradeAbbreviation

varchar

Y

drop down

Y

vwPlacement

PlacementType

varchar

Y

drop down (from reference table)

Y

restricted to options from placement type ref table

vwPlacement

SiteTisID

bigint

Foreign Key

N

Primary Key of Site reference table

N/A

derived

vwPlacement

SiteName

varchar

Y

  • derived from the post

  • can be overwritten

  • add multiple

Y

derived

vwPlacement

SpecialtyTisID

bigint

Foreign Key

N

Primary Key of vwSpecialty

N/A

derived

vwPlacement

SpecialtyName

varchar

Y

derived from post

drop down

Y

  • derived from post, can be overwritten

  • add multiple

  • at least one specialty must be added

  • editable

vwPlacement

SpecialtyNumber

varchar

N

derived from post

Y

derived

vwPlacement

WholeTimeEquivalent

decimal

Y

free text

number values only

Y

must be a number between 0 and 1, Default value = 1

vwPlacementOtherSite

PlacementSiteTisID

bigint

Unique Identifier/Primary Key

N

System generated upon user selecting and saving an Other Site on the Placement

N

vwPlacementOtherSite

PlacementTisID

bigint

Foreign Key

N

Primary Key of Placement record

N

vwPlacementOtherSite

SiteTisID

bigint

Foreign Key

N

Primary Key of Site reference table recorded when user select an Other Site on a Placement.

Site Name drop down on Front End

N

vwPlacementOtherSpecialty

PlacementTisID

bigint

Foreign Key

N

Primary Key of Placement record

N

vwPlacementOtherSpecialty

SpecialtyTisID

bigint

Foreign Key

N

Primary Key of Specialty reference table recorded when user select an Other Site on a Placement.

Specialty Name drop down on Front End

N

vwPlacementSupervisor

PlacementTisID

bigint

Foreign Key

N

Primary Key of Placement record

N

vwPlacementSupervisor

SupervisorPersonTisID

bigint

Foreign Key

N

Primary Key of Person record

Smart search on placement for GMC Number in either the Clinical Supervisor field or Educational Supervisor field

must be a clinical supervisor or must be an educational supervisor

vwPlacementSupervisor

SupervisorType

varchar

Y

Derived by the field Clinical Supervisor or Educational Supervisor on Placement

derived

vwPlacementSupervisor

SupervisorSurname

varchar

Y

Auto-populated from Person record

derived

vwPlacementSupervisor

SupervisorForenames

varchar

Y

Auto-populated from Person record

derived

vwPlacementSupervisor

SupervisorGMCNumber

varchar

Y

Auto-populated from Person record

derived

vwProgramme

ProgrammeTisID

bigint

Unique Identifier/Primary Key

N

System generated upon record creation

N/A

vwProgramme

ManagingDeanery

varchar

Y

Drop down

Y

Must be a value in the Local Office ref table

vwProgramme

ProgrammeName

varchar

Y

Free Text

Y

vwProgramme

ProgrammeNumber

varchar

Y

Free Text

Y

vwProgramme

Status

varchar

Y

Drop Down

Y

Values:

  • Current

  • Inactive

vwSite

SiteTisID

bigint

Unique Identifier/Primary Key

N

System generated upon record creation

N/A

vwSite

SiteName

varchar

Y

Free Text

Y

vwSite

SiteCode

varchar

Y

Free Text

Y

Must be unique

vwSite

Status

varchar

Y

Drop down

Y

Values:

  • Current

  • Inactive

vwSite

AddressLine1

varchar

Y

Free Text

N

vwSite

AddressPostCode

varchar

Y

Free Text

N

vwSpecialty

SpecialtyTisID

bigint

Unique Identifier/Primary Key

N

System generated upon record creation

N/A

vwSpecialty

SpecialtyName

varchar

Y

Free Text

Y

vwSpecialty

SpecialtyNumber

varchar

Y

Free Text

Y

Must be unique

vwSpecialty

Status

varchar

Y

Drop down

Values:

  • Current

  • Inactive

Data Mapping: TIS - 14Fish RCGP e-Portfolio - To Be Confirmed

In order to achieve the above, the depth and breadth of the data required from TIS must be determined. Of that data set, a mapping of TIS data fields to 14Fish is also required. The below mapping represents the RCGP e-Portfolio fields as demoed on the frontend. A full list of relevant fields in the backend of the system are unknown. The behaviour of the fields in FourteenFish and their interdependencies are also unknown.

Please note that all ProgrammeMembership fields could also come from CurriculumMembership

...