QuickBooks [where] parameter

To filter data that you want to fetch from QuickBooks, you can use the Where parameter field:

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:

• What is the syntax of the Where parameter?
• What fields of QuickBooks data entity can be used in the Where parameter?
• Examples of Where parameter usage for different fields per 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:

Id='30'

In this example:

• Id - the name of the data entity field
• = - operator
• '30' - value to compare with

Filtering by multiple parameters is supported by AND

Example:

Id='30' AND TxnDate='{{lastmonthstart}}'

The OR operation isn’t supported.

Available operators are: >, <, =, >=, <=, IN, LIKE. Not all operators, however, can be used with every type of data entity field:

String	>, <, =, >=, <=, IN, LIKE
ReferenceType	=, IN, LIKE
Boolean	=, IN
TypeEnum	=, IN, LIKE
Decimal	>, <, =, >=, <=, IN
BigDecimal	>, <, =, >=, <=, IN
IdType	>, <, =, >=, <=, IN
Date	>, <, =, >=, <=, IN

Fields supported in the where parameter for each QB data entity

Account

Fields	Type
Id Name Description Classification FullyQualifiedName AccountSubType	String
ParentRef	ReferenceType
Active SubAccount	Boolean
AccountType	AccountTypeEnum
CurrentBalance CurrentBalanceWithSubAccounts	Decimal

Attachable

Fields	Type
Id	IdType
FileName Note Category ContentType PlaceName Long Tag Lat ThumbnailTempDownloadUri	String
Size	Decimal

Bill

Fields	Type
Id DocNumber	String
VendorRef APAccountef SalesTermRef	ReferenceType
TxnDate DueDate MetaData.CreateTime MetaData.LastUpdatedTime	Date
TotalAmt	BigDecimal
Balance	Decimal

Billpayment

Fields	Type
Id DocNumber	String
VendorRef APAccountRef	ReferenceType
TotalAmt	BigDecimal
TxnDate	Date

Budget

Fields	Type
FullyQualifiedName Id Name	String
BudgetType	BudgetTypeEnum
Active	Boolean

Class

Fields	Type
Id FullyQualifiedName	String
Active	Boolean
MetaData	ModificationMetaData

CompanyInfo

Fields	Type
Id	String

CreditMemo

Fields	Type
Id DocNumber	String
SalesTermRef CustomerRef	ReferenceType
TxnDate	Date
Balance	Decimal

Customer

Fields	Type
Id FullyQualifiedName PrintOnCheckName PrimaryEmailAddr DisplayName GivenName MiddleName FamilyName CompanyName	String
Active	Boolean
Balance	Decimal
MetaData.CreateTime	Date

Department

Fields	Type
Active	Boolean
MetaData	ModificationMetaData
Id FullyQualifiedName	String

Deposit

Fields	Type
Id	String
TxnDate	Date

Employee

Fields	Type
Id DisplayName GivenName MiddleName Suffix FamilyName PrintOnCheckName	String
Active	Boolean

Estimate

Fields	Type
Id DocNumber	String
CustomerRef	ReferenceType
TxnDate DueDate	Date

Exchangerate

Fields	Type
AsOfDate	Boolean
SourceCurrencyCode	String
MetaData	ModificationMetaData

Invoice

Fields	Type
Id DocNumber	String
CustomerRef SalesTermRef	ReferenceType
TxnDate DueDate	Date
Balance	Decimal

Item

Fields	Type
Id	IdType
Name Type Sku FullyQualifiedName	String
Active	Boolean

JournalEntry

Fields	Type
Id DocNumber	String
TxnDate	Date

JournalCode

Fields	Type
Id	IdType
MetaData	ModificationMetaData

Payment

Fields	Type
Id PaymentRefNum	String
CustomerRef	ReferenceType
TxnDate	Date

PaymentMethod

Fields	Type
Id	String
Active	Boolean

Preferences

No fields are supported in the Where parameter.

Purchase

Fields	Type
Id DocNumber	String
TxnDate	Date

