2025-02-13 Oriel-NDW-TIS Options Follow-up Meeting

TIS: Ashley Ransoo Adewale Adekoya James Harris Kavitha.Shankar

13 Feb 2025

💡 Details

Actions / Decisions

https://hee-tis.atlassian.net/wiki/spaces/NTCS/pages/4595941521/Oriel-TIS+Solution+Options+-+Phased+Approach#1.-Filter-and-Enrich-Oriel-Data-with-Timestamps

1. Added Fields:

   1. DayToDayActivitiesLimited (yes/no) – PIDSpecial Schema

   2. MaritalStatus – PIDSpecial Schema

   3. OfferRoundName – PID Schema

   4. StaffGroups – PID Schema

   5. AllocationPreference – PIDSpecial Schema

2. Still outstanding

   1. OfferPreference – Is this still required?

   2. AllocationCode – Is this different to AllocationPreference

   3. Public Health Application Type – Not sure what this is but assume it is Medic vs Non-Medic Public Health. Can this be derived from other fields?

• Offer Preference - Still required. Has similar data to Offer Description but with some difference. In Particular GP may hold specific info to the programme. This is a free textbox. Used to determine where they are going when the Offer Description is not enough.
• IOS form to be sent by Louise Peckham. - Offer Preference contains the narrative of the preference whereas the code is the GMC approved code.
• To review how this aligns with TIS programme code, programme name etc.
• Training Programme = General Practice, Offer Preference = ‘Coventry & Warwickshire - ST1 - Aug - 3’ , Offer Description = 'HEE West Midlands - Coventry & Warwickshire - ST1 - HEFT posts available, for more information please click on the following link -https://sway.office.com/GFff3fvOoCNguGy3?ref=Link '
• Allocation Code - Different to Allocation Preference although related to the same thing. Identity set of rotations for the posts within the preference. E.g. used to identify which rotations have not been allocated.
• PH Application Type - Medic vs Non-Medic Public Health
• Identity which route the offer was appointed for. Although unsure if this is the only way to determine this.
• To determine the Lead Employer where this is a model used in a region.
• James Harris to chase with Hicom on the additional fields.

1. NE: Original query created by James used, extract to an excel template, added to tableau, that formats it correctly for TIS upload, use of dropdown in the Programme and curriculum names to populate those fields. Use of Data Sheets to populate tableau. Tableau is used due its access to all.

   1. Issues regarding marital status, gender, ethnicity that is not aligned between Oriel and TIS

   2. TIS not allowing to update the record if other data are not correct. E.g. without correcting the ethnicity.

   3. Marital Status NI Marital Status are legacy values on TIS therefore not aligned.

2. South: Similar to NE with validation done in an Excel template for missing data that is adapted from the TIS Bulk upload template. Programme team updates missing Programme and curriculum data on template.

3. Pain point is there a still a lot of mapping that is still required despite the script.

4. Bulk People upload to sync through without having to change much data. Then Programme membership upload to follow with less issues.

• marital status, gender, ethnicity that is not aligned between Oriel and TIS

1. Andrew Horton tweaks to SQL script

   1. Marital Status values removed – couldn’t get uploads to work with them in.

   2. We’ve put a case statement in to make Nationality a Nationality – may have missed some but had everything we needed last year.

   3. Case statement for to match to the TIS Gender reference values as at April 2024 reference values.

   4. We put the provided OrielID’s into an IN statement to capture the records we want. – How are these known?

2. Other pain points from Andy and team

   1. Move address 3 into address 2 where it is blank

   2. Check the telephone number looks correct as there is sometimes an issue with formatting (Convert the column to number, then remove the decimal places)

3. Questions:

   1. Will the ‘tweaks’ above be useful for all and should they be applied?

   2. Do the Address[RT1]  and Phone Number pain points hold true for all? Should a fix be investigated for them?

• James Harris to adjust script with the additions from South.
• Oriel Address Fields and TIS Address Fields not aligned - still has to copy Line 3 in 2 in order to upload for TIS. Need to factor in ESR address fields in the alignment process to match all 3.
• Telephone numbers not coming though as numbers due to downloading as a CSV. Requiring the file to be converted to proper Excel first to resolve this.
• Ethnicity - Oriel, TIS and ESR should follow the latest release of NWD Dataset / Dictionary. However, requires a process to manage the release and updates into the systems to keep alignment. In TIS values that are discontinued in the NWD dataset on a subsequent release is set as INACTIVE to maintain historical records.
• GMC numbers - Tableau Dashboard to review records that have not got a registration number at the time of recruitment used in the East Midlands in order to avoid creating multiple person records meant for the same person on TIS.
• Deduplication work - Complexity to investigate and resolving retrospectively. Trainees uploaded on a previous year but did not start the programme ad then appointed again in the next year to start tends to generate duplicates - need a way to identify those?
• Fixing formats of fields at source on Oriel a the time of entering on application form is the desire - adding validations. E.g. mixed cases. This to be fed back to Oriel. However, needs to review examples where this can be manipulated in transit.
• Kate to send an example of their template with their rules.

• Formatting issues is the priority before adding to tableau
• Option 2 - Putting Oriel data on Tableau to then follow
• Option 3 - Oriel-TIS data comparison - e.g. can Unknown be added to trainees where there is no registration number?
• Oriel PIN / Applicant ID to be uploaded to TIS - to help stop duplicates and potentially the registration numbers. James Harris to share the combination of 3 fields we have used in the NDW in order to determine uniqueness as Applicant ID was not sufficient.

• Useful session to have regularly - March/April to have an update on progress. Frequency dependent on recruitment loads.
• To provide timeframes/ non-specific indications to this group on when some of this would be available in preparation for next recruitment Rounds.