Export invoices and import into MYOB AccountRight

You can import Case Manager invoices into MYOB AccountRight using its import function. This creates MYOB summary invoices, with linked invoice numbers.

To do so, follow these steps:

  1. Create a journal entry that includes the desired invoices.
  2. Use the Report facility to export the journaled invoices to a text file.
  3. Import the text file into MYOB.
  4. Check the import log file and the invoices created.

Full details of the steps are available below.

Note that MYOB can only import invoices with this feature and you should enter adjustments and other accounting transactions manually, see Other notes below.

Installation and setup

To use this function you first need to follow a simple setup procedure to create a custom report

Follow the same instructions as at Custom report utility.

  • At step A select the select the usp_ExportJournalToMYOBAccountRight stored procedure.
  • At step B set default values for the journal number and the other export options. These are explained at Export options below.

  • At step C name and describe the report for your users.
  • At step D regulate access to it with permissions.

Mapping of invoice information between systems

Below is a Case Manager invoice that has been imported into MYOB.

The table indicates where elements of the MYOB invoice are derived from. The Case Manager invoice is displayed beneath.

  MYOB Notes
A The description contains the Case Manager case's client and the case's claim number in brackets.

The claim number is found in the case details.
B The customer is the equivalent of Case Manager's invoice contact's company (see invoice below).  
C The MYOB invoice number is the same as the Case Manager invoice number (see invoice below).  
D

The MYOB invoice figures, i.e. total amount and total tax, come from the totals in the Case Manager invoice (see below).

An imported MYOB invoice cannot have separate line items. It can only have one tax rate.

This is a significant issue for Case Manager invoices with mixed tax rates, see Mapping notes below.
E The MYOB account number is derived from the invoice case category's shortcut:

The shortcut is found at the category's settings.
F The MYOB salesperson can be set as the case's assigned user if desired, see Export options below.

The assigned user is found in the case details.
G

The MYOB job can be set to the case office's bank account code if desired, see Export options below.

 

Be mindful that if you store your MYOB job fields in Case Manager's office bank account Code field, the field cannot also be used to store a BSB number for use in invoice templates, see Adding banking details to invoice template.

The case's office is found in the case details.

The office's bank account code is set at the Office List.

The Case Manager invoice is:

Mapping notes

  1. Do not use this technique to import an invoice that contains line items with different tax rates.

    2. MYOB cannot import invoice line item details and can only have one tax rate for an imported invoice.

    When Case Manager exports a mixed tax rate invoice, it accurately exports the invoice's total and total tax amount. However it must set the tax rate to GST if at least one line item contains tax.

    Thus, MYOB imports such an invoice with the correct invoice total and total tax amount. However, there is no indication in MYOB of the invoice's different tax rates This will lead to subsequent errors when managing tax reporting in MYOB.

    You could choose to exclude such invoices from the export and to add them to MYOB manually.

  2. A MYOB customer name that matches the invoice contact's company name (B) must already exist in the MYOB database before you perform the import. If it doesn't, the import will fail.
  3. It is essential that Case Manager category shortcuts are set up to match the MYOB account numbers (E above); if not, the import will almost certainly fail.

    4. For example, when the appropriate MYOB account number for an invoice is 4-9990, the shortcut for the case's category should be 4-9990.

    You set this value for the category at the Category List in the General tab.

    You may want multiple categories to use the same MYOB account number. Since you cannot use the same shortcut for more than one category, the solution is to add extra characters at the end of each category's shortcut. The export only uses the first 6 characters of the shortcut, so it will allocate the same account number to invoices with each of these categories. In the example above, the invoice case categories could have shortcuts of 4-9990a, 4-9990b, 4-9990c, and so on. Each invoice would have a MYOB account number of 4-9990.

MYOB does not prevent you importing the same invoice more than once, even when you select its option to reject duplicates. We recommend that you set up business processes so that you do not import the invoices in the same journal entry more than once.

The process

Step 1 create journal entry

When you create a journal entry, in effect you take a snapshot of transactions that also 'locks' them (see Changes to journaled transactions if further details are required).

Create a new journal entry and specify the invoices to include in it by date, category, and so on. Make sure that the journal only includes invoices:

Full instructions are available at Create a journal entry.

Step 2 export invoice information

