How do I import Invoices, Sales Orders or Quotes?

Before you start:

The below FAQ is quite lengthy, we know, but it is vital that you read each section and follow the guidance exactly. 

Before you start importing your Invoices, Sales Orders, Quotes you will need to make sure you have prepared your import file.

The standard import file can be prepared in much the same way as any standard import file for OpenCRM, bearing in mind the requirements for date format, etc.

Each record in the import file will need a unique reference. Usually an invoice will have an invoice number - this can be used as this unique ID.

In the article below we refer to Invoice import, however the steps and process for Sales Orders and Quotes are essentially the same.

We do advise that you test with a small number of records to ensure you are happy with the results before you do your full import. Remember to save your mapping to save time when you do your full import.

IMPORTANT: If you perform an update import any existing product rows on the record are removed and replaced by those found in the new import file. If you do not include product row data when performing an update import your existing product rows will be removed.

How do I include product lines?

To include product lines, add the product fields to the main import file and repeat the invoice data for each individual product line. A basic example is below:

The above example will import a single invoice (556677)  with two products linked.

More details on mapping product fields are below.

Getting Started

Now that you have prepared your import file, you can start importing.

If you have permission, then an "Import Invoices" link will show at the bottom of the Invoice Home/Search page. Click this link to go to the Import Invoice page.

* On an Invoice Import, you cannot deselect the "Update Existing Records" flag. Due to the way the invoice import links products, this flag is required for the invoice import.

Clicking "Help" will bring you back to this FAQ.

Invoice Numbers and External IDs

As stated above, you need to provide a unique ID for each invoice to import. This is so the products can be linked to the invoice as part of the import. This can be done by mapping any of the following:

• Invoice Number and External ID. In this instance The OpenCRM invoice ID will be linked to the ExternalID that you provide. To update these invoices at any later date, you will need to map the External ID to the same ID field in your data or you may end up with duplicates
• External ID - If you only map the external ID and do not provide an invoice number, OpenCRM will assume that the Invoice number is the external ID and so will populate the invoice number with the External ID
• CRMID - This is only relevant when updating existing invoices. If you know the CRM record ID, you can provide this here, you will need to map the invoice number separately

Some further notes:

• If you do not map the "Subject" field, the invoice subject will be set to the invoice number
• Any non-numeric characters will be stripped from the Invoice Number as part of the import, though if the Invoice Number is also used for the External ID, the External ID will use the full alphanumeric value.
• The Invoice Number field needs to be mapped on an import or the invoices will import with an invoice number of 0.  The Invoice Number column can either have a specific invoice number set or, if it is set to "auto" on in the import file, it will allocate an invoice number from OpenCRM
• Make sure all Mandatory Fields are included in your CSV file

Invoice Tax Details

You can set the Invoice level tax rate by mapping the following fields (please note this is only relevant if you are NOT using line level VAT which calculates VAT at product line level). Most new OpenCRM systems use line level VAT by default.

• Tax (VAT) Class (External) - If your accounts package exports your invoice data with a "tax class" or "vat code" or similar field, you can map this to this field (e.g. Sage has T1, T2, T3 etc tax codes). This does need a small piece of configuration on your OpenCRM system in order to map this tax class to the relevant vat rate in OpenCRM. Please contact support for this.
• Tax (VAT) Rate Percentage - import the percentage direct.

* If no tax data is imported, the system will default to the default tax rate.

* If both fields above are set, the value found from the Tax (VAT) Class (External) will be used

* If Charge VAT is not imported, the system will default to this being turned ON.

Importing Linked Entities

Linked Companies

Map to either of the following fields to link to a company in OpenCRM:

• Company ID - This must be the OpenCRM record ID
• External ID - If you previously imported company data with an External ID field, then you can provide this here
• Company Accounts Ref - if you have linked your company data in OpenCRM to an accounts reference from your accounts package, then you can use this to make the link
• Company Name - If you do not have a unique ID, you can provide a company name and the system will search for a link based on this, but be careful as if you have more than one company with the same name (e.g. Sub-offices of a main office)

If no match is found then a new company record will be created with the company name, and any address information provided in the import.

Linked Company - Address

If neither shipping street nor shipping city are provided as part of the import, they will be populated from the linked company

If neither billing street nor billing city are provided as part of the import, they will be populated from the linked company

Linked Contacts

You can map to an existing Contact record using either their First and Last Name, CRM ID or External ID. Contact First Name, Contact Last Name and Company Name are the fields used to check for existing records. If a match is found, the new Sales Order will be linked to the existing Contact. Otherwise it will link the Order to a brand new Contact record, with the information populated as submitted via the Import (First Name, Last Name, Company, Phone Number, Email Address).

