Wearers

Bulk Wearer Data Importing

The more complete Wearer information can be in the Historion database, the more valuable the information becomes for analysis and reporting. Historion provides a Bulk Wearer Data Import Utility to simplify and expedite the completion of as much of the Wearer record as possible. The utility helps to fill the gaps in all of the important Wearer demographic, classification, licence and address details.

The utility uses a Microsoft Excel spreadsheet template with free text cells and controlled value drop down lists for the source data. The controlled value drop down lists match Historion equivalents.

The utility also imports Radiation Licence files e.g. PDF files into the Historion database, by file path.

The process of searching, opening, editing and saving changes to Wearer details through various pages, is not optimised for updating bulk sets of Wearer data. Populating a spreadsheet with data is significantly faster and more efficient than updating Wearer records by using application pages.

The ability to paste data updates into the template in bulk is further optimisation for updating large numbers of Wearer records. Using Excel, external data from other sources (such as exports from a HR system) can be pasted in bulk into the template for subsequent importing into Historion.

The typical process for using the bulk Wearer Data Import Utility involves eight steps, outlined below;

Each of the steps involved, outlined above, is explained in detail in the pages that follow.

Wearer Data Import Utility          Step 1          Import Dose Readings 

Basic Wearer information is added to Historion when Dose Reports are imported and Wearers are first new to the Historion database. The Data Name (or enough name type fields to be sufficient for Historion to build a Data Name) Wearer Number, First Name and Surname are usually available. 

Most Dosimetry Service Providers do not include more data that this against each of the Dose Readings reported. This means that apart from basic Wearer name and number (placeholder information sufficient to identify the subject of each Dose Reading), the remaining Wearer demographic, classification, address and licensing types of fields are usually not included.

Significantly the Date of Birth and Sex of the Wearer are typically not included with Dose Readings, these data values are more sensitive. The assumption is that the Dose Readings would be matched to other Wearer data by the customer, if needed. This is where Historion comes in, to fill in data gaps.

The initial process of importing Dose Readings which adds Wearer place holder data is described in this document under Importing Dose Readings.

Wearer Data Import Utility          Step 2          Identify Missing Wearer Data 

Assuming the requirement is to update existing Wearer records with further data, there are four main ways to assess which Wearer fields most need to be updated across the full set of Wearers. They are;

1. Use the Wearer Search utility (shown below), select [Show All] then export the resulting Wearer list that is displayed to a new Excel workbook using the Export icon. After exporting the data, examine the various data columns for incomplete Wearer values that might be required.

Note that the Wearer Search results can be simply, generally checked, by using the vertical scrollbar to scroll through the list of Wearers and assess data completeness in main fields of interest.

                    Export to Excel                                                Show All button                  Missing Data Values

2. Use applicable Wearer Search Utility filter fields to search for (Not Selected) data scenarios. The drop down lists in the Wearer Search utility each have the (Not Selected) item at the bottom of the list. This is a special list choice which Historion uses to search for Wearer records not having a selection in the field.

3. Observe the Vital Data Graph results on the Home tab and using the Quick Navigation Links to search for Wearers missing critical demographic, classification, address or identifier details.

4. Export the full Wearer data table to CSV file (System Administrator users only) from the Reports tab, Extract feature, Wearers option. Examine the Wearers.csv file export created for missing values.

Wearer Data Import Utility          Step 3          Obtain the bulk Wearer Data Import template 

The Wearer Data Import template can be downloaded from: Wearer Data Import Template. 

The template is a Microsoft Excel spreadsheet with some controlled drop down values. To use the Wearer Data Template Microsoft Excel must be installed on the user’s workstation.

Historion provides a direct link to the above URL via the Download Wearer Data Import Template link at the lower left corner of the Wearer Data Import Tool page, shown below. The Wearer Data Import Tool is accessed via the [Import Wearers] icon in the [Wearers] ribbon tab;

                                                            Wearer Data Import Template download link

Please contact Historion Support to receive an emailed copy of the Wearer Data Import Template if there are any problems experienced with downloading the template.

Wearer Data Import Utility          Step 4          Populate the Excel template Wearer matching fields

