How to use Where parameter in QuickBooks data flow?
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 |
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)):
- 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?
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.