Linked Projects / Quotes / Sales Order / Campaign

As with companies, you can provide either a CRM ID or an External ID for each of these links. However, unlike the Company or Contact link, a new record will not be created if a match is not found.

Linked Products

As described above, Include all linked Products on the same line as the invoices, duplicating invoice lines where there are multiple products linked. Map the fields in the interface using the fields labelled "Product Line-> .... "

Note that if you run a second import with the same Invoice ID, all product lines will be removed from the existing invoice and will be replaced with those from the current import. This means that you can update both the invoice and the product lines.

You can link to existing products by mapping any of the following fields:

• Product Line -> Product ID (External) - If you have previously imported products with an External ID mapped, you can provide this here
• Product Line -> Product ID - You can provide the OpenCRM Record ID
• Product Line -> Product Code - Provide the product code as set in OpenCRM

If no matching record is found then a new product is created as an Invoice cannot be created without product lines.

When a new product is created:

• If no product code has been provided in the import, then the External ID will be used  to set this field
• If no External ID has been provided, then the Product Code will be used to set this field
• If no Product Name is provided, then the Product Code will be used to set this field

Quantities and List Prices

Make sure that the List price imported is a PER ITEM price, NOT the total value of the line.

E.g if your invoice line was for 2 Widgets at £40, then the List Price figure to import is £40 NOT £80. As part of the import, the overall total will be calculated by multiplying the list price by the quantity.

Tax on Product Lines

There are a number of ways to set the tax value used on product lines using the following field mappings. These are in order of priority, so if you supplied all of these, the first option listed below would be the one used.

• Product Line -> External Tax Class - As with the invoice level tax, you can pass in an external tax code provided your OpenCRM system has been configured to accept this - contact Support.
• Product Line -> Tax Class - Provide a standard OpenCRM tax class, and the VAT percentage will be taken from that.
• Product Line -> Tax Rate (Percentage)

* If no tax details are supplied, and the product already exists, then the tax class and rate set on the existing product will be used, otherwise a new product will be created and the default tax class will be set on the product and this will be used on the invoice line.

* Dependant on which value is imported, the other values will be generated automatically so Tax Class and Tax Percentage fields will always be complete in OpenCRM.

Invoice Totals

It is not possible to import into the Invoice total fields. The invoice totals are calculated based on the product lines and tax values imported. This is to prevent an inconsistency which could be caused if total values were imported and then recalculated to a different figure when an invoice is edited in OpenCRM.

The tax value will be calculated and depending on the same system wide OpenCRM setting as is used for the standard Invoice interface, this will either be calculated per line item or on the overall totals.

Margins

These are also automatically calculated as part of the import - including the line level and total margin figures.

Discounts

It is possible to import discount on the product lines by importing into one of the two fields:

• Product Line -> Discount Percentage - If you import into this field, the discount amount per line will be calculated by taking this percentage off the List price imported
• Product Line -> Discount Amount - if this field is imported (and no discount percentage is imported), then the discount amount will be deducted from the List Price imported.
• If both of the above are imported, then the discount percentage overrides the discount amount.

Currency / Multi-Currency

It is possible to set the currency of the invoice by importing into the "Currency" field.

If this is not set, it will default to the GLOBAL default (NOT the company default)

It is also possible to import an external currency code (e.g. Sage currency code) using the "Currency (External)" field. This can be mapped to the correct OpenCRM currency code. Please contact support to ensure this mapping is set up before importing.

If you need this feature, please contact Support.

Delivery Amount

When importing you will see a new field called "Delivery Amount" when you're mapping the fields.

This will be a separate column on your spreadsheet that holds the delivery amount for each individual product. 

When you map this field, you will get a single Product that is "Delivery Amount" that will show a total of this column for all the products you imported.

Set up

The field you will need to map to is: 

In order to use this, you will need to have a specific Product that you use to hold the delivery amount designated. To set this up, you will go to Settings->Additional Settings and add the CRM ID of the Product you wish to use.

You will also need to choose whether to add together the delivery cost of ALL the products or just the last imported.

Example

This is a bit of a tricky one, so we'll use an example--this one uses the "Last Imported" setting. 

Below is an example of what your .csv file would look like before you imported it.

This is what it would then look like after you imported it:

You can see that only the delivery amount of the second item is included on the Invoice.

Additional Duplicate Checking on Sales Orders

Please refer to this FAQ for added information.