RCGP e-Portfolio Data Transfer Automation

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

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

ID

N

N/A

Assumption this is a unique system ID

Title

Y

Person.Title

First Name

Y

Person.Forenames

Surname

Y

Person.Surname

Gender

Y

Person.Gender

Main Email

Y

Person.ContactEmail

Other Email

Y

Person.EmailAddress

This email address is what is held by the current employing trust and is populated by the TIS-ESR bidirectional interface if live

GMC

Y

Person.GMCNumber

Nat. Training No.

Y

NTN

This field is not saved in TIS but is calculated based upon TIS data

Start of Training

Y

ProgrammeMembership/CurriculumMembership.ProgrammeStartDate

End of Training

Y

ProgrammeMembership/CurriculumMembership.ProgrammeCurriculumCompletionDate or ProgrammeMembership/CurriculumMembership.ProgrammeEndDate

ProgrammeCurriculumCompletionDate is the last date of all curriculum components of a programme whereas ProgrammeEndDate will include Period of Grace. GP Trainees do not have access to POG and so the dates should be identical

Enable Postfolio

N

Assume an action by a trainee, NHSE admin or other system admin will trigger this to be enabled

Phone

Y

Person.TelephoneNumber

Mobile

Y

Person.MobileNumber

Address

N

Person.AddressLine1

Person.AddressLine2

Person.AddressLine3

Person.AddressLine4

Person.AddressPostCode

Multiple fields in TIS which would need to be concatenated if one field in 14Fish

Certificate Type

Y

ProgrammeMembership/CurriculumMembership.TrainingPathway

Certificate subtype

N

Deanery

Y

ProgrammeMembership/CurriculumMembership.ProgrammeManagingDeanery

This is a reference table that exists in both systems which are not aligned. Either these reference tables need to be aligned or equivalents mapped

Training prog

Y

ProgrammeMembership/CurriculumMembership.ProgrammeName

This is a reference table that exists in both systems which are not aligned. Either these reference tables need to be aligned or equivalents mapped

Trainee

N

Concatenation of Person.Title Person.Forenames Person.Surname

Phonetic name

N

This field is not on Oriel and therefore TIS

GMC

Y

Person.GMCNumber

Training number

Y

NTN

This field is not saved in TIS but is calculated based upon TIS data

Certificate type

Y

ProgrammeMembership/CurriculumMembership.TrainingPathway

Certificate subtype

N

Current stage of training

N

Placement.ApprovedGradeAbbreviation or Placement.PlacementType

Assumption that this field is displaying data populated in the Stage of Training page. Please see below

Educational Supervisor

N

Concatenation of PlacementSupervisor.SupervisorForenames PlacementSupervisor.Surname where the Placement is CURRENT, Placement.PlacementTisID = PlacementSupervisor.PlacementTisID and PlacementSupervisor.SupervisorType = ‘EducationalSupervisor’

Unsure of whether this field is populated by Post or by ESR

Deanery

Y

ProgrammeMembership/CurriculumMembership.ProgrammeManagingDeanery

This is a reference table that exists in both systems which are not aligned. Either these reference tables need to be aligned or equivalents mapped

Training Programme

Y

ProgrammeMembership/CurriculumMembership.ProgrammeName

This is a reference table that exists in both systems which are not aligned. Either these reference tables need to be aligned or equivalents mapped

Dean/RO

Y/N

This field exists in the TIS backend and in Revalidation but is not exposed currently for export. There is a mapping of ProgrammeManagingDeanery/Local Office from TIS or the GMC which could be used.

CCT date

Y

ProgrammeMembership/CurriculumMembership.ProgrammeCurriculumCompletionDate

Scheme Days - Primary Care

N

Total of data differentials of Placement.StartDate and Placement.EndDate where Placement.SpecialtyName = ‘General Practice’

Assumption that this is derived from Posts or Stage of Training. On TIS this would most likely be all the placements where the specialty = ‘General Practice’

Scheme Days - Non-primary care

N

Total of data differentials of Placement.StartDate and Placement.EndDate where Placement.SpecialtyName <> ‘General Practice’

Assumption that this is derived from Posts or Stage of Training. On TIS this would most likely be all the placements where the specialty <> ‘General Practice’

Scheme Days - Total

N

Total of data differentials of Placement.StartDate and Placement.EndDate

Assumption that this is derived from Posts or Stage of Training.

GP - Organisation

Y

Placement.SiteName where Placement is CURRENT

There is a Site reference table on 14Fish and TIS. These are not aligned and would need to be as mapping would be extremely labour intensive and quickly become deprecated.

GP - Dates

N

Concatenation of Placement.StartDate - Placement.EndDate where Placement is CURRENT

Assumption that this is derived from current Post

GP - Days

N

Date differential of the above

GP - Time Out of training

N

TIS does not currently record short periods of time out of training

GP - WTE

Y

Placement.WholeTimeEquivalent

In TIS this is a decimal, 1 being Full Time

