fmAccounting Link (Xero Edition) v2 Integration Guide

These instructions demonstrate how to replicate the FileMaker Xero integration from the Databuzz FileMaker Xero solution file in your existing FileMaker solution. The Databuzz FileMaker Xero solution file – fmAccounting Link – currently includes the following examples:

authentication with the Xero API using OAuth 2.0
download Chart of Accounts from Xero to FileMaker (GET)
upload Chart of Accounts from FileMaker to Xero (PUT/POST)
download Tax Rates from Xero to FileMaker (GET)
upload Tax Rates from FileMaker to Xero (PUT/POST)
download Tracking Categories from Xero to FileMaker (GET)
download Contacts from Xero to FileMaker (GET)
upload Contact/s from FileMaker to Xero (POST)
download Inventory Items from Xero to FileMaker (GET)
download Invoices from Xero (GET)
upload Invoice/s from FileMaker to Xero (POST)
download Quotes from Xero (GET)
upload Quote/s from FileMaker to Xero (POST)
upload Payment/s from FileMaker to Xero (PUT)
upload Invoice Attachments from FileMaker to Xero (PUT)
download Bills from Xero (GET)
upload Bill/s from FileMaker to Xero
download Reports from Xero to FileMaker
download Credit Notes from Xero (GET)
upload and allocate Credit Notes from FileMaker to Xero (POST and PUT)
download Overpayments from Xero (GET)
upload and allocate Overpayments from FileMaker to Xero (POST and PUT)
download Prepayments from Xero (GET)
upload and allocate Prepayments from FileMaker to Xero (POST and PUT)
download BatchPayments from Xero
upload BatchPayments from FileMaker to Xero
download Employees from Xero (GET)
upload Employees to Xero (POST)
download Timesheets from Xero (GET)
upload Timesheets to Xero (POST)
download BankTransactions from Xero (GET)
upload BankTransactions from FileMaker to Xero (POST)
download Tracking Categories/Tracking Options from Xero (GET)
upload Tracking Categories/Tracking Options from FileMaker to Xero (PUT and POST)
delete Tracking Categories/Tracking Options from Xero (DELETE)
Deep Links: opening a previously uploaded FileMaker record in Xero (e.g. have a button on the Invoices layout to open that Invoice in Xero)

We are continually updating the fmAccounting Link files to include additional examples. If you would like to see an example for other Xero endpoints supported by the Xero API please let us know.

There are 2 versions of the fmAccounting Link (Xero Edition) solution:

fmAccounting Link (Xero Edition) Single Organisation – if you are only ever going to integrate FileMaker with a single Xero Organisation use this file.
fmAccounting Link (Xero Edition) Multiple Organisations – if you will be needing to integrate FileMaker with more than one Xero Organisation use this file.

Both files have the same Xero integration functionality, however the fmAccounting Link (Xero Edition) Multiple Organisations file has an additional table for tracking different Organisations and which records/transactions belong to each Organisation.

We recommend having the following links open in separate browser tabs when testing the integration:

OAuth 2.0 Overview

Xero Developer Centre

Xero API Previewer

We also recommend activating the Xero Demo Company to use with your testing – this will let you upload and edit Xero data without affecting your live Company Xero data. Once you have a happy with the integration you can create a new OAuth2.0 App that points to your live Xero Organisation:

Xero Demo Company Setup

If you don’t already have a Xero Account you can create a trial account via the Xero website – you will need a valid Xero Account to setup the API Application that is linked to your Xero Organisation.

Step 1: Create the Xero OAuth2.0 App

Before you get started with the integration with your own FileMaker solution we strongly recommend that you use the fmAccounting Link file to download and upload and become familiar with the processes involved. You will first need to setup a Xero OAuth2.0 App. This involves creating a new Xero OAuth2.0 App and storing the Client id, Client Secret and Redirect URI in the fmAccounting Link file. We have a short video that demonstrates how to create your OAuth2.0 app – check out the OAuth2.0 Application Setup video or review the following instructions.

You will need to first create a new OAuth2.0 app in the Xero Developer website in the My Apps page. Click the New App button to create a new App – you will be prompted to enter the following details:

