Import Payments

You can Import Payment Records in bulk within VETtrak by making use of the Import Payments utility.

This functionality works by interpreting payment 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 Import Payments Utility
• Preparing the Spreadsheet File to Import
  
  

Using the Import Payments Utility

To start the import, go to Utility > Finance Utilities > Import Payments:

This will display the Payment Importer wizard:

1. Layout File
   • Layout File field displays the currently selected Layout file.
     
     
     Note: VETtrak has access to a Standard Layout File, titled PaymentsDefault.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 Fields provides 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 an Invoice Number within your Spreadsheet to the Invoice Number in VETtrak.
     • Update specifies that the details within your spreadsheet will be created as data within VETtrak.
       For example, A Payment Amount within your Spreadsheet being applied to an Invoice in VETtrak.
     • 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.
     
     In the example above, a non-flagged standard warning is displayed mentioning that the Default Payment Location will be used, however the status is reflected as 'Okay' - meaning the data will still be imported.
     
     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 Added, Edited or No action will take place.
   • Open provides a range of direct access options to specific data in VETtrak associated with this record if applicable.
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 Action area indicates the action that will be performed when the record is imported.
   • No Action: Row has no import action and cannot be imported.
   • Add: Row will be used to add data to an existing record.
   • 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 Payment Importer 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 VETtrak Codes.

You can download a Spreadsheet that matches the VETtrak Default Updater Layout here: Payment Importer Template

Using the Default Payment Importer 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 values that will be inserted (such as Invoice Number and Payment Amount):

To better understand how this Invoice information will correspond to your VETtrak data, here is an example of an invoice with no payments that you may intend to import details for, with the Invoice Number highlighted: 

The External Number, Recorded Date, Payment Type and Amount fields are entered based off the information you wish to import regarding the Payment.

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

And upon importing this Payment data, your Invoice would update using the values specified, appearing like so: