Skip to main content

​
Intro

Xero develops cloud-based accounting software for small- and medium-sized businesses. You can use this connector to retrieve data about bank accounts, transactions, expense claims, contacts, and the like. To establish the connection between Domo and Xero, a user must first authenticate to Xero and then grant Domo permission to access his or her data. To learn more about the Xero API, visit their page (http://developer.xero.com/documentat…/api-overview/ ). You connect to your Xero account in the Data Center. This topic discusses the fields and menus that are specific to the Xero connector user interface. General information for adding DataSets, setting update schedules, and editing DataSet information is discussed in Adding a DataSet Using a Connector.

​
Prerequisites

To connect to your Xero account and create a DataSet, you must have the email address and password you use to log into your Xero account.

​
Connecting to Your Xero Account

This section enumerates the options in the Credentials and Details panes in the Xero Connector page. The components of the other panes in this page, Scheduling and Name & Describe Your DataSet, are universal across most connector types and are discussed in greater length in Adding a DataSet Using a Connector.

​
Credentials Pane

The Domo Xero connector uses OAuth to connect, so there is no need to enter credentials within Domo. Click Connect (or select Add Account if you have existing Xero accounts in Domo) to open the Xero OAuth screen where you can enter your Xero credentials. Once you have entered valid Xero credentials, you can use the same account any time you go to create a new Xero DataSet. You can manage connector accounts in the Accounts tab in the Data Center. For more information about this tab, see Manage Connector/Adapter Accounts.
Note: You can only have one Xero account authenticated at a time with a given set of credentials. If you try to use OAuth with Xero anywhere else, it will invalidate your Xero account in Domo.
Note: If you are already logged into Xero when you connect in Domo, you are authenticated automatically when you click Add account. If you want to connect to an account that is different from the one you are logged into, you must first log out of Xero.

​
Details Pane

This pane contains a primary Reports menu, along with various other menus which may or may not appear depending on the report type you select.

Menu

Description

Report

Select the Xero report you want to run. The following reports are available:

Report Name Description
Accounts Returns account information, including ID, name, description, status, type, etc.
Aged Payables by Contact Returns aged payables for a specified contact.
Aged Receivables by Contact Returns aged receivables for a specified contact.
Balance Sheet Returns balance sheet information for the current month.
Bank Statement Returns statement lines for a specified bank account.
Bank Summary Returns balances and cash movements for all bank accounts.
Bank Transactions Returns all spend and receive money transactions.
Bank Transfers Returns information about bank transfers.
Branding Themes Returns a list of defined branding themes.
Budget Summary Returns a monthly budget summary.
Contacts Returns information on your contacts, including ID, first and last name, email address, physical address, etc.
Credit Notes Returns information about credit notes, including issue date, type, status, etc.
Currencies Returns information about currencies used in your account.
Employees Returns information about payroll employees, including first and last name, job title, contact information, salary information, etc.
Executive Summary Returns a summary that includes monthly totals and common business ratios.
Expense Claims Returns submitted expense claims with their status.
Invoices Returns invoices, including contact name and ID, invoice date, status, totals and subtotals, amount paid, etc.
Items Returns items, including purchase and sales details.

Journals (deprecated)

Returns a list of journal entries. Xero no longer supports the functionality of this report, and its accuracy cannot be guaranteed. Use the Journals (Append) report instead.

Journals (Append)

Returns a list of journal entries created or modified after the specified date.
Manual Journals Returns manual journals, including date, status, URL, etc.
Organizations Returns information about organizations, including name, address, business type, tax status, etc.
Payments Returns information about payments for invoices and credit notes.
Profit and Loss Returns profit and loss information for the current months.
Purchase Orders Returns a list of purchase orders.
Receipts Returns draft expense claim receipt information, including user, date, totals, tax, etc.
Repeating Invoices Returns repeating invoice templates.
Tax Rates Returns tax rates, including tax type, status, tax and effective rates, etc.
Tracking Categories Returns tracking categories and options.
Trial Balance Returns trial balance information, including provider name, report name and date, debit and credit amounts, etc.
Users Returns account user information, including ID, first and last name, email address, etc.

Include Tracking Categories

Select this checkbox to include the tracking category data for each line item in the journal entry. As journal entries may contain more than one tracking category, this will include 1 row of data per tracking category.

Select the field to flatten

Some Xero reports returns additional arrays of data. For example, the ‘Invoices’ report has a list of payments that are a part of that invoice. If you chose to flatten on payments, and a single invoice had 5 payments, you would get 5 records for that one invoice.

Select Date filter field

Select the Date filter to view the Date of transaction or the Last modified date in UTC format .

Date Entry Method

Select the date function that you would like to run for this report, or enter a date manually.

Fiscal Month

Select the month from when your fiscal calendar begins.

Fiscal Day

Enter the day from when your fiscal calendar begins.

Week Start Day

Select the day from when your week begins.

Contacts Filter Type

Filter Type Description
None No filtering will be performed.
Filter by contact ID Only data for specified contact IDs will be returned.
Filter by contact date Only data for contacts that have been modified since the specified date will be returned

Contact IDs

Enter a comma separated list of contacts.

Report Date

Select the report date using relative or specific dates. Relative indicates the number of days from today or a specific date using the date selector.

Select Specific Date

Select the report date using the date selector.

Days back

The number of days back for which you would like to get data. Specify either today or 0, yesterday or 1, or today-7 or 7 to get the data for 7 days into the past.

Duration

Select the duration for the report (a Single Date, or a Date Range).

Days back

The number of days back for which you would like to fetch the data. Specify either today or 0, yesterday or 1, or today-7 or 7 to get data for 7 days into the past.

Start Date

Select the report start date using relative or specific dates. Relative indicates the number of days from today or a specific date using the date selector.

Select Specific Start Date

Select the report date using the date selector.

Days back to start from

The number of days back for which you would like to fetch the data from (start day). Specify either today or 0, yesterday or 1, or today-7 or 7 to get data for 7 days into the past.

End Date

Select the report end date using relative or specific dates. Relative indicates the number of days from today or a specific date using the date selector.

Select Specific End Date

Select the report date using the date selector.

Days back to end at

The number of days back for which you would like to fetch the data (end day). Specify either today or 0, yesterday or 1, or today-7 or 7 to get data for 7 days into the past.

Select the period count

Select the period count.

Select the timeframe type

Select the timeframe type.

Tracking Category ID

Filter your results based on a tracking category ID. This field is optional.

Journal History in Days

Enter the number of days of Journal History you would like to retrieve. After the initial run, the connector will only retrieve newly created or updated journals.

Include Date in Amount Header

Select this checkbox to append the date returned by the API to the amount column, allowing you to distinguish between different amounts with varying dates.

Select the tenant to request data from

If you have authenticated against multiple tenants, you must specify which tenant you wish to query. If you do not specify, the first tenant in the list will be used as a default.

​
Other Panes

For information about the remaining sections of the connector interface, including how to configure scheduling, retry, and update options, see Adding a DataSet Using a Connector.

​
Troubleshooting for Consulting/Support

​
Dates in Column Names

Some Xero reports return data in which the run date is used as a column name. For example, if you were to run the “Profit & Loss” report on Feburary 17th, 2017, an amount column called “Row__17_Feb_17” would appear. This prevents these columns from being used in a DataFlow or card, as the name of the column changes with each scheduled DataSet update. To resolve this issue, we recommend replacing dates in column names with generic column names. You can use a Domo SQL query to replace the faulty column with a generically named column, which can then be referred to in a Magic DataFlow. In the following example, we replace the column name “Row__16_Feb_17” with the name “ProfitLossAmount.” 
select NULL AS Row_Id, NULL AS Row_ProviderName, NULL AS Row_DateTimeUTC, NULL AS Row_ReportName, NULL AS Row_ReportDate, NULL ​AS Row_UpdatedDateUTC, NULL AS Row_ReportTitle, NULL AS Row_column_0, NULL AS ProfitLossAmount, NULL AS _BATCH_ID_, NULL AS _BATCH_LAST_RUN_ from xero_profit_and_loss_report
where 1 = 0 --this row returns nothing but allows setting the column names for the query
union all
select * from xero_profit_and_loss_report

​
Unwanted Pivot Tables

Often, data with the column naming issue described above is represented as a pivot table. You cannot use the Collapse Columns action IN ETL to unpivot this data because the column names change for each scheduled run. An example of this is the “Budget” report. To resolve this issue, unpivot columns that have been named with dates, but retain date values as rows so the output DataSet has one date row per column. In the following example, we first use a Domo SQL query to rename the columns to generic names. Then we use a Magic ETL DataFlow to perform a Collapse Columns action and then convert the generic column name (which is now row data) back into a date.
  1. Create a Domo SQL query as follows: Input: Xero DataSet created using the report “Budget”
    xero_unpivot_1.png
    Transform: Replace the column names “Row__Feb-17,” “Row__Mar-17,” etc. with “Month1,” “Month2,” etc. using the method described above. Output Format: Renamed columns
    xero_unpivot_2.png
  2. In a Domo Magic DataFlow, unpivot the output data defined above and replace the Month1, Month2 etc. values with dates based on the _BATCH_LAST_RUN date.
    xero_unpivot_3.png
    1. Perform a Collapse Columns action in which you unpivot the 12 date columns.
      xero_unpivot_4.png
    2. Perform a Value Mapper action in which you create a new field that contains the number of months to add to the BATCH_LAST_RUN_ date to get the correct date field for the row.
      xero_unpivot_5.png
    3. Perform a Set Column Type action in which you change the data type of the number of months column to a numeric type.
      xero_unpivot_6.png
    4. Perform a Date Operations action in which you use the Add Months to Date operation to add the number of months to the _BATCH_LAST_RUN_ date to determine the date for the row.
      xero_unpivot_7.png
Your output format should be as follows: one row per month instead of one column per month
xero_unpivot_8.png