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