Due to Customer demands ��

A new feature in the Generic (not PAH/STH) ESM system now exists

The Mapping table can be exported for editing in excel

Import will merge changes and append new.

An error in the handling of Commas in the export and import is resolved

I must note that adding new has only one purpose to do a date split on an existing row

Unlike in the prior Dept Mapping system where all were exported, and rows could be pasted, that is not the same.

• Rows cannot be added or pasted into the form
• All rows were exported but only changed or new rows needed to be imported and others were not altered.

In the new system

• If a filter other than % is active i.e. filtered, the default name of the  excel XLS file will include -FILTERED.xls

• If a filter  % is active i.e. not filtered, the default name of the  excel XLS file will include -ALL.xls
• You can change the name of the file when you save it
• You should always save and leave the files in ESM_Imports
• Splits of rows by date range must be done by highlighting a row in excel (click the row number on the left), a row, not a set of cells!!, and right click copy, then while the row is still selected right click, insert copied cells
• You must then alter the end date of the top row and it must end on your end date (last date to map that line) with a time of 23:59:59, and the inserted row must start on the midnight of the following day or to be precise the next second after the end date of the line above.
• Please ensure
• You must NEVER EVER add columns or place data outside the existing area
• Do not add blank rows
• Do not just add rows below. The Tables is a named range and only the named range will be imported
• Delete any blank rows
• Do not alter the underlying sort order
• Do not add worksheets
• DO NOT ALTER COLUMN HEADINGS - NOT ONE SPACE, COMMA, - NOTHING
• You must observe your ANY columns rules you cannot change an ANY column EVA! or any of there elements which are from the source data
• COMPANY_TLA
• CAMPUS_TLA
• LOCATION
• PROVIDER_TYPE
• APPT_TYPE
• RESOURCE
• RESOURCE_CD
• FACILITY_CODE
• Product_Key
• You may copy and paste in the three mapping columns
• Mapped_TII_Dept
• MAPPED_UNIT
• MAPPED_DIVISION
• Only as above may you alter START_DATE or END_DATE
• Record Date will update automatically on import

As this table has ALL the rows found in the data. Adding other lines has NO purpose at all

Duplicated lines will merely ensure that last of the duplicates is used, a line by line update and insert process is used to maintain the primary key of the table

Use of the Missing and ALL spreadsheets generated on load in the past form is NOT!!!! Permitted as they do not have the correct columns.

These export formats will be updated to match shortly.

Generate your own MISSING XXXX99 & DBNU99 files using filters and export them until that occurs

There is no room for variation in the new method.

Date range mapping is only to be used where a Clinic/type/provider/resource changes

When someone leaves to NOT dead end their mapping, certainly split them, map the post termination dept to DNBU99 - you will never know that they are not coming back !!!

All mappings must cover from 1/1/1900 to 1/1/2100 00:00:00

• Font is bigger and only editable columns are in black, all others a grayed. Record Date will keep itself in order.
• Right Clicking any cell, allows the filter to be set to that cells contents on the drop down menu
• Clicking the row header (the grey bit on the left with an > is selected but any row header..)

will filter to same location, provider, appt_type resource, resource_cd and facility_code

• Click it again to reset back to current filter
• Clicking on record date heading twice shows recent additions
• Click any column header to sort by that column (if not filtered can take a while!!),
• Click again to reverse sort order - be patient
• Click COMPANY_TLA to revert to natural sort order which is

ORDER BY LOCATION, PROVIDER_TYPE, APPT_TYPE, RESOURCE,RESOURCE_CD

Clicking Date Split Mode while on a specific row or cell in that row (here the BOTTOM ROW)

• Filters to all rows with that location, provider, appt_type resource, resource_cd and facility_code (logically if not already date split this will be a single row)
• Selection of a specific row if more than one, setting a date (bottom left) (a date picker allows dates to be selected in a standard Microsoft manner
• Clicking Do Date Split will
• Confirm that the date selected is between the row selected start date and end date and if not drop back
• Prompt to proceed
• Alter the Current row to have and end Date of date selected minus one second
• Prompt you to save any edits (made since last save in any rows!!)
• Add a new row with a start date at 00:00:00 as entered
• Refresh the data
• Reload the data
• Re-apply the current filter

Important Point

Please note that once split, you can then edit the new date range row and change the dept code, unit, division

If you split a row and don’t change anything, then it does not matter as the full date range is covered in two or more mappings.

Repeat IT DOES NOT MATTER AND DOES NOT NEED TO BE DELETED

If you wish to dead end a particular mapping, split it on its termination date, end of clinic date whatever .. and assign DNBU99 as the last dept.

Later if they return (Resource) or change mind, you can split the last record on their reappointment date and assign a new value to the mappingor replace DBNU99 with correct dept. The Split does not need to be removed..

A by product of this function allows you so filter very narrowly to a specific mapping for all start dates and check for consistency, then edit as required then click cancel split and save.

Save button will no longer exit, use cancel and exit after save to exit or close form at top right.

The ability to change start and end dates manually has been removed

DNBU99

• This is special, this dept will be filtered from utilisation extracts sent to T2
• You may end up with XV_ESM records with no utilisation as the blocking of a specific utilisation may not block other utilisation for the same "episode" or may.. I cant tell, the episode has no single dept. Remember to always look in t2 for Encounters with no utilisation, these should be disposed of.
• It is intended to eliminate data duplicated in Theatre systems etc. Use with care.
• While it is not necessary to end a mapping for a terminated resource you can use this. I stress, the presence of data determines if there is utilisation for resource or clinic.