After saving the template to a local folder, simply fill the template in with available Wearer data;

Please note the following Excel behaviour associated with completing the Wearer Data Template;

Column headings and default order match the fields and tabs in the Wearer Details page.

•Cell colour coding communicates the type or disposition of each column, explained below

•All yellow columns are mandatory, required for matching to existing or adding new Wearers

•All blue columns use controlled drop down lists with a specific range of values allowed

•Peach coloured columns are date fields and require a date value per the DOB example above

•White cells can have optional free text values

•As data is recorded in cells with coloured backgrounds the background colours change to white. This emphasises empty cells with remaining colours.

The Email column (Excel column M) is separately validated for correct email addresses when the Excel spreadsheet is used to import the Wearer data into Historion.

In addition to the above some operational points associated with completing the Template include;

Columns that are not mandatory and not required can be hidden if desired but not deleted. If columns are deleted from the template it will fail structural validation on import.

Columns can be re-ordered. Validation results returned will be in the Users column sequence.

Additional columns of data can be pasted into the template, such as working data or columns copied from other system or spreadsheets (or new columns inserted) as long as the required starting set of 54 columns are retained.

Wearer Data Import Utility          Step 5          Populate remaining Excel data values as needed

As long as the yellow mandatory columns are completed the User is free to pick and choose which Wearer data columns will have new data values and which will not.

Cells that are not mandatory and not completed are ignored and any existing data is not changed. Empty cells will not result in Historion data being cleared out (there are two exceptions to this, below).

When a row results in a new Wearer (not an update to an existing Wearer) then some Wearer values (such as Wearer Type “Individual”) will use standard Historion defaults if Excel equivalents are blank.

If part of a Street Address or Postal Address is changed, the whole street or postal address will be changed, as a set, to include the values or absence of values used in Historion. In this instance empty address values will clear out values in Historion as addresses must be updated as a set. The Wearer Data Import tool displays a red cautionary warning describing this;

Controlled value lists remain visible (they are used I Excel as data validation reference ranges) but are password protected to ensure uncontrolled changes are not made.

The ranges available in the various drop down cells match ranges used in Historion and are separately validated on import. If new data fields and changes in contents are required over time then a co-timed update to both Historion and the Wearer Data Import Template will be issued.

The lookup lists are visible for reference and value domain information. An example is shown below;

Wearer Data Import Utility          Step 6          Import Updated Wearer data via the Spreadsheet

After completing the mandatory matching columns and any other required new values in the Wearer Data Import spreadsheet, the results are imported into Historion via the Wearer Data Import Utility in the same location as the link to download the empty template. Close the file before trying to import.

Select the tool via the [Import Wearers] icon in the [Wearers] ribbon tab;

                                                                                          Link to Import new or updated Wearer data

Click the Select Completed File & Import Data link to display a file selection prompt. Navigate to and select the required, prepared Wearer Data Import file then Click [Open] to commence data importing;

Wearer Data Import Utility          Step 7          Work through any validation messages displayed

The Wearer Data Import Utility will execute the following sequence of twelve validation steps and checks. During this time a progress meter is displayed. If any of the steps fail validation Historion will stop the processing and display the validation window, which will indicate what the issue is.

An example failed validation message is provided on the following page. The import strictly will not proceed until all validation phases described below have been passed. This is to protect the integrity of the data update process and to ensure accurate and reliable outcomes are achieved.

Wearer Data Import Validation Steps:

1Try to open the file (invisible Excel session) Excel must be installed. Must be an Excel file.

2Ensure the file is not already opened for write access, meaning it is currently being changed.

3Check there are Wearer records in the Excel file (not an empty template, selected in error).

4Confirm all of the 54 column headers required are present in the import file.

5Confirm none of the 54 columns have been duplicated, known columns must be unique.

6Confirm mandatory Wearer matching fields are present for all Wearer rows. The fields are; Data Name | Wearer No. | Provider | Surname | First Name

7Validate controlled value fields (those with drop down lists) against their reference ranges.

8Validate date fields contain date values and pass reasonableness tests (no future DOB etc.).