PurchaseOrder

Fields	Type
Id DocNumber	String
TxnDate DueDate	Date

RefundReceipt

Fields	Type
Id DocNumber	String
TxnDate	Date
CustomerRef	ReferenceType
PaymentType	PaymentTypeEnum
Balance	Decimal

SalesReceipt

Fields	Type
Id DocNumber	String
CustomerRef	ReferenceType
TxnDate	Date
TotalAmt	BigDecimal
Balance	Decimal

TaxAgency

Fields	Type
Id	String

TaxCode

Fields	Type
Id Name	String
Active	Boolean
Description	String

TaxRate

Fields	Type
Id Name Description	String
AgencyRef TaxReturnLineRef	ReferenceType
Active	Boolean

Term

Fields	Type
Id Name	String
Active	Boolean

TimeActivity

Fields	Type
Id	String
TxnDate	Date
BillableStatus	BillableStatusEnum

Transfer

Fields	Type
Id	String
TxnDate	Date

Vendor

Fields	Type
Id GivenName MiddleName Suffix FamilyName DisplayName CompanyName PrintOnCheckName	String
Active	Boolean
Balance	Decimal

VendorCredit

Fields	Type
Id DocNumber	String
APAccountRef	ReferenceType
TxnDate	Date
TotalAmt	BigDecimal

Detailed examples of Where parameter usage

Examples with String type:

Id='30' - retrieve rows with Id equals to 30  
Id>'30' - retrieve rows with Id bigger than 30  
Id>='30' - retrieve rows with Id equals or bigger than 30  
Id<'30' - retrieve rows with Id lower than 30  
Id<='30' - retrieve rows with Id equals or lower than 30    
Id IN ('30','80') - retrieve rows with Id from the defined list of ids: [30;80]    

Name LIKE '%Bank%' - retrieve rows with Name that contains "Bank"  
Name LIKE 'Bank%' - retrieve rows with Name that starts with "Bank"  
Name LIKE '%Bank' - retrieve rows with Name that finishes with "Bank"   
Name='Bank' - retrieve rows with Name equals to "Bank"   

Name IN ('Amortisation expense','Long-term debt') - retrieve rows with Name from the defined list

GivenName > ' ' - retrieve rows with not empty GivenName //this tip is needed, because QBO does not support "not equal" operator

Parameters with Boolean type:

Active=TRUE - retrieve rows with Active records only  
SubAccount=FALSE - retrieve rows that are not a SubAccount

Active IN (false, true)- retrieves rows with active and inactive records.

Parameters with ReferenceType type:

To define the Id of the reference entity (i.e. Vendor (Supplier)):

1. Login to QuickBooks Online.
2. Navigate to the Expenses Suppliers tab.
3. Open the needed Supplier.
4. Copy the id from the URL: https://monosnap.com/file/RrQPdj21a76SfFufgQegij4LBmskt0.

VendorRef='4'- retrieve rows with VendorRef id equals to 4

Parameters with AccountTypeEnum, PaymentTypeEnum types:

AccountType='Other Current Asset'- retrieve rows with specific AccountType

Parameters with IdType, Decimal, BigDecimal types:

CurrentBalanceWithSubAccounts='0'- retrieve rows with zero balance  
CurrentBalanceWithSubAccounts>'0'- retrieve rows with balance bigger than 0

Parameters with Date type:

TxnDate>='2022-01-01' - retrieve rows with transaction date for Jan 01, 2022 or later

The date parameter supports macros. To check which macros are supported, visit: How to use macros in Coupler.io?

Parameters with ModificationMetaData type:

ModificationMetaData is splitted into 2 columns with Date type: MetaData.CreateTime and Data.LastUpdatedTime

MetaData.CreateTime >= '2021-04-01'  
MetaData.LastUpdatedTime < '2021-04-01'

Tip: macros supported. E.g.:
MetaData.CreateTime >= '{{yesterday}}'
MetaData.LastUpdatedTime < '{{1yearago}}'