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:
- 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
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
- Attachable
- Bill
- Billpayment
- Budget
- Class
- CompanyInfo
- CreditMemo
- Customer
- Department
- Deposit
- Employee
- Estimate
- Exchangerate
- Invoice
- Item
- JournalEntry
- JournalCode
- Payment
- PaymentMethod
- Preferences
- Purchase
- PurchaseOrder
- RefundReceipt
- SalesReceipt
- TaxAgency
- TaxCode
- TaxRate
- Term
- TimeActivity
- Transfer
- Vendor
- VendorCredit
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 |
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)):
- Login to QuickBooks Online.
- Navigate to the Expenses -> Suppliers tab.
- Open the needed Supplier.
- 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.