How to use Where filter in Xero

To filter data that you fetch from Xero, you can use the Where parameter:

With the Where parameter, it's possible to, for example, fetch only Invoices with an amount higher than a certain number, 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:

Data type	Allowed operators
String	== .Contains("value") .StartsWith("value") .EndsWith("value")
GUID	=GUID("value")
Number	=, >, <, >=, <=, !=
Date	=, >, <, >=, <=, !=
Boolean	==
Data with predefined set of values (i.e. Type, Status)	== !=

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

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

Type=="ACCPAY" && AmountDue > 0

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

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

Parameters	Type
AccountID	GUID
Code, Name, BankAccountNumber Description, CurrencyCode, ReportingCode ReportingCodeName	String
Type	Account Type
Status	Account Status
BankAccountType	Bank Account type
TaxType	Tax Type
EnablePaymentsToAccount ShowInExpenseClaims HasAttachments AddToWatchlist	Boolean
Class	Account Class Types
UpdatedDateUTC	Date

Bank Transactions

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

Parameters	Type
BankTransactionID PrepaymentID OverpaymentID BankAccount.AccountID Contact.ContactID	GUID
Type	Bank Transaction Type
IsReconciled HasAttachments	Boolean
DateString	Date
CurrencyCode CurrencyRate Url BankAccount.Code BankAccount.Name Contact.Name	String
Status	Bank Transaction Status Code
LineAmountTypes	Line Amount Type
SubTotal, TotalTax, Total	Number

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:

Parameters	Type
ContactID	GUID
ContactNumber Name EmailAddress	String

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:

Parameters	Type
InvoiceID Contact.ContactID	GUID
InvoiceNumber	Number
Status	Invoice Status Code
Contact.Name Reference	String
Date	Date
Type	Invoice Type

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.

Note on JournalLines fields

When you filter by a field inside JournalLines (for example JournalLines.AccountCode == 310), Xero returns each matching Manual Journal as a single row that includes all of its journal lines - not just the one that matched your filter. If you need each journal line as its own row (e.g. to see only the line with AccountCode = 310), enable Split by Journal Lines on the Sources step before applying filters in the Data Sets step.

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:

Parameters	Type
PaymentId Invoice.InvoiceId	GUID
PaymentType	Payment Type
Status	Status
Date	Date
Reference	String

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

Parameters	Type
UserID	GUID
EmailAddress FirstName LastName	String
IsSubscriber	Boolean
OrganisationRole	User role
UpdatedDateUTC	Date

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.