Content Comparison

💡 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

•  Priority 1 Done

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?

•  Public Health Application Type: It's a field that is only populated for people who have been appointed to public health, and it will either say a medical practitioner or another practitioner. Knowing the difference between the Medicaid non medic is a requirement? Silence in the meeting is taken as YES
•  The medical one are those that are on the medical route and so should be under the GMC and the other
  Practitioners are of those appointed through the non medic route.
•  In the Midlands, they do separate those on TIS and that's the only way they can identify which route they've been appointed through.
•  It helps to identify which ones to chase for their GMC.
  Numbers or maintaining GMC Status
•  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; Louise “making a change on a programme or a date and it won't let me come out until I've changed the ethnicity, because I guess some of that has changed in the background.
      Some of the terminology, so I guess that's what's going on, but I think if you Scroll down, I think some of this is outlined in .4.”

   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. Louise “if the people upload could be a whole lot easier than it is now without having to spend hours changing data so it can be uploaded. That would be amazing.” Then Programme membership upload to follow with less issues.

5. Duplicate as a result of updating Unknown GMC number before the next upload is a major cause of duplicate records: It will create a new record and might need to remove legacy one.

6. Uploading Oriel Pin Number/ Application applicant Number into TIS; It is in the person upload but not saved anywhere in TIS

•  marital status, gender, Nationality, ethnicity that is not aligned between Oriel and TIS
•  James on Marital Status “we got that through basically, what we had before was a legacy. So, the data in Oriel, we weren't getting one of the issues that we kind of have with how the data flows is. So the forms on Oriel will change, but those new fields if they're newly added and they're not just updated, won't come through in the data set. We have to then request it. We had previously a marital status that was a legacy, a legacy values, and we also had the Northern Ireland marital status, but we didn't have the the England one because Northern Ireland has slightly separate values. So yeah, so having that field for marital status should at least resolve some of those pain points”
•  In the future, nationality as an example and ethnicity will the same? Question from Louise, and Yes from James

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.