# QuickBooks \[where] parameter

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

<div align="left"><figure><img src="https://1055512216-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FOFxU2LUkJMCfvbE2re5C%2Fuploads%2FPffYZuLYA7q5LSkPWJSQ%2Fimage.png?alt=media&#x26;token=bcf258b8-153c-4243-a9b6-bc0f9fe11aa4" alt="" width="563"><figcaption></figcaption></figure></div>

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:

* [x] [What is the syntax of the Where parameter?](#the-syntax-for-the-where-parameter)
* [x] [What fields of QuickBooks data entity can be used in the Where parameter?](#fields-supported-in-the-where-parameter-for-each-qb-data-entity)
* [x] [Examples of Where parameter usage for different fields per data type.](#detailed-examples-of-where-parameter-usage)

### 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'
```

<div align="left"><figure><img src="https://1055512216-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FOFxU2LUkJMCfvbE2re5C%2Fuploads%2FV8jDvBofGF1BB8LIflO7%2Fimage.png?alt=media&#x26;token=8bc8a05c-a083-44eb-8077-62121cb46001" alt="" width="563"><figcaption></figcaption></figure></div>

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}}'
```

{% hint style="warning" %}
The **OR** operation isn’t supported.
{% endhint %}

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

<table data-header-hidden><thead><tr><th width="357.60546875">Data type</th><th>Allowed operators</th></tr></thead><tbody><tr><td>String</td><td>>, &#x3C;, =, >=, &#x3C;=, IN, LIKE</td></tr><tr><td>ReferenceType</td><td>=, IN, LIKE</td></tr><tr><td>Boolean</td><td>=, IN</td></tr><tr><td>TypeEnum</td><td>=, IN, LIKE</td></tr><tr><td>Decimal</td><td>>, &#x3C;, =, >=, &#x3C;=, IN</td></tr><tr><td>BigDecimal</td><td>>, &#x3C;, =, >=, &#x3C;=, IN</td></tr><tr><td>IdType</td><td>>, &#x3C;, =, >=, &#x3C;=, IN</td></tr><tr><td>Date</td><td>>, &#x3C;, =, >=, &#x3C;=, IN</td></tr></tbody></table>

### Fields supported in the `where` parameter for each QB data entity

#### Account

| Fields                                                                                                    | Type            |
| --------------------------------------------------------------------------------------------------------- | --------------- |
| <p>Id</p><p>Name</p><p>Description</p><p>Classification</p><p>FullyQualifiedName</p><p>AccountSubType</p> | String          |
| ParentRef                                                                                                 | ReferenceType   |
| <p>Active</p><p>SubAccount</p>                                                                            | Boolean         |
| AccountType                                                                                               | AccountTypeEnum |
| <p>CurrentBalance</p><p>CurrentBalanceWithSubAccounts</p>                                                 | Decimal         |

#### Attachable

| Fields                                                                                                                                    | Type    |
| ----------------------------------------------------------------------------------------------------------------------------------------- | ------- |
| Id                                                                                                                                        | IdType  |
| <p>FileName</p><p>Note</p><p>Category</p><p>ContentType</p><p>PlaceName</p><p>Long</p><p>Tag</p><p>Lat</p><p>ThumbnailTempDownloadUri</p> | String  |
| Size                                                                                                                                      | Decimal |

#### Bill

| Fields                                                                                | Type          |
| ------------------------------------------------------------------------------------- | ------------- |
| <p>Id</p><p>DocNumber</p>                                                             | String        |
| <p>VendorRef</p><p>APAccountef</p><p>SalesTermRef</p>                                 | ReferenceType |
| <p>TxnDate</p><p>DueDate</p><p>MetaData.CreateTime</p><p>MetaData.LastUpdatedTime</p> | Date          |
| TotalAmt                                                                              | BigDecimal    |
| Balance                                                                               | Decimal       |

#### Billpayment

| Fields                              | Type          |
| ----------------------------------- | ------------- |
| <p>Id     </p><p>DocNumber</p>      | String        |
| <p>VendorRef</p><p>APAccountRef</p> | ReferenceType |
| TotalAmt                            | BigDecimal    |
| TxnDate                             | Date          |

#### Budget

| Fields                                  | Type           |
| --------------------------------------- | -------------- |
| <p>FullyQualifiedName Id</p><p>Name</p> | String         |
| BudgetType                              | BudgetTypeEnum |
| Active                                  | Boolean        |

#### Class

| Fields                             | Type                 |
| ---------------------------------- | -------------------- |
| <p>Id</p><p>FullyQualifiedName</p> | String               |
| Active                             | Boolean              |
| MetaData                           | ModificationMetaData |

#### CompanyInfo

| Fields | Type   |
| ------ | ------ |
| Id     | String |

#### CreditMemo

| Fields                                | Type          |
| ------------------------------------- | ------------- |
| <p>Id</p><p>DocNumber</p>             | String        |
| <p>SalesTermRef</p><p>CustomerRef</p> | ReferenceType |
| TxnDate                               | Date          |
| Balance                               | Decimal       |

#### Customer

| Fields                                                                                                                                                                 | Type                   |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------- |
| <p>Id</p><p>FullyQualifiedName</p><p>PrintOnCheckName</p><p>PrimaryEmailAddr</p><p>DisplayName</p><p>GivenName</p><p>MiddleName</p><p>FamilyName</p><p>CompanyName</p> | String                 |
| Active                                                                                                                                                                 | Boolean                |
| Balance                                                                                                                                                                | Decimal                |
| MetaData.CreateTime                                                                                                                                                    | <p>Date</p><p><br></p> |

#### Department

| Fields                             | Type                 |
| ---------------------------------- | -------------------- |
| Active                             | Boolean              |
| MetaData                           | ModificationMetaData |
| <p>Id</p><p>FullyQualifiedName</p> | String               |

#### Deposit

| Fields  | Type   |
| ------- | ------ |
| Id      | String |
| TxnDate | Date   |

#### Employee

| Fields                                                                                                            | Type    |
| ----------------------------------------------------------------------------------------------------------------- | ------- |
| <p>Id</p><p>DisplayName</p><p>GivenName</p><p>MiddleName</p><p>Suffix</p><p>FamilyName</p><p>PrintOnCheckName</p> | String  |
| Active                                                                                                            | Boolean |

#### Estimate

| Fields                       | Type          |
| ---------------------------- | ------------- |
| <p>Id</p><p>DocNumber</p>    | String        |
| CustomerRef                  | ReferenceType |
| <p>TxnDate</p><p>DueDate</p> | Date          |

#### Exchangerate

| Fields             | Type                 |
| ------------------ | -------------------- |
| AsOfDate           | Boolean              |
| SourceCurrencyCode | String               |
| MetaData           | ModificationMetaData |

#### Invoice

| Fields                                | Type          |
| ------------------------------------- | ------------- |
| <p>Id</p><p>DocNumber</p>             | String        |
| <p>CustomerRef</p><p>SalesTermRef</p> | ReferenceType |
| <p>TxnDate</p><p>DueDate</p>          | Date          |
| Balance                               | Decimal       |

#### Item

| Fields                                                    | Type    |
| --------------------------------------------------------- | ------- |
| Id                                                        | IdType  |
| <p>Name</p><p>Type</p><p>Sku</p><p>FullyQualifiedName</p> | String  |
| Active                                                    | Boolean |

#### JournalEntry

| Fields                    | Type   |
| ------------------------- | ------ |
| <p>Id</p><p>DocNumber</p> | String |
| TxnDate                   | Date   |

#### JournalCode

| Fields   | Type                 |
| -------- | -------------------- |
| Id       | IdType               |
| MetaData | ModificationMetaData |

#### Payment

| Fields                        | Type          |
| ----------------------------- | ------------- |
| <p>Id</p><p>PaymentRefNum</p> | String        |
| CustomerRef                   | ReferenceType |
| TxnDate                       | Date          |

#### PaymentMethod

| Fields | Type    |
| ------ | ------- |
| Id     | String  |
| Active | Boolean |

#### Preferences

*No fields are supported in the Where parameter.*

#### Purchase

| Fields                    | Type   |
| ------------------------- | ------ |
| <p>Id</p><p>DocNumber</p> | String |
| TxnDate                   | Date   |

#### PurchaseOrder

| Fields                       | Type   |
| ---------------------------- | ------ |
| <p>Id</p><p>DocNumber</p>    | String |
| <p>TxnDate</p><p>DueDate</p> | Date   |

#### RefundReceipt

| Fields                    | Type            |
| ------------------------- | --------------- |
| <p>Id</p><p>DocNumber</p> | String          |
| TxnDate                   | Date            |
| CustomerRef               | ReferenceType   |
| PaymentType               | PaymentTypeEnum |
| Balance                   | Decimal         |

#### SalesReceipt

| Fields                    | Type          |
| ------------------------- | ------------- |
| <p>Id</p><p>DocNumber</p> | String        |
| CustomerRef               | ReferenceType |
| TxnDate                   | Date          |
| TotalAmt                  | BigDecimal    |
| Balance                   | Decimal       |

#### TaxAgency

| Fields | Type   |
| ------ | ------ |
| Id     | String |

#### TaxCode

| Fields               | Type    |
| -------------------- | ------- |
| <p>Id</p><p>Name</p> | String  |
| Active               | Boolean |
| Description          | String  |

#### TaxRate

| Fields                                  | Type          |
| --------------------------------------- | ------------- |
| <p>Id</p><p>Name</p><p>Description</p>  | String        |
| <p>AgencyRef</p><p>TaxReturnLineRef</p> | ReferenceType |
| Active                                  | Boolean       |

#### Term

| Fields               | Type    |
| -------------------- | ------- |
| <p>Id</p><p>Name</p> | String  |
| Active               | Boolean |

#### TimeActivity

| Fields         | Type               |
| -------------- | ------------------ |
| Id             | String             |
| TxnDate        | Date               |
| BillableStatus | BillableStatusEnum |

#### Transfer

| Fields  | Type   |
| ------- | ------ |
| Id      | String |
| TxnDate | Date   |

#### Vendor

| Fields                                                                                                                              | Type    |
| ----------------------------------------------------------------------------------------------------------------------------------- | ------- |
| <p>Id</p><p>GivenName</p><p>MiddleName</p><p>Suffix</p><p>FamilyName</p><p>DisplayName</p><p>CompanyName</p><p>PrintOnCheckName</p> | String  |
| Active                                                                                                                              | Boolean |
| Balance                                                                                                                             | Decimal |

#### VendorCredit

| Fields                    | Type          |
| ------------------------- | ------------- |
| <p>Id</p><p>DocNumber</p> | 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 <i class="fa-arrow-right">:arrow-right:</i> 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?](https://docs.coupler.io/functionality/macros-in-data-flows)

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}}'
```