How to use Where parameter in QuickBooks importer?

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:

  1. What is the syntax of the Where parameter?
  2. What fields of QuickBooks data entity can be used in the Where parameter?
  3. 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}}'

Note: 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:

Data type Allowed operators
String >, <, =, >=, <=, IN, LIKE
ReferenceType =, IN, LIKE
Boolean =, IN
TypeEnum =, IN, LIKE
Decimal >, <, =, >=, <=, IN
BigDecimal >, <, =, >=, <=, IN
IdType >, <, =, >=, <=, IN
Date >, <, =, >=, <=, IN

Check which fields are 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

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 importers?

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}}'
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.
Meanwhile, check QuickBooks documentation for the latest information.

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.