Payment Status Importer - MYOB AccountRight

For users who wish to record invoices in VETtrak and manage their payments in MYOB this article describes how the payment status of the invoices can be updated in VETtrak.  MYOB can be used to export a file and that file can be used to import into VETtrak using the payment status importer.

Export from MYOB

Open MYOB and use the Import/Export Assistant found under the File menu.

1. Select Export data
   
   
   
2. Set the Data Type selections to pick up the Services Sales. This allows invoices to be captured in a date range.
   Export: Sales
   Sales type: Service Sales
   Status: All Invoices
   Date from/to: the date range you want to cover
   Identifiers: can be left blank
   
   
   
3. Set the Format to separate data using commas and exclude the field headers.
   
   
   
4. Add the Invoice No., Amount, Tax Amount and Amount Paid fields to the Fields to export by clicking the Add > button.
   VETtrak is also able to import an external invoice number and an external payment date so if you wish to add any other MYOB fields that represent these fields in VETtrak add them to the Fields to export list to import these into VETtrak also.
   
   
   
5. After clicking the Export button choose to save the file however change the .TXT to .CSV in the file name.  Renaming this changes the file to a CSV format which VETtrak will be able to recognise when importing the file.
6. As the file does not yet have a payment status the file will need to be edited to have this added.  Open the file in Microsoft Excel then remove the first row.
7. MYOB includes blank lines between each record in the CSV when exporting the file.  These will need to be removed to avoid any conflicts when importing the file into VETtrak.  Highlight all the cells in the spreadsheet (hold ctrl and press A on your keyboard), go to the Data tab and click Sort.
   
   
   
8. Now that all the blank lines have been removed click on cell E1 and paste the following formula into the field.
   

   =If(B1 > 0, IF(D1 = (B1+C1), "Paid in full", IF(D1 > (B1+C1), "Over paid", IF(D1 > 0, "Part paid", "No payments"))),"")

   Click and drag the small box in the bottom right of this cell down to populate the formula on each row. This formula will then display a payment status for each row.
   
   
9. The file can now be saved and is ready to be imported into VETtrak.

Import into VETtrak

Open VETtrak and follow these steps:

1. Go to Utility - Invoicing utilities - Import invoice payment statuses.
   
   
   
2. Select the file under Spreadsheet to import by clicking the ... button to browse for the file.  Click the Load button to load the spreadsheet into the grid and set the Invoice Number to Col 1 and Invoice Status to Col 5.
   
   If you have chosen to include more information such as the External Number and Inv Status Date set these columns appropriately.
   
   
   
3. Click Next.  If you have been using the standard invoice numbers in VETtrak your numbers will have leading zeros.  Excel strips these leading zeros out of the CSV however ticking the Pad Invoice number to leading zero format box helps match the invoice numbers up in VETtrak to the spreadsheet.
   
   Match the statuses in the Invoice Status and Mapping To columns and then click Validate.  The invoice statuses should now be ready to import, click Next to continue.
   
   
   
4. The invoice statues will now appear in VETtrak and can be seen by editing the invoice and looking at the disabled fields at the bottom of the invoice wizard.  If the external invoice number and/or external payment date has been imported these will also appear in these fields.