As I explained in a previous post - Xero accounting software has a database quirk. This issue is unlikely to affect 95% of small businesses using Xero. However, if you are a subscription-based business like ours, and have another CRM as your main database (or your own custom database like us), which holds more than a couple of hundred customers, you are likely to run into the same problem we did.
The issue comes about because Xero uses “Customer Name” for its unique identifier (sometimes referred to as “primary key”) in the Xero database. This can cause major integration problems if your business has more than one customer with the same name (see: reasons your business might have more than one customer with the same name).
There is a solution to this problem. The logical thing for our business would be to use our account number in Xero’s field for “Customer Name”, instead of the customer’s name. Our problem would immediately be solved, and we would actually have a better user experience when working in Xero as it would be easier for our admin people to quickly cross-reference account numbers to Xero customers.
However there is a problem. This work-around would work fantastically if all of the Xero database details were “behind the scenes” with nothing front-facing the public. But this is not the case. The most visible part of our Xero database is the invoices we generate. Unfortunately, the standard invoice template uses the “Customer Name” field in the invoices “To: Section”.
Using our work-around solution above, the invoices we send out would now look like this:
To: 3221
20 Main Road
Cape Town
7375
Fail! In fairness, Xero does provide the option to upload your own custom templates. After a lot of frustrating customisation we may get this right, but I have been advised by more than one Xero Advisor, not to go this route, as it can mess with your invoice pay-now link integrations and also your individual invoice URLs.
Notwithstanding the above, progress has been made. The problem is no longer an underlying database design problem (which is what the primary key problem is). The problem is now an invoice layout problem - something that is surely a lot quicker and easier to fix.
All that Xero has to do is provide a second optional field in the “To section” on the standard invoice template. In our case we would use our database’s “Invoice To” field, which is a separate piece of information from the “Customer Name”. That way it wouldn’t matter if we have more than one of the same “Invoice to” names (as the “Invoice To” piece of data is not the unique identifier). We could have two, or even three (or as many as we like) “invoice to” names even if they were identical. Everything would display on the invoice perfectly. And if a customer changes an “Invoice to” name, no problem - you just change the name, not the original account name. Any live integration between a user’s database and the Xero database, whether done by API (or another service such as Zapier) would also be a lot easier.
The second optional field that needs to be added to the standard invoice template need not even be a brand new database field. Xero already has fields that are not being used by essential data and so one could use one of these fields as a custom field to populate with your own database data field. In our case we would use one of the "spare" fields for our own database "invoice to" field. Problem solved, and the job would be a small job for Xero to do.
I have now linked this article to an existing Feature Request that somebody had previously posted on Xero's Developer Community board. The existing request is also for more fields to be added to the standard invoice template. If you are a Xero user, and agree with the sentiments I have expressed above, it would be sincerely appreciated if you went to the official request and voted it up. Update: Alternate solution to the Sage Pastel to Xero migration problems
Author: Chris Preen
The opinons expressed above are those of Chris Preen in his personal capacity and do not neccessarily reflect the opinions of Find a Professional.