Integration type: select the Web app option (Standard auth code)
App Name: use a name like fmAccounting Link (the App name cannot contain the word ‘Xero’)
Company or application URL: enter your company URL or you can also enter fmAccounting Link (Xero Edition) solution URL (https://www.databuzz.com.au/fmaccounting-link-xero-edition/)
Privacy policy URL: this is optional
OAuth 2.0 redirect URI: this must be an HTTPS URL and the value in the App must also be the same in the fmAccounting Link solution (you will get an error if these are not identical). If you’re not sure what to use here use the fmAccounting Link (Xero Edition) solution URL: https://www.databuzz.com.au/fmaccounting-link-xero-edition/

Once you have clicked the checkbox to accept the terms and conditions click the Create app button to create your new OAuth2.0 app:

You will now be viewing the App Details screen – from here you need to click on the Configuration menu link and then generate a Client Secret by clicking the Generate a secret button at the bottom:

You now need to copy and paste the following into the equivalent fields in the fmAccounting Link file on the Organisations screen:

OAuth 2.0 redirect URI
Client id
Client secret

N.B. the Client secret will only ever be displayed once and will not be visible once you click the Save button (you can always generate a new secret if necessary). Use the Copy buttons next to the Client id and Client secret to copy these one at a time into the matching fields in the Organisations screen of the fmAccounting Link (Xero Edition) file:

Once you have copied across the above items make sure you click the Save button to save your OAuth2.0 app. You are now ready to test the link between the fmAccounting Link file and your OAuth2.0 app. Click the Authenticate button and it should prompt you to login and authorise your app connection. Once it has completed the authorisation process and taken you to the Redirect URL you entered when creating the Xero app click the Continue button in the bottom right hand corner of the window and it will generate the following:

Access Token
Expires At
Refresh Token
Tenant ID

These will be used to authenticate with your OAuth2.0 app from now on – you won’t be required to login to the Xero website unless your Refresh Token expires. Access Tokens expire after 30 minutes (we keep track of this in the Expires At field) and can be refreshed automatically using the Refresh Token. Refresh Tokens expire once they’re used or after 60 days: if you don’t refresh your access token within 60 days you will need to click the Authenticate button and reauthorise the app.

If you are using the fmAccounting Link (Xero Edition) Multiple Organisations file we recommend creating a new OAuth 2.0 app for each Xero Organisation and entering these into the details for each Organisations table record. This helps keep these separate and prevents you from accidentally uploading data to the wrong Xero Organisation – you will only ever authorise access access to a single Xero Organisation for each OAuth 2.0 app.

Once you have managed to authenticate successfully we recommend completing the following steps as you become more familiar with the fmAccounting Link file and start to look “under the hood” at the scripts, tables, relationships etc:

click the Update Organisation from Xero button on the Organisation Xero Preferences screen to download some Organisations details from Xero, including the Organisation Name and the Short Code (this is used for the Open in Xero buttons)
go to the Accounts tab on the Organisations screen and download the Chart of Accounts, Tax Rates and Tracking Categories
go to the General tab > Products and download the Products from Xero
you can optionally also download Contacts and Invoices, or if you prefer you can enter this in yourself
navigate to the Contacts section and create a new Contact then upload that to Xero. Click the Open Contact in Xero button to compare FileMaker and Xero and to see what data was uploaded
create a new Invoice for this Contact, enter a few line items and then upload that Invoice to Xero. Click the Open Invoice in Xero button to compare FileMaker and Xero and to see what data was uploaded
enter a payment for the Invoice then upload that Payment to Xero. View the Invoice in Xero to confirm the Payment was uploaded
create a new Bill for this Contact, enter a few line items and then upload that Bill to Xero. Click the Open Bill in Xero button to compare FileMaker and Xero and to see what data was uploaded
enter a payment for the Bill then upload that Payment to Xero. View the Bill in Xero to confirm the Payment was uploaded

You should know be comfortable with uploading and downloading data between FileMaker and Xero. You can view each of the scripts in the Script Debugger as you go to see what steps are being taken – we have commented the scripts to explain each step of the process. You are now ready to tackle the integration with your own FileMaker solution.

Make sure you check out our fmAccounting Link file videos – we will be adding more videos in the coming months that demonstrate each of the modules in the fmAccounting Link file.

N.B. as we are continually updating the fmAccounting Link file the screenshots below might not look exactly the same as what you see. Please refer to your fmAccounting Link file for the latest version of these.

Step 2: Custom Functions – Copy and Paste or Import

This requires FileMaker Pro Advanced – you can either copy the Custom Functions from the fmAccounting Link file or you can import them into your solution file. If you already have any of these custom functions installed you can skip those:

Step 3: Tables – Copy and Paste or Import

This requires FileMaker Pro Advanced – you can either copy the Tables from the fmAccounting Link file or you can import them from your solution file:

Depending on your integration and your existing solution file won’t need to import all of the following tables if you already have an existing matching table:

Accounts: this stores the Xero Chart of Accounts records – if you already have a Chart of Accounts table you won’t need to import this

Addresses: this stores the Contact address records – if you already have a Contact Addresses table or store your Addresses in your Contacts table you won’t need to import this

BankTransactions: this stores the Bank Transaction records – if you already have a Bank Transactions table or will not be downloading Bank Transactions records from Xero you won’t need to import this

BankTransactionItems: if you already have a Bank Transaction Items (BankTransactions Line Items) table or will not be downloading Bank Transactions records from Xero you won’t need to import this

Bills: this stores the Bill records – if you already have a Bills table you won’t need to import this

BillItems: if you already have a Bill Items (Bill Line Items) table you won’t need to import this

Contacts: this stores the Contact records – if you already have a Contacts/Customers table you won’t need to import this

Countries: this stores the Country records – if you already have a Countries table or all your Contacts and transactions are restricted to just a single Country you won’t need to import this

EarningsRates – this stores the Earnings Rates records – if you already have a Earnings Rates table or you will not be integrating with the Xero Payroll API you won’t need to import this

Employees – this stores the Employees records – if you already have a Employees table or you will not be integrating with the Xero Payroll API you won’t need to import this

Interface: this is ‘Preferences’ or ‘Settings’ table that contains a single record. If you already have a single record Preferences table you can use that.

Invoices: this stores the Invoice records – if you already have a Invoices table you won’t need to import this

InvoiceItems: if you already have a Invoice Items (Invoice Line Items) table you won’t need to import this

Navigation: this is part of the fmAccounting Link demo file and usually will not be required

Organisations: this stores a record for each Xero Organisation you wish to authenticate with. This allows you to use the same solution to work with multiple Xero Organisations. If you only have one Xero Organisation and you already have an existing single record preferences table you could use that instead of adding a new Organisations table

Payments: this stores the Payments records (Payments for an Invoice) – if you already have a Payments table you won’t need to import this

Phones: this stores the Contact phone records – if you already have a Contact Phones table or store your Phone numbers in your Contacts table you won’t need to import this

Products: this stores the Product/Inventory Item records – if you already have a Products/Price List/Inventory Items table you won’t need to import this

ServerSideErrors: if you will not be using FileMaker Server to perform server side scripts you won’t need to import this

TaxRates – this stores the Tax Rates records – if you already have a Tax Rates table you won’t need to import this

TaxComponents – this stores the Tax Components records (Tax Rates components) – if you already have a Tax Components table you won’t need to import this

Timesheet – this stores the Timesheet records – if you already have a Timesheet table or you will not be integrating with the Xero Payroll API you won’t need to import this

TimesheetLines – this stores the Timesheet Lines records – if you already have a Timesheet Lines table or you will not be integrating with the Xero Payroll API you won’t need to import this

TrackingCategories – this stores the Tracking Categories records – if you already have a Tracking Categories table or you will not be downloading Tracking Categories you won’t need to import this

TrackingCategoryOptions – this stores the Tracking Category Options records – if you already have a Tracking Category Options table or you will not be downloading Tracking Categories you won’t need to import this

Step 4: Create Fields in Existing Tables

If you already have existing tables for Contacts, Invoices, InvoiceItems, Organisations, Payments, Products and Interface you will simply need to create the following new fields in each of the tables if you are not going to import these tables. If you have FileMaker Pro Advanced you can use that to copy/paste the following fields:

Accounts: make sure the following fields exist:

LastAPIResult – this stores the last API call result when downloading Xero Accounts. It doesn’t need to be displayed on a user visible layout but it helpful to see the response from Xero when troubleshooting the API integration

Contacts: make sure the following fields exist:

_kf_OrganisationID – this stores the Xero Organisation ID that this Contact is associated with. This allows you to work with multiple Xero Organisations and is only found in the fmAccounting Link (Xero Edition) Multiple Organisations file.

_kf_XeroContactID – this stores the Xero Contact ID after a Contact/Invoice is uploaded from FileMaker to Xero or imported from Xero to FileMaker

LastAPIResult – this stores the last API call result for the current Contact record. It doesn’t need to be displayed on a user visible layout but it helpful to see the response from Xero when troubleshooting the API integration

Invoices: make sure the following fields exist:

_kf_OrganisationID – this stores the Xero Organisation ID that this Invoice is associated with. This allows you to work with multiple Xero Organisations and is only found in the fmAccounting Link (Xero Edition) Multiple Organisations file.

_kf_XeroInvoiceID – this stores the Xero Invoice ID after a Invoice is uploaded from FileMaker to Xero or imported from Xero to FileMaker

LastAPIResult – this stores the last API call result for the current Invoice record. It doesn’t need to be displayed on a user visible layout but it helpful to see the response from Xero when troubleshooting the API integration

Payments: make sure the following fields exist:

_kf_OrganisationID – this stores the Xero Organisation ID that this Payment is associated with. This allows you to work with multiple Xero Organisations and is only found in the fmAccounting Link (Xero Edition) Multiple Organisations file.

_kf_XeroPaymentID – this stores the Xero Payment ID after a Payment is uploaded from FileMaker to Xero or imported from Xero to FileMaker

LastAPIResult – this stores the last API call result for the current Payment record. It doesn’t need to be displayed on a user visible layout but it helpful to see the response from Xero when troubleshooting the API integration

Products (Inventory Items): make sure the following fields exist:

_kf_OrganisationID – this stores the Xero Organisation ID that this Product is associated with. This allows you to work with multiple Xero Organisations and is only found in the fmAccounting Link (Xero Edition) Multiple Organisations file.

_kf_XeroProductID – this stores the Xero Product ID after a Product is uploaded from FileMaker to Xero or imported from Xero to FileMaker

LastAPIResult – this stores the last API call result for the current Product record. It doesn’t need to be displayed on a user visible layout but it helpful to see the response from Xero when troubleshooting the API integration

Step 5: Relationship Graph – Table Occurrences and Relationships

You will need to recreate the Table Occurrences and Relationships from the fmAccounting Link file in your solution file. Unfortunately FileMaker Pro/Advanced does not currently support copy/pasting or importing of table occurrences and relationships so these will need to be recreated manually. As with importing tables if you did not import certain tables you will not need to recreate table occurrences/relationships associated with those tables you did not import:

Step 6: Update fields that referenced Relationships

The various scripts that download data from Xero and upload data to Xero will need to be updated to handle the field references in your existing FileMaker solution. For example when you upload data from FileMaker to Xero you need to generate the required JSON payload that is included in the HTTP POST or PUT request.

For example when uploading a Contact using the ‘Create Contact in Xero’ script as an example you will see the steps where we generate the JSON data:

If you look at the $json variable you will need to update any field mapping/references in the calculation formula for each upload script that you wish to incorporate from the fmAccounting Link (Xero Edition) file:

Step 7: Create Layouts

You will need to create any necessary layouts in your solution file with the same names as the fmAccounting Link file. If you did not import certain tables then you will not need to create layouts for those tables. At this step you are only creating the blank Layouts with the same name as in fmAccounting Link. You will be copy/pasting the layout objects/content at a later step. As we have not imported any scripts yet copy/pasting layout content would mean any buttons that reference scripts will be broken.

As you will be recreating layouts in your existing solution file we will be assuming that you will with to recreate the layouts using the same theme/style as your existing solution and not necessarily keep the layouts the same as they are in the fmAccounting Link file. The layouts in the fmAccounting Link file use the Cool Gray theme.

The “DEV” layouts are layouts designed for the developer and are kept hidden from users.

We find having the Manage Layouts window open for both the fmAccounting Link file and your solution file side by side is the quickest way to create new layouts in your solution file. You can quickly copy the Layout Name from the fmAccounting Link file, create a new Layout in your solution file and paste in the name and then select the correct table occurrence to use in the Show Records from menu.

Step 8: Scripts – Copy and Paste or Import

You can use FileMaker Pro/Pro Advanced to either copy/paste or import the scripts from the fmAccounting Link file. You should import all scripts in the following folders:

Organisations

Bills

Accounts

Contacts

Products

Invoices

Payments

Server

If you will be integrating with the Xero Payroll API you can also import the scripts from the following folders:

Employees

Timesheets

You can generally not import scripts in the following folders as they are used in the fmAccounting Link demo file and are usually not required for your solution file or you have existing scripts that perform the same functionality:

System

Do import this script from the System folder:

– System Install Solution Plugins

Developer

N.B.: some of the scripts perform other scripts which include script parameters which are referenced by the calling script (e.g. when performing the authentication script step and passing the keys for the API authentication script). If you are creating these scripts yourself make sure you include the script parameters. We use the NightWing Enterprises method for passing multiple script parameters which requires the DeclareVariables custom function. You can change this to your preferred method for passing multiple script parameters as required.

Step 9: Value Lists

You can use FileMaker Pro/Pro Advanced to copy/paste the Value Lists from the fmAccounting Link file:

Step 10: Layout Contents – Copy and Paste Layout Objects/Content

Now that we have the necessary value lists and scripts in your solution file you can now copy/paste the Layout objects/content from the layouts in the fmAccounting Link file. You will first need to size the layouts and layout parts to the correct sizes, or you can customise these to suit your requirements. We are assuming that you will be changing the layouts to match your existing theme and styles, but whilst you are testing the integration you can copy/paste these “as is” and once everything is working you can then come back and restyle the layouts to suit your requirements.

Step 11: OnFirstWindowOpen Script Trigger

The fmAccounting Link file has a OnFirstWindowOpen Script Trigger set via the File Menu>File Options:

The OnFirstWindowOpen Script script performs a number of functions that are specific to the fmAccounting Link file, but you may need to replicate some of this functionality in your existing OnFirstWindowOpen Script Trigger in your solution file (or set a OnFirstWindowOpen Script Trigger if you do not currently have one set).

Step 12: Create buttons for Xero API Calls

You will need to create some buttons in your existing layouts to perform the following functions (where required):

Authenticate (found on the Organisations Form layout)

Import Contacts (found on the Organisations Form layout)

Contacts: Push to Xero – Current Record (found on the Contacts Form layout)

Contacts: Push to Xero – Found Set (found on the Contacts Form layout)

Open Contact in Xero (found on the Contacts Form layout)

Invoices: Push to Xero – Current Record (found on the Invoices Form layout)

Invoices: Push to Xero – Found Set (found on the Invoices Form layout)

Open Invoice in Xero (found on the Invoices Form layout)

Payments: Upload (found on the InvoicesPayments portal on the Invoices Form layout)

Import Accounts (found on the Organisations Form layout)

Import Products (found on the Organisations Form layout)

Products: Push to Xero – Current Record (found on the Products Form layout)

Products: Push to Xero – Found Set (found on the Products Form layout)

You can copy/paste these buttons from the layouts in the fmAccounting Link file.

N.B.: some of the buttons have script parameters attached to the button which are referenced by the calling script (e.g. to instruct it to upload the current record or the current found set of records). If you are creating these buttons yourself make sure you include the script parameters. We use the NightWing Enterprises method for passing multiple script parameters which requires the DeclareVariables custom function. You can change this to your preferred method for passing multiple script parameters as required.

Step 13: Xero API Authentication

You are now ready to start testing the integration. The first step is to ensure you can authenticate successfully with the Xero API. You need to recreate the following steps in your solution:

navigate to the equivalent of your Organisations Form layout and copy/paste the Authenticate and Update Organisation from Xero buttons onto your layout.
check the Script Parameter for the Update Organisation from Xero button to ensure there are no errors.
copy/paste the Client ID and Client Secret that was generated during the setup of your Xero OAuth 2.0 app into the FileMaker fields for these
click the Authenticate button – it will now attempt to authenticate with the Xero API and your OAuth 2.0 app. If the authentication was successful you will get a dialog box confirming this and you can then click the Update Organisation from Xero button to download the Organisation details from Xero. The Organisation Short Code field will be populated with the value from the API response and you should get a confirmation dialog box like this:

The full API Response will appear in the Last API Call Result field – you can reference this if you are experiencing any errors authenticating with the Xero API. Once you have successfully authenticated you can move on to the additional modules and test the scripts that you have imported to ensure they are working as expected. We recommend testing in the following order:

Accounts
Tax Rates
Products
Contacts
Invoices
Payments
Bills

Once you can successfully download and upload using the scripts/buttons that you have imported you are ready to go live.

Step 14: Go Live and switch from Demo Company to your live Company Organisation

Once you have successfully tested the integration of your FileMaker solution file with the Xero API you can then create a new OAuth 2.0 Application to use with your live Xero company. Repeat Step 1 to generate the new OAuth 2.0 app and test that you can successfully authenticate by performing the Update Organisation from Xero to authenticate with Xero and download your Xero organisation details.