9Validate Name fields do not contain characters not found in names ($#@%^{}[);[ etc.

10Validate Email Addresses are in valid Email address format; mailbox@mailserver.domain 

11Validate any Licence file paths indicated are in correct formats for file paths.

12Validate any Licence file paths indicated have files physically present in their destinations.

After validation the Wearer Data Import Tool proceeds with;

•Updating Wearer records that have changed for existing Wearers

•Adding new Wearer records not already present

•Logging record updating and adding outcomes to display at the end of the import process

•Displaying final results in a log outcome window for saving to text file or Excel
 

 After successfully importing Wearer data and updating records it is always a good idea to retain the Excel log of the completed import process for future reference.

File Validation steps are undertaken first.

When a file validation step produces a fail outcome, Historion shows the reason and disallows the commencement of the Wearer data import process. Two common failed file validation examples are;

The selected Wearer Data Import file is still open in Excel;

One or more of the columns in the template have been deleted (hiding columns is OK, not deleting)

Historion cannot continue with importing and updating Wearer data in the event of failing a file validation check. This is to protect from introducing new errors to existing data and to ensure a smooth update process.

Content Validation steps are undertaken next.

When a Content Validation step produces a fail outcome, Historion shows the reason and disallows the commencement of the Wearer data import process. Content validation outcomes are gathered together and shown, with potentially multiple field outcomes per Wearer.

Multiple failed Content validation of common examples are shown below. Multiple failures per Wearer in the first import pass re not uncommon;

Historion cannot continue with importing and updating Wearer data in the event of failing a Content Validation check. This is to protect from introducing new errors to existing data and to ensure data integrity is preserved by the update process.

If the Export to Excel Log feature is used after Content validation failures, a new validation log is populated in a new Excel spreadsheet with a column for each field failure by Wearer, as shown below.

        Note; Centres, Organisations, Business Units and Site Locations are matched by name 

              (Centre Number in the case of Centres) and their exact names must appear in Historion 

              already, to not fail content validation.  Add these items as needed before importing any

              Wearer data that references them.

Wearer Data Import Utility          Step 8          Confirm Wearer Data Updates/Addition Succeeded

The final step is a repeat of Step 2 Identify Missing Wearer Data. Re-running this step and examining data completeness in areas that should have been updated or added will confirm the success of the Wearer Data Import Utility’s processing.

In addition, to reviewing actual data outcomes, exporting the final Data Import outcome log to Excel will show the actions undertaken for each Wearer and for each field. The log could be important in keeping a record of updates and confirming data updating work tasks completed.

When the Wearer Data Import process has completed, a summary of outcomes at the Wearer level

is displayed. This summary can be copied to the Clipboard using the [Copy Text] button and pasted for safekeeping, such as in notepad or an email, then saved. The displayed text, only, is copied

Export detailed log to Excel feature

The Export icon populates a detailed log of the update outcomes into a new Excel spreadsheet.

An example is provided below.

     After successfully importing Wearer data and updating records it is always a good idea to retain the Excel log of the completed import process for future reference.

Wearer Data Import processes Detailed Outcome log exported to Excel;

The Detailed Excel Log provides an outcome for each Wearer (Column C) and from this column onward an outcome for all fields in the Wearer Data File used. The results after the summary Outcome column are displayed in order of the columns as they appear in the Wearer Data File used.

For each Cell in the Wearer Data File one of the following outcomes will be displayed (as shown in the example above). This provides a clearer understanding of what is occurring at the cell and field level;

Wearer Data Import Utility futures;

Historion will save a detailed update log of values added or changed for Wearers, automatically, in the upcoming Historion comprehensive logging feature.

An export option will be included in the Wearer search page that populates the latest import template with existing Wearer data so the user can effectively hit the ground running for data updating tasks.

The wearer import template will be stored in Historion in the templates table but not be visible in the same way as for Word templates are used.

We will add a field to the templates table that indicates whether the template is visible to Users or not. We will provide the ability to import a replacement Excel template instead of having to get IT to run a script. Using the imported template will be possible instead of just the current URL.

Copyright© Cybermynd Information Systems Consulting Pty Ltd