fmAccounting Link (Xero Edition) v2 Frequently Asked Questions
This is the FAQ for fmAccounting Link (Xero Edition) v2 (requires FileMaker Pro v16 or higher). You can view the FAQ for fmAccounting Link (Xero Edition) v1 here.
What versions of FileMaker Pro/Pro Advanced does fmAccounting Link (Xero Edition) require?
fmAccounting Link (Xero Edition) currently works with FileMaker Pro/Pro Advanced v16 or later. We continue to develop fmAccounting Link (Xero Edition) and will ensure it works with future releases of FileMaker Pro.
Is FileMaker Pro Advanced required?
FileMaker Pro Advanced is only required to copy/paste the Custom Functions that fmAccounting Link (Xero Edition) uses when you are integrating this into your own FileMaker solution files. Otherwise you can use both FileMaker Pro and Pro Advanced when using the fmAccounting Link (Xero Edition) functionality.
Does fmAccounting Link (Xero Edition) support OAuth 2.0?
Yes – fmAccounting Link (Xero Edition) v2 only works with OAuth 2.0 Xero apps.
Does fmAccounting Link (Xero Edition) use a plug-in?
No – fmAccounting Link (Xero Edition) v2 runs natively on the FileMaker 16 or higher platforms, including FileMaker Pro, FileMaker Go, FileMaker WebDirect and FileMaker Server without requiring a FileMaker plug-in.
Is FileMaker Go supported?
Yes – fmAccounting Link (Xero Edition) v2 works with FileMaker Go 16 or later. The fmAccounting Link (Xero Edition) solution doesn’t include any FileMaker Go specific layouts for the iPhone or iPad but you can use FileMaker Go to upload and download between FileMaker and Xero.
Is FileMaker WebDirect supported?
Yes – fmAccounting Link (Xero Edition) v2 works with FileMaker Server 16 or later and supports FileMaker WebDirect. The fmAccounting Link (Xero Edition) solution doesn’t include any FileMaker WebDirect specific layouts but you can use FileMaker WebDirect to upload and download between FileMaker and Xero.
Is FileMaker Cloud for AWS supported?
There are no issues when using a FileMaker Cloud for AWS hosted version of fmAccounting Link (Xero Edition) when using the FileMaker Pro client application as all communication with the Xero API is handled by FileMaker Pro. You can also use server side scripts with FileMaker Cloud for AWS, e.g. via Perform Script on Server.
Is FileMaker Cloud supported?
There are no issues when using a FileMaker Cloud hosted version of fmAccounting Link (Xero Edition) when using the FileMaker Pro client application as all communication with the Xero API is handled by FileMaker Pro. You can also use server side scripts with FileMaker Cloud, e.g. via Perform Script on Server.
Is there any documentation to help us integrate fmAccounting Link (Xero Edition) into our own FileMaker solution?
Yes – we have a dedicated Integration page. We also plan to add some additional videos to illustrate particular aspects of the integration.
I’m having trouble uploading the file to my FileMaker Server/FileMaker Cloud.
To make it easier to get started we set the fmAccounting Link (Xero Edition) file to open automatically with the Full Access account (the login credentials can be found in the Full Access Login.rtf file that is included with the download). This can cause an issue if you try upload the file to your FileMaker Server/FileMaker Cloud if the
Require Password-Protected Databases option is enabled on your FileMaker Server. The file will be seen as insecure when uploading and you’ll get an error (this is also logged in the FileMaker Server Event.log file). You just need to untick the “Log in using” checkbox in the File Options dialog first before uploading your file:
I don’t see an example for all Xero API endpoints?
We’ve tried to include all the common Xero integration requirements for a typical small business that uses FileMaker (Contacts, Invoices, Bills, Payments, Employees, Timesheets etc). We’re happy to add additional examples to the fmAccounting Link (Xero Edition) solution file – as long as it is supported by the Xero API we can create an example.
Can I upload a Payment with an Invoice to mark the Invoice as Paid?
The Xero API does not currently support creating a Paid Invoice with a single API call. You will need to upload the Invoice and the Payment separately. They plan to support creating a Paid Invoice in a single API call in a future update to the API.
What’s the best way to integrate this into my existing FileMaker solution?
How you go about integrating the functionality from the fmAccounting Link (Xero Edition) file is entirely up to you – as the file is completely unlocked you get to go behind the scenes to learn how to handle the authentication, uploading, downloading etc and then recreate that in your existing FileMaker solution. Based on feedback from customers all around the world there have been three main ways they have approached the integration:
Linking – this involves using the fmAccounting Link file as a ‘interface’ or front-end file to your existing FileMaker file. This is a relatively quick way to get up and running – you relink the table occurrences to reference the matching tables in your existing FileMaker file and then update the layouts and add any new fields (such as the ‘Xero ID’ fields and the POST data calculation fields). You also need to update any field references in scripts and create any new value lists.
Use the fmAccounting Link file as a Controller File – this involves pushing data to the fmAccounting Link file from your existing FileMaker file and use that to handle the uploading/downloading of data with Xero. This is also a relatively quick way to get up and running – you simply write scripts in your FileMaker solution to push Contacts, Invoices etc across from your file to the fmAccounting Link file and use that to handle all the uploading of FileMaker data to Xero. To prevent duplicate Contacts from being created you would create a ‘Xero Contact ID’ field to store the Xero Contact ID value, otherwise everything else happens in the fmAccounting Link file. You could then do an upload once a day of all Invoices generated that day etc.
Embedding – this is the most complex and time consuming type of integration as it involves recreating the required functionality from the fmAccounting Link file in your existing FileMaker solution. Our integration guide covers the high level steps that you need to take and the order in which to complete them. Most of the code can be copied and pasted, however you will need to manually create some relationships, table occurrences and valuelists. You can typically be up and running with authentication and uploading a Contact/Invoice in around 4 – 8 hours depending on the complexity of your existing FileMaker solution.
If you do decide to use the fmAccounting Link file you will have to create and manage any required FileMaker Accounts and Privilege Sets.
We are already using Xero and have existing Contacts, Invoices etc. Is there a way to prevent duplicate Contacts from being created?
Yes – the Xero API exposes the Xero Contact ID field which is their unique identifier for a Contact record. We store this in a field in the fmAccounting Link file in the Contacts table (same for Invoices, Payments etc – they all have their own unique Xero ID). You can use the fmAccounting Link file to download all existing Contacts from Xero to get access to the Xero Contact ID values and then create a field in your FileMaker solution to store this, then populate this from the fmAccounting Link file via a relationship based on Company Name etc.
Once this is populated any future uploads to Xero will not create a duplicate Contact in Xero but use this for uploading to an existing Xero Contact.
What is the Contact Code that appears in Xero and how does this get populated?
The Contact Code field that appears in Xero can only be updated via the Xero API – it appears as a read only field within Xero. The Contact Code field represents the value of the ContactNumber JSON element that is pushed to Xero. In the fmAccounting Link Xero file we map this to the ContactNumber field in the Contacts table. This allows you to easily map/identify the same Contact record in Xero and FileMaker.
N.B. when uploading a Contact the Xero API will match on either their Xero Contact ID (e.g. 64eedbc9-1fa0-485a-837f-705f23188162) or the ContactNumber/Contact Code. If you archive a Contact that has a Contact Code value (e.g. CT1523) and then attempt to upload another Contact with the same Contact Code you will get this error:
The contact number CT1523 is already assigned to another contact. The contact number must be unique across all contacts
The Xero CRM Integration guide has more information – remember that for Contacts in Xero the ContactID, ContactNumber and Name elements are unique identifiers, and both ContactNumber and ContactID are uniform resource identifiers.
How does the Xero Invoice Number get populated?
The Invoices API endpoint has a InvoiceNumber element which is a unique alpha numeric code to identify an Invoice. You have 2 options when uploading Invoices to Xero:
- leave this blank and Xero will will auto-generate this from your Organisation Invoice Settings
- populate this with a value from a FileMaker field
In the fmAccounting Link (Xero Edition) file we are using the 2nd option to populate this with the value from the InvoiceNumber field (an auto-enter serial number field). If you wish to change this to have Xero auto-generate the number for you just comment out this line:
["InvoiceNumber" ; Invoices::InvoiceNumber; JSONString];
in the Create Invoice in Xero script when it sets the $json variable with this value.
We have a separate field in the Invoices table named XeroInvoiceNumber which we populate with the Xero Invoice Number value, regardless of the above settings.
I see a Xero ID field in most tables – what is the purpose of these fields?
Most tables that upload to Xero will contain a “Xero ID” field, e.g.:
- _kf_XeroContactID in Contacts
- _kf_XeroInvoiceID in Invoices
- _kf_XeroProductID in Purchase Orders
These fields store Xero’s unique identifier for each Contact, Invoice, Purchase Order etc that you upload to Xero or download from Xero.
These operate in a similar way to a primary key field does in FileMaker. If there is a value in these fields that means that the Contact, Invoice etc has previously been uploaded (or downloaded from Xero) – i.e. that FileMaker record also exists in Xero. When uploading to Xero if the “Xero ID” field is empty a new record will be created in Xero, otherwise the Xero record that matches the “Xero ID” field will be updated.
N.B. as these Xero ID values should be unique make sure when you duplicate an Invoice, Contact, Bill etc that you clear out the value in these Xero ID fields (_kf_XeroContactID, _kf_XeroInvoiceID etc) otherwise you will run into issues when uploading as it will be attempting to update that record in Xero that matches the value in the Xero ID field.
I’m getting this error when uploading an Invoice to Xero: Invoice not of valid status for modification. How I can resolve this?
You will get this error if you’re trying to modify an Invoice in Xero that doesn’t allow modifications due to the Invoice Status. For example you cannot modify an Authorised Invoice that has payments applied to it.
You cannot modify an Invoice in Xero if:
- it has been fully paid
- it has part-payments or credit notes applied to it
- it is in a locked period
You can view the full list of possible Xero Invoice Status codes here and the rules associated with these.
You could also get this error if you’re uploading a new Invoice with the same Invoice Number as an existing Invoice in Xero that cannot by modified, or you have duplicated an Invoice that has previously been uploaded to Xero. If you duplicate an Invoice the value in the _kf_XeroInvoiceID field will also be duplicated – this is Xero’s unique identifier for each invoice so make sure that if you do duplicate an Invoice, Contact, Bill etc that you clear out the value in these Xero ID fields (_kf_XeroContactID, _kf_XeroInvoiceID etc).
How do I delete an Invoice in Xero from fmAccounting Link?
You don’t actually delete an invoice by calling a script to run a delete command on the Xero API for an Invoice (you do for Payments and other items though).
You delete an Invoice by changing the Status of the Invoice to ‘DELETED’ and uploading the Invoice to Xero. There are some rules around this (same ones that apply in the Xero web app):
- you can delete a DRAFT or SUBMITTED invoice by updating the Status to DELETED
- If an invoice has been AUTHORISED it cannot be deleted but you can set it’s status to VOIDED.
I don’t see any Archived Contacts when I download all my Xero Contacts?
Archived Contacts are excluded by default when you perform a GET request to download a group of Xero Contacts. To include archived contacts in your request simply append the following to your URL:
I’m getting an HTTP Error 429 Too Many Requests when doing a bulk upload/download
A 429 error means you are encountering the Xero Rate Limits for their API. The most common one we see is the Minute Limit of 60 calls per minute. The easiest way to resolve this is to add a pause to the looping script using the Pause/Resume Script script step to the loop so that is pauses between iterations (e.g. 1 second) which will slow the script down so that it doesn’t hit the Xero Rate Limits.
Does fmAccounting Link (Xero Edition) support Unit Price Rounding to 4 decimal places?
Yes – v2 of fmAccounting Link includes support for rounding unit prices on line items to 4 decimal places for:
- Credit Notes
- Bank Transactions
I just tried to upload a Contact that was archived and got an error?
The Xero API currently does not allow edits to be made to an archived contact. You will get the following validation error:
The specified contact details matched an archived contact. Archived contacts cannot currently be edited via the API
To make edits to this Contact you will need to Restore (unarchive) the Contact within Xero first.
I’m getting the following error when trying to authenticate with my OAuth 2.0 app:
The Xero website requires that your web browser support at least TLS 1.1 or later and the version of Internet Explorer (used by the FileMaker Web Viewer) either doesn’t support this or does not have this enabled by default. This site provides details of how to enable TLS 1.1/1.2 in Internet Explorer. After enabling TLS 1.1 or later you should be able to authenticate successfully in the fmAccounting Link (Xero Edition) solution.
I’m getting the following error when trying to authenticate with my OAuth 2.0 app:This is usually caused when the Redirect URL entered in the fmAccounting Link Organisation screen doesn’t match one of the Redirect URIs saved against your app on developer.xero.com. Make sure these are exactly the same (copy and paste helps to ensure they are identical) and try again.
I”m creating a new Tax Rate but do not see any drop down lists for the Tax Type and the Report Tax Type?
The Tax Type values are different for each Xero Organisation region, and include some values that can be updated and other (system values) that cannot be updated. You can get the full list of Tax Types here:
Report Tax Types are also region specific and include a mix of values which can be used when creating and updating Tax Rates and others which are used for system tax rates and only returned on GET requests. You can get the full list of Region Tax Types here:
You can use these values to create your own custom drop down lists specific to your Xero organisation’s region.
Can I upload a Credit Note and allocate it in a single request?
The Xero API does not currently support creating and allocating a credit note in a single call – the create and allocation must be done in two separate calls. The fmAccounting Link (Xero Edition) file shows how to perform these in a single FileMaker script so you can have one button that creates and allocates at the same time.
How do Overpayments work with the Xero API?
The process of creating and allocating Overpayments works a bit differently to other Xero API endpoints. To create an Overpayment you use the BankTransactions endpoint and to allocate outstanding invoices to an Overpayment you use the Overpayments endpoint. It’s also worth noting that Overpayments can only have a single line item. The line item will be automatically coded to either the Accounts Receivable (receive) or Accounts Payable (spend) control account when uploading to Xero. Overpayments also do not have any taxable component to the line item – this is always set to no tax.
The Xero API does not currently support creating and allocating a Overpayments in a single call – the create and allocation must be done in two separate calls. The fmAccounting Link (Xero Edition) file shows how to perform these in a single FileMaker script so you can have one button that creates and allocates at the same time.
The Xero API does not currently support updates to an existing Overpayment – once you have pushed the Overpayment from FileMaker to Xero you cannot make any changes to the Overpayment, but you can upload allocations against the Overpayment.
There is also a bug with the Xero Overpayments endpoint that incorrectly shows the TaxType as ‘NONE’ in the API but displays as ‘BASEXCLUDED’ in the Xero website/UI. This is scheduled to be fixed in a future update to the Xero API.
Can I upload the Bill Reference?
The Reference field is only available for Accounts Receivable invoices. For Accounts Payable invoices – Bills in the fmAccounting Link file – the Xero InvoiceNumber value is displayed as the Reference in the Xero interface. The Reference field from the Bills table is populating the InvoiceNumber in Xero which then appears as the Bill Reference (e.g. BL1305).
What kind of attachments can be uploaded to an Invoice?
You can upload up to 10 attachments (each up to 3mb in size) per invoice, once the invoice has been created in Xero. We’ve successfully uploaded the following file types:
- PDF files
- JPEG images
- PNG images
- Word documents
- Excel spreadsheets
- Text file
A full list of file types that you can upload is available on the Xero website. You cannot currently delete an attachment in Xero from FileMaker – you will need to login to Xero to delete any attachments that have been uploaded to an Invoice.
Are Xero Tracking Categories supported?
Yes – you can use the fmAccounting Link file to download Tracking Categories from Xero, as well as create new Tracking Categories and Tracking Category Options. The fmAccounting Link file also has examples for deleting a Tracking Category or Tracking Category Option. You can also add up to 2 Tracking Categories to the following items in the fmAccounting Link file:
- Purchase Orders
- Credit Notes
- Manual Journals
- Timesheets (supports a single specified Tracking Category)
I’m getting a 503 response code error when doing a bulk upload from FileMaker to Xero.
An HTTP 503 response usually means “Rate Limit Exceeded”.
An application can make up to 5000 API calls against a particular Xero organisation in a rolling 24 hour period and 60 calls against a particular Xero organisation in a rolling 60 second period. The following will be returned in the body if you exceed a rate limit:
(unencoded this is “oauth_problem=rate limit exceeded&oauth_problem_advice=please wait before retrying the xero api”)
The most common issue encountered is the 60 requests/min rate limit. If you’re encountering this limit simply add a pause of a few seconds to your looping script to keep the upload within the allowed limits
You can get more information about Xero API Rate Limits here:
I’m not familiar with the technique that you’re using to create records when downloading data from Xero – can you explain how this works?
When downloading data from Xero if we need to create records in another table (e.g. Contacts, Invoices etc) rather than constantly changing layouts to create new records in the correct table we’re using a technique that allows you to create related records via a relationship that has the “Allow creation of records in this table via this relationship” enabled for the relationship.
This technique is also known as the “Magic Key” technique and is also used when implementing support for database transactions in FileMaker. You can read more about this technique at these websites:
Is there a list of changes in each version of fmAccounting Link (Xero Edition)?
Yes – to see what’s new with each update check out the fmAccounting Link (Xero Edition) version history log