RCGP e-Portfolio Data Transfer Automation

@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

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
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