Is this an ITP Post

N

If PlacementOtherSite.PlacementTisID is not null and/or??? PlacementOtherSpecialty.PlacementTisID is not null for a Placement then Is this an ITP Post = Y

If a placement has an OtherSite and/or an OtherSpecialty than there is a record in the PlacementOtherSite or PlacementOtherSpecialty table respectively for that parent Placement

Scheme Days - Primary Care

N

DateDiff Placement.StartDate and Placement.EndDate where Placement.SpecialtyName = ‘General Practice’

If this is an ITP post, on TIS the PlacementOtherSpecialty/Site will have the same dates as the parent placement and therefore any count of days will be the same. Should these be split even across this range, i.e. Datediff(day,Placement.StartDate,Placement.EndDate)/2???

Scheme Days - Non-Primary Care

N

DateDiff Placement.StartDate and Placement.EndDate where Placement.SpecialtyName <> ‘General Practice’

If this is an ITP post, on TIS the PlacementOtherSpecialty/Site will have the same dates as the parent placement and therefore any count of days will be the same. Should these be split even across this range, i.e. Datediff(day,Placement.StartDate,Placement.EndDate)/2???

Scheme Days - Total

N

What is this a total of???

Organisation

Y

Placement.SiteName

There is a Site reference table on 14Fish and TIS. These are not aligned and would need to be as mapping would be extremely labour intensive and quickly become deprecated.

Dates

N

Concatenation of Placement.StartDate - Placement.EndDate

Days

N

Datediff(day,Placement.StartDate,Placement.EndDate)

WTE

Y

Placement.WholeTimeEquivalent

In TIS this is a decimal, 1 being Full Time

GMC

Y

Person.GMCNumber

Training Number

Y

NTN

This field is not saved in TIS but is calculated based upon TIS data

Certificate Type

Y

ProgrammeMembership/CurriculumMembership.TrainingPathway

Certificate subtype

N

Current stage of training

N

For the current placement:

If Placement.PlacementType in ('In Post','In Post - Acting Up','In Post - Extension') then Placement.ApprovedGradeAbbreviation

If Placement.PlacementType not in ('In Post','In Post - Acting Up','In Post - Extension') then Placement.PlacementType

Not sure if all placement types should be displayed in the second logic. Attached is a list of current placement types on TIS.

Unsure if ‘In Post’, ‘In Post - Acting Up’ and 'In Post - Extension should be different stages of training???

Educational Supervisor

N

Concatenation of PlacementSupervisor.SupervisorForenames PlacementSupervisor.Surname where the Placement is CURRENT, Placement.PlacementTisID = PlacementSupervisor.PlacementTisID and PlacementSupervisor.SupervisorType = ‘EducationalSupervisor’

Unsure of whether this field is populated by Post or by ESR

Deanery

Y

ProgrammeMembership/CurriculumMembership.ProgrammeManagingDeanery

This is a reference table that exists in both systems which are not aligned. Either these reference tables need to be aligned or equivalents mapped

Training Programme

Y

ProgrammeMembership/CurriculumMembership.ProgrammeName

This is a reference table that exists in both systems which are not aligned. Either these reference tables need to be aligned or equivalents mapped

Dean/RO

Y/N

This field exists in the TIS backend and in Revalidation but is not exposed currently for export. There is a mapping of ProgrammeManagingDeanery/Local Office from TIS or the GMC which could be used.

CCT Date

Y

ProgrammeMembership/CurriculumMembership.ProgrammeCurriculumCompletionDate

Stage of Training

N

A combination of sequential placements should be considered to make up on stage of training if the following are the equal Placement.GradeAbbreviation, Placement.PlacementType in ('In Post','In Post - Acting Up','In Post - Extension'), Placement.WholeTimeEquivalent.

If any of the above of sequential placement is not equal to the previous placement, a new Stage of Training should be created.

If Placement.PlacementType in ('In Post','In Post - Acting Up','In Post - Extension') then Placement.ApprovedGradeAbbreviation

If Placement.PlacementType not in ('In Post','In Post - Acting Up','In Post - Extension') then Placement.PlacementType

Unsure if ‘In Post’, ‘In Post - Acting Up’ and 'In Post - Extension should be different stages of training???

Dates

N

A combination of sequential placements should be considered to make up on stage of training if the following are the equal Placement.GradeAbbreviation, Placement.PlacementType in ('In Post','In Post - Acting Up','In Post - Extension'), Placement.WholeTimeEquivalent.

Dates would then be Min(Placement.StartDate) - Max(Placement.EndDate)

Full-Time

N

If Placement.WholeTimeEquivalent = 1 then Yes, else No

Is an extension

N

If Placement.PlacementType = ‘In Post - Extension’ then Yes, else No

Do NOT count towards training

N

If Placement.PlacementType not in ('In Post','In Post - Acting Up','In Post - Extension') then Yes, else No

Notes

Y/N

There is a Placement.Comments field but unsure if the notes in these fields are equivalent