Update Unit Results

You can Import/Update unit results in bulk within VETtrak by making use of the Update Unit Results utility.

This functionality works by interpreting result data from a spreadsheet into your system, with tools to allow a user to configure how VETtrak will interface with the spreadsheet data, including a robust method of translating what spreadsheet column applies to what VETtrak field.

• Using the Update Unit Results Utility
• Preparing the Spreadsheet File to Import

Using the Update Unit Results Utility

To start the import, go to Utility > Update Unit Results:

This will display the Unit Result Updater wizard:

1. Layout File
   • Layout File field displays the currently selected Layout file.
     
     
     Note: VETtrak has access to a Standard Layout File, titled UnitResultsDefault.vtlayout, that will be selected by default when this utility is first opened. This is an ideal option for new users.
   • Press Load to open the selected Layout File. This will populate the Import Spreadsheet Columns area to match the settings within the file.
   • Press Save to keep any changes made to the Layout File after customising it. Changes are made using the Available Fields/Import Spreadsheet Columns areas.
   • Settings provides access to additional options for interpreting your spreadsheet. By default, the settings should be optimal for a painless import. This will mostly depend on the type of spreadsheet being used.
     
     
     
2. Available Fieldsprovides access to a list of VETtrak fields that can be used to interpret your spreadsheet data to be ready for import.

   Be selecting a list of Fields present in your spreadsheet, VETtrak will then be able to recognise the values in the next step, validating them ready for import.

   You can select columns in the left hand side by navigating to the required Field name, and either Clicking and Dragging the item into the right hand side, or simply Double Click to immediately add it: 

   
   
   
3. Import Spreadsheet Columns
   • Click theto change order options, and drag records up or down to reorder them within the list.
   • Column displays the VETtrak field selected.
   • Operationidentifies what VETtrak should do with this information when reading your spreadsheet. Depending on the type of field selected, only specific options may be available. These options include:
     • Match indicates that VETtrak will attempt to match the fields information to a record within VETtrak.
       
       For example, Matching a Client Code within your Spreadsheet to the Client Code in VETtrak.
     • Update specifies that the details within your spreadsheet should replace the data within VETtrak.
       
       For example, Updating a Unit Result within VETtrak to the value in your Spreadsheet.
     • Update (can be set blank) will update a record as above, but can use an empty field in the spreadsheet to update the record within VETtrak to be blank (if VETtrak allows).
     • Ignore indicates that VETtrak should not validate the data in this column at this time.
   • Delete removes this field from the Import Spreadsheet Columns list.
   • Add ignored column allows a user to create a value that may match data within your Spreadsheet that is irrelevant to VETtraks needs, so as to avoid forcibly removing it from the spreadsheet. For example, external record details that are useful when viewing the spreadsheet on it's own, but serve no purpose within VETtrak for the sake of importing.
4. Press Next to review your Spreadsheet data to import/update.

This will display the second page of the wizard:

1. Import File displays the currently selected Spreadsheet file. Press the folder button to browse for a valid Spreadsheet document, then press Load and Validate to check the data.
2. The Data Grid will display the information interpreted from your Import File, with row headers and columns containing the data.
   • Use can be toggled to indicate if that record should be imported or ignored.
   • Status will display a coloured indicator of whether the data present is suitable to be imported, or has issued that need to be addressed. The key for this is displayed on the right hand side.
     
     If you click a coloured Status box, it will expand:
     
     
     This will display detailed information about how the spreadsheet data has been interpreted, and will give a rundown on the change that will take place if you process the update.
     
     Alternatively, if there is an Error/Warning it will provide a detailed explanation of what is incorrect:
     
     
     
   • Action indicates what should happen if this data row was to be imported, whether a VETtrak record will be Edited or No action will take place.
   • Open provides a range of direct access options to specific data in VETtrak associated with this record, such as editing AVETMISS Details, Training Plan information or Recording Results.
3. The Status panel displays a key of the status of your data, showing what each particular status colour means and the number of records with that status.
4. The Actionarea indicates the action that will be performed when the record is imported.
   • No Action: Row has no import action and cannot be imported.
   • Edit: Row will be used to edit an existing record.
5. Press Import to process the Import/Update of records.
6. If required, you can Export an Excel spreadsheet document of the values displayed in this area, including column headings. This can be used to export a blank formatted spreadsheet, ready to populate for import - or a record of the data as it appeared prior to being imported.
   
   

 Preparing the Spreadsheet File to Import

The VETtrak Unit Result Updater will require a Spreadsheet that matches your Layout to work - we recommend using a Microsoft Excel 97-2003 Worksheet (.xls) file format to make sure that all data is kept intact, as some Excel formats can strip leading zeros that are required for details such as a Clients VETtrak Code.

You can download a Spreadsheet that matches the VETtrak Default Updater Layout here: Unit Result Updater Template

Using the Default Unit Result Updater Template as an example, the excel spreadsheet could appear like the following, including the information needed to identify the records to be updated within VETtrak, as well as the new value that will be inserted (such as Unit Results and Dates):

To better understand how these fields correspond to your VETtrak data, here is an example of a persons enrolment that you may intend to import results for, with the related fields highlighted: 

The Unit Start, Unit Finish and  Result Name or Outcome Code fields are entered based off the information you wish to import regarding the Students training activity.

Within VETtrak this spreadsheet document would appear like so after validation, prior to import:

And upon importing this data, your Unit Result Record would update using the values specified, appearing like so: