How to use Where parameter in Xero importer?

With the  Where parameter, it's possible to, for example, fetch only Invoices with an amount higher than X, updated after a specific date, etc. To use the Where parameter correctly, please check the following materials:

1. What is the syntax of the Where parameter?
2. Specific of Where parameter usage for different data types.
3. List of fields per Xero data entity with their data type.

The syntax for the Where parameter

The structure for the Where parameter is as follows:

<<Name of the data entity parameter>> + <<Operator>> + <<Value to compare with>>

The simplest example:

Total = 20.00

In this example:

• Total - the name of the data entity field
• = - operator
•  20.00 - value to compare with

Available operators for each data type are different:

It is possible to use several conditions in the Where parameter:

To fetch results that correspond to both conditions - use AND / &&:

Date >= DateTime(2015, 01, 01) AND Date < DateTime(2015, 12, 31)
Type=="ACCPAY" && AmountDue > 0 && Status=="AUTHORISED

To fetch results that correspond to one of the conditions - use OR /  ||:

TaxType == "TAX001" OR TaxType == "TAX002"
Type == "ACCPAY" || Type == "ACCREC"

Examples of Where parameter usage for different data types

Parameters with  GUID type:

AccountID = GUID("CEEF66A5-A545-413B-9312-78A545AADBC4")

Parameters with String type.

It is possible to filter data by exact value, data that contains some value, data starts from some value, data ends with some value:

Name=="Checking Account"
Name.Contains("Account")
Name.StartsWith("Account")
Name.EndsWith("Account")

Note: Check for not null value can be required for optional String parameters - i.e. BankAccountNumber

BankAccountNumber=="132435465"
BankAccountNumber != null AND BankAccountNumber.Contains("132435465")
BankAccountNumber != null AND BankAccountNumber.StartsWith("1")
BankAccountNumber != null AND BankAccountNumber.EndsWith("1")

Parameters with Number type.

It is possible to filter data if it equals some value, less, more, less&equals, more&equals or not equals:

Total = 20.00
Total < 20.00
Total > 20.00
Total <= 20.00
Total >= 20.00
Total != 20.00

Parameters with a  Date type.

It is possible to filter data using dates and next comparison: equal, before/after (including or excluding the date), not equal:

Date = DateTime(2022, 01, 30)
Date > DateTime(2022, 01, 01)
Date >= DateTime(2022, 01, 01)
Date < DateTime(2022, 01, 01)
Date <= DateTime(2022, 01, 01)
Date != DateTime(2022, 01, 01)

Parameters with  Boolean type:

ShowInExpenseClaims == TRUE
EnablePaymentsToAccount == FALSE

Parameters with types that have a  predefined set of values (i.e. Account Type, Account Status, Tax Type):

TaxType == "TAX001"
Type != "SPEND"

List of fields per Xero data entity with their data type (supported in Where parameter)

• Accounts
• Bank Transactions
• Bank Transfers
• Branding Themes
• Contact Groups
• Contacts
• Credit Notes
• Currencies
• Employees
• Expense Claims
• Invoices
• Items
• Journals
• Linked Transactions
• Manual Journals
• Organisation
• Overpayments
• Payments
• Prepayments
• Purchase Orders
• Receipts
• Repeating Invoices
• Tax Rates
• Tracking Categories
• Users

Accounts

It is possible to use the next parameters in the  Where field:

Bank Transactions

It is possible to use the next parameters in the  Where field:

Bank Transfers

Xero API allows filtering of Bank Transfers by any parameter. See Bank Transfer Xero documentation. 

Branding Themes

The Where parameter is not supported.

Contact Groups

Xero API allows filtering of Contact Groups by any parameter. See Contact Groups Xero documentation. 

Contacts

Xero documentation recommends limiting filtering to optimized parameters only:

Credit Notes

Xero API allows the filtering of Credit Notes by any parameter. See Credit Notes Xero documentation. 

Currencies

Xero API allows the filtering of Currencies by any parameter. See Currencies Xero documentation. 

Employees

Xero API allows the filtering of Employees by any parameter. See Employees Xero documentation. 

Expense Claims

Xero API allows filtering of Expense Claims by any parameter. See Expense Claims Xero documentation. 

Invoices

Xero documentation recommends limiting  filtering to optimized parameters only:

Items

Xero API allows the filtering of Items by any parameter. See Items Xero documentation. 

Journals

Journals Data Entity has no "Where" parameter in settings. However, it has 2 specific fields that allow fetching journals filtered by JournalNumber:

• Journal number more than
• Journal number less than

Linked Transactions

The Where parameter is not supported.

Manual Journals

Xero API allows the filtering of Manual Journals by any parameter. See Manual Journals Xero documentation. 

Organisation

The Where parameter is not supported.

Overpayments

Xero API allows the filtering of Overpayments by any parameter. See Overpayments Xero documentation. 

Payments

Xero API allows the filtering of Payments by any parameter, but defines a list of optimized parameters:

Prepayments

Xero API allows filtering of Prepayments by any parameter. See Prepayments Xero documentation. 

Purchase Orders

The Where parameter is not supported.

Receipts

Xero API allows the filtering of Receipts by any parameter. See Receipts Xero documentation. 

Repeating Invoices

Xero API allows the filtering of Repeating Invoices by any parameter. See Repeating Invoices Xero documentation. 

Tax Rates

Xero API allows the filtering of Tax Rates by any parameter. See Tax Rates Xero documentation. 

Tracking Categories

Xero API allows the filtering of Tracking Categories by any parameter. See Tracking Categories Xero documentation. 

Users

If you can't find the case that you need or have any issues with syntax, please write to our support team!

If you find discrapency in the documentation, please reach out to our support team as well - we'll quickly update it.
Meantime, check Xero documentation for the latest information.