Important : Please Read
There are a number of elements that you need to configure, all of these come with some level of caution and you should not attempt these without your Accountant or a Company Director taking responsibility for any decisions you make.
Software Add-ons Limited – The Home of OpenCRM are a certified App partner in the Xero marketplace and understand the development aspects of Xero Accounting Software but are not accounting specialists – you should seek independent advice from a Xero specialist when making any changes to your Xero Accounting installation and any integrations.
Integration between Xero and OpenCRM shares sensitive financial information which should be treated appropriately. When you push a new Invoice to Xero it has the potential to create duplicates if the original Contact information does not match, you should plan how you will integrate the two data sets to avoid any duplication arising. If you have any concerns about your own ability to setup the systems to work together please speak with your OpenCRM Account Manager to schedule some time with our professional services team.
Now that we have that over, on to the main article. In this FAQ, we're going to cover:
1. How to set up the Xero Integration, including:
a. Importing client data from Xero to OpenCRM and
b. Setting up your Products
2. Using the OpenCRM to Xero integration, including
a. Sending to Xero
b. Updating from Xero
c. How to handle payments
3. Some other options you have for managing this integration,
4. What to do if something goes wrong and
5. Some general notes you should be aware of.
We recommend that you read ALL of the below before you start using this integration, just to make sure it is all familiar to you.
Setting up the integration
The Xero settings page contains a link to the Xero page you need to go to to set up the Xero app. Currently only Public authentication is supported although the feature was originally written for private key authentication which means the process can be automated.
Note : Once this private key authentication is approved by Xero, automated invoice creation and balance updating will happen without user input.
To access the Xero Settings, go to the Invoice module, and click on the button to Connect to Xero:
Clicking this link will give you the below page. The Enable Integration box will already be ticked. If you untick this, it will remove your access to the Xero integration and you will need to contact OpenCRM to get this re-enabled. You will also need to have a minimum User level of "standard" in Xero. If you have access to "Invoice Only" within Xero, you will not be able to complete the integration.
Clicking Connect to Xero will take you to a Xero login page where you can then authorise your OpenCRM system to connect to Xero.
Once you authorise Xero to connect to OpenCRM, the page will refresh and you will find yourself back in OpenCRM with the consumer key and secret fields populated. This will turn on the connection. That connection will remain active for approx 30 minutes and is available to all users of the system in that time (each user does not need to connect each time).
Each time you enter the settings page, the saved connection is cleared and you will need to click Connect to Xero to reactivate the connection.
Tax table / rates can be associated with tax rates in Xero via the Settings page in OpenCRM, please see this FAQ for more information: https://opencrm.co.uk/crm-knowledgebase/kb6786897/Xero-Tax-Rates-Types.html
There is a background poll which can automatically send and update invoices to/from Xero but this will only work while the connection is valid. The connection expires every 30 minutes (approx), so it is actually quite unlikely this will be useful at the moment.
Importing Client Data from Xero into OpenCRM
If you have been using Xero prior to integrating with OpenCRM, you will need to run an import or an update import, in order to get the Xero Reference field populated on the Company record in OpenCRM. If you do not do this, you will be creating duplicate client records in Xero and invoices from OpenCRM will be linked to a new Contact record in Xero instead of the existing client.
The Xero Reference field is the unique identifier that is used to match up records in OpenCRM with existing data in Xero. This reference is not available on a standard Xero export or report, so you will need to use the tool built into the Xero Settings page in OpenCRM, as per the screenshot below:
This will create a csv file that looks like this:
Importing New Client Data into OpenCRM
If your client data is present in Xero, but not present in OpenCRM, then you can carry out an import, to create new Company records in OpenCRM. Click on the link from the screenshot above. This will perform an export of your client data held in Xero. They key columns to note are ContactID (= Xero Reference) and Name (Company Name).
As well as this key information the export provides details such as a Contact person, email address, telephone and address details. The export also provides numerous columns of information exported from Xero that you are not likely to need in OpenCRM e.g. "AccountsReceiveableTaxType" or "BankAccountDetails".
1. You need to decide which columns you wish to import. You may need to create custom fields for non-standard information, in which case please refer to this guide on how to create custom fields in OpenCRM: https://www.opencrm.co.uk/support/knowledgebase.html?kb=1131928&name=How-do-I-create-custom-fields-in-OpenCRM.html
2. Formatting the address columns in Xero to match the formatting in OpenCRM. Typically, you will want to import the address details into the fields Billing Address Street/ Billing Address City/ Billing Address State/ Billing Address Postcode/ Billing Address Country. So, you will need to concatenate AddressLine1 and AddressLine2 into one field, to populate the Billing Address Street field in OpenCRM. See this FAQ for details on how to concatenate two (or more) fields in Excel: https://www.opencrm.co.uk/support/knowledgebase.html?kb=734996&name=In-Excel-how-can-I-join-two-cells-into-one-(concatenate).html
3. Formatting telephone numbers to import into OpenCRM. In the Xero export, the phone numbers are split into separate columns for phone number/ area code/ country code, so you will need to concatenate the columns as per with addresses. Also, csv strips out formatting so numbers are likely to be missing leading zeros ie "0221" will display as "221". This FAQ explains how to restore and leading zeros that may be missing: https://www.opencrm.co.uk/support/knowledgebase.html?kb=1873178&name=How-can-I-add-a-leading-Zero-(0)-in-Excel-CSV-For-example-in-phone-numbers.html
4. Once you have formatted the data, you can proceed with the Import. You will need to import the Company details into the Companies module, and if you want to add the Contact details, you should import this to the Contacts module. Go to the relevant module and select the Import option
The key fields to map are the Xero Reference and Account Name. You can map the other fields as described above. once you have finished, select Import Now at the bottom of the import screen and that will launch the import and create the Companies in OpenCRM.
5. You can repeat the process to import into the Contacts module, and create Contacts that will be linked to the Company.
Updating Existing Client Data in OpenCRM with Xero information
The procedure for carrying out an update import is very similar to doing a new import - you pick the columns you wish to bring into OpenCRM. The difference is that at the bottom of the Import screen you select the option to Update Existing Records before hitting Import Now.
For OpenCRM to know which record to update, you need to add a column to the spreadsheet, containing the CRM ID from the matching record in OpenCRM. You can use the VLOOKUP function in Excel to match the Xero Reference to the correct company. You should be OK with using the Company Name as the identifier to match up records however if you have more than one Company with the same name you will need a more sophisticated solution (e.g. using the Company Name and Postcode as the unique identifier). This FAQ explains how to use VLOOKUP: https://www.opencrm.co.uk/support/knowledgebase.html?kb=1718709&name=How-can-I-copy-values-from-one-Excel-sheet-to-another-(VLOOKUP)-E-g-I-have-a-file-for-Companies-and-a-file-for-Contacts-and-I-want-to-copy-some-data-from-the-records-on-one-sheet-to-the-associated-records-on-the-other.html
Once you have matched up the Company with the relevant CRM ID, map the fields you want to update in OpenCRM. The key fields to map are the CRM ID field, as this is the look-up field that will be used to update the relevant record, and the Xero Reference, or ContactID field, which is the identifier that will be used to look up the correct Company record in Xero.
Setting up Products
When you push Invoices from OpenCRM to Xero, it will create the invoice and either link to existing or create new Companies and Products. The Nominal Information on the Products is used to map the Invoice as a Sale, so you should ensure that the Nominal Codes in OpenCRM are set up to match the values in the Chart of Accounts in Xero.
The default code in Xero is 200 for Sales. Note that if you have migrated Product data from another accounts package into Xero, you will need to make sure that you have set the Chart of Accounts in Xero appropriately, and that you have matched these with the Sales Nominal fields in OpenCRM.
Within the OpenCRM Nominal Code settings, you need to match the values used in Xero, in this case this means setting up a Nominal Code of 200. This can be done by going to Settings > Edit Picklist Settings > Edit Products Picklist > Edit Sales Nominal.
Once you have done this, you should do an update import or bulk update on your Products, to update the Nominal Code fields to contain the relevant information. If you are at all unsure about how to do this, please contact support or your account manager at OpenCRM.
If the values between the Sales Nominal list in OpenCRM and the Chart of Accounts in Xero do not match, you will get an error message when attempting to push Invoices from OpenCRM to Xero:
Using Xero Integration
On Invoice home screen, there are two buttons - "Send to Xero" and "Update From Xero". These buttons are only visible if integration is enabled, and the current connection has not expired.
If it has expired, a button "Connect to Xero" is displayed which takes you to the settings screen.
Send to Xero
This button will send selected invoices (and associated company and contact information) to Xero. It will send all invoices even if they have already been sent to Xero, so long as the outstanding balance matches the total amount. As soon as the outstanding balance is below the total amount (i.e. payments have been made in Xero), the invoice is no longer sent to Xero. The invoice status is ignored, so this will send Pending and Paid invoices so long as the outstanding and total balance matches.
Information synced from the Company record in OpenCRM into the Contact record in Xero includes Company Name, Xero Reference and Address details. On the Contact screen in Xero the Company name is displayed at the top, and Company Address/ Contact details are displayed down the right hand side.
As well as the Company, Xero also picks up details from the linked Contact - Contact Name and Email Address.
Update From Xero
This button will update selected invoice balances from Xero, and update associated companies with their Xero reference. It will NOT update company balances. It will only get updates for invoices not marked as Paid or cancelled and where the "Sent to Accounts" field is checked.
Once an Invoice has been updated from Xero, the status will be updated from Created to Live. The Invoice will also be marked as having been Sent to Accounts, with the Date on which it was sent, you'll this this information in the following two fields:
With this integration, it is important that the payment side is managed in Xero.
Once Payments (in Xero) have been logged, these will be reflected in OpenCRM if the Update from Xero option is selected. It will show part-payments and full payments, updating the status to either "Part Paid" or "Paid", and the Amount Outstanding field will be adjusted accordingly.
How does Xero handle payments, does it sync use the payments tab on OCRM?
No - we don't sync Xero payments to payment records in OpenCRM.
You make the payment in Xero, which updates the balance on the invoice in Xero which is updated in OpenCRM by the sync. No payment record is recorded in OpenCRM.
If you delete payments on OpenCRM does this update the "outstanding" balance on the invoice in OpenCRM?
Yes. It recalculates the outstanding amount based on the remaining payments against the Invoice. This would override the value that had been set from the Xero sync.
What about part payment? If an Invoice is marked as Part Paid in OpenCRM, what happens then?
The total value of the invoice would not match the outstanding amount and Xero would have no reference for a payment being made. Xero would just reject the record.
Ergo, the payments need to be made in Xero.
In the tools section, there are links to send all live invoices (that is all invoices where the outstanding amount is equal to the due amount),and update all live invoices (all invoices not marked as Paid and which have previously been sent to Xero).
Send All Live Invoices
Sends all invoices where the outstanding amount is equal to the due amount, and the status is not Paid or Pending
Update all Invoice Balances
This will go through all invoices not marked as Paid or Cancelled and which have previously been sent to Xero and query Xero for an updated balance. It will update associated companies with their reference in Xero. Additionally, at the end it will update all company balances following the invoice update. This could take some time to complete, during which time your OpenCRM session on that browser will be locked and unusuable.
Logging / Error Checking
* In order to create an Invoice in Xero, the sync will first add the Products. This is so it can link any existing or new Products to the Invoices in Xero. If there is a problem with any Product row, this will not send any invoices, until the error has been corrected. An error message will be displayed which outlines the problem Product(s).
* When sending multiple invoices, each invoice will be sent separately to ensure any errors only affect the relevant invoice. For example, if you send three invoices and two send successfully and one fails, the two which are sent successfully will be marked sent to Xero.
* A separate log table "xero_log" is updated on any successful attempt to send the invoice and any errors are logged in here too.
Important Notes and Info
* Invoices and credit notes sent currently go into the "Drafts" section in Xero where they can then be approved.
* OpenCRM's integration with Xero allows you to create a new Client record, with a Primary Person linked (first name, last name, email address). If you sync a subsequent invoice for the same client, but with a different Person linked, it will not add a second Person, but will overwrite the first Person.
* Finance and System admins only are able to access the Xero settings page and send invoices to Xero or update balances from Xero.
* Line amounts are passed through as the full undiscounted amount. The discount rate is calculated to a percentage per line and passed through to Xero which then calculates the actual line total.
* Credit notes are transferred with list prices as the discounted amount because Xero does not support discount on credit notes.
* Update of balances on credit notes is not yet supported.
* Product (Inventory) Name in Xero is limited to a length of 50 characters. If your products are set up in OpenCRM with a longer name (in excess of 50 characters), you will need to modify the name to allow the integration to work.