Select View > Reports from the main menu and click Export Journal To MYOB AccountRight.

  1. Enter the journal number at Journal.
  2. Click this checkbox if you want to set the person assigned to the case as the MYOB invoice's salesperson, see Export options below.
  3. Click this checkbox to set the office's bank account code to the MYOB invoice's job, see Export options below.
  4. Click Text File.
  1. You will be asked to provide a name and location for the text file.
  2. Click Save.

Step 3 import invoices into MYOB

The import at MYOB is performed via the Import/Export Assistant.

Select File > Import/Export Assistant from the main menu to start the import.

  1. Click the Import data checkbox.
  2. Click Next.

  3. Select Sales at the Import dropdown list.
  4. Select Service Sales at the Sales type dropdown list.
  5. Click Browse to locate the saved file from step 6 in the previous section.
  6. The File type specifications are complete. Click Next.

  7. Click the Tabs check box.
  8. Click the Headers or Labels check box.
  9. Select Reject the import data from the dropdown list.
  10. Select Co./Last Name from the dropdown list.
  11. The Format specifications are complete. Click Next.

  12. Click Auto Match to automatically match the fields in your import file with MYOB fields that have the same name.

When you have done this, each of the import fields on the left has a link icon next to it indicating that it has been matched to a MYOB field. It is listed under the Matched Import fields column.

An example is highlighted below:

    Check that the Case Manager fields are matching correctly with the MYOB fields.

  1. The Match specifications are complete. Click Next.
  2. You may choose to back up your database before importing, especially when testing this process.
  3. Click Import.

  4. The results of the import are displayed and the import log should be examined.

    5. Note that for every record successfully imported, MYOB will say in the log file that one record was skipped. You can ignore this. The main thing to check is that there are no warnings and no errors.

  5. You can close the import assistant or go back to the beginning and import more invoices.

The MYOB invoices have now been created. It's a good idea to confirm that the imported invoices match those in the journal.

Export options

There are two options where you specify whether you want to include certain Case Manager information in the file that will be imported into MYOB. You should customise these to suit your own requirements when you first set things up. Note that users can change the default values when they perform the export. Be careful to set each option to either ticked, i.e. set, or empty, i.e. not set.

  1. If this option is set, the person assigned to the case in Case Manager becomes the sales person for the MYOB invoice.

    2. When this person does not exist in MYOB, the invoice is imported successfully into MYOB, however its salesperson field is left empty and you get an error in the log.

    To avoid the warning, a good default setting is not to tick this option.

  2. If this option is set, the export includes the Case Manager bank account Code field as the invoice's MYOB Job field. It uses the details for the office linked to each invoice.

    3. Notes:

    • If you have a value in this field in Case Manager and no matching job exists in MYOB, the invoice will be imported successfully into MYOB. However, as above, its job field is left empty and you get an error in the log.

      • To avoid the warning, a good default setting is not to tick this option.

    • Be mindful that if you store your MYOB job fields in Case Manager's office bank account code field, the field cannot also be used to store a BSB number for use in invoice templates, see Adding banking details to invoice template.

Other general notes

  • These instructions are for MYOB AccountRight. MYOB Essentials cannot perform imports.
  • It is recommended that when you create the journal containing invoices, you also create a separate, matching journal containing the adjustments for this period. You can then edit existing invoices in MYOB to correlate with these adjustments.
  • As for payments and any refunds, you may choose to process payments completely in MYOB, rather than in Case Manager. You can also enter payments in both MYOB and Case Manager.

    • Your choice depends on your business processes and the accounting method (cash/accruals) that you use.

  • Do not re-order, re-name or remove columns in the text file because MYOB will not be able to import this file correctly.
  • Make sure that the Bill To company names are spelled the same as existing MYOB customer names.

    • If a name is spelled differently or if it doesn't already exist in the MYOB system, the invoice import will fail.

  • Make sure that when you are importing invoices for a certain company (customer), you do not have more than one copy of this customer in your MYOB system. If you do, the invoices cannot be imported and you will be informed that more than one customer found. Making the copies inactive does not fix the problem. MYOB are looking into this issue, but meanwhile the solution is to change the names of the customer copies slightly so that they appear unique.

MYOB links

The MYOB import process is explained at http://help.myob.com/wiki/display/ar/Importing+data.

MYOB import specifications are at: http://help.myob.com/wiki/display/ar/Import+and+export+fields#expand-Servicesales.