Avalara Developer Network Developer avaTax

Filtering in AvaTax REST v2

Filter Operation Symbols

Filter operations are used to test the value of a field and return only those records where the test is true.

Avalara supports the following filtering operations defined in the Microsoft REST API Guidelines. Please note that in addition to the normal method of naming these operations, we also support symbols - but to use those symbols, you must properly URL encode your query string. To avoid URL encoding problems, we encourage you to use the Microsoft syntax wherever possible.

Using Filters

Filters are present all throughout the AvaTax REST v2 API. You can use filters in most every place as a way to search for objects matching specific criteria. For example, let’s say you want to search for all companies with an address in California. You might do the following:

GET /api/v2/companies?$filter=region eq 'CA'

In this example, calling GET /api/v2/companies is an API call that returns all companies in your account. However, the $filter=region eq 'CA' adds a test: the API call will only return those companies who have a field region with value CA.

As you can imagine, there’s a lot you can do with filtering. Let’s walk through the basics.

Filter Criteria

The Microsoft standard defines an english-language shortcut for all filter criteria; Avalara supports both this english-language shortcut as well as the common mathematical symbols for filter criteria.

Here’s a list of filters supported in REST v2:

Filter Symbol Example
Equals (EQ or =) EQ or =
isActive eq true
Greater Than GT or >
taxDate gt '2016-01-01'
Greater Than Or Equal GE or >=
id ge 123
Not Equal NE or <> or !=
id ne 123
Less Than LT or <
id lt 123
Less Than Or Equal LE or <=
id le 123

Filtering Against Normalized Data

In some cases, the AvaTax API provides post-processing, normalization, and standardization of data. AvaTax only stores normalized and validated data in order to ensure consistency. For example, the AvaTax CreateLocation API allows you to provide a full length country name and region name; but internally, we validate all country and region names against the International Standards Organization’s ISO 3166 database.

This means that if you provide your country name as “United States”, we convert it to the two character ISO 3166 code US. Someone else who calls the country “The United States Of America” or “U.S.A.” will also be normalized to US. In each case, only the value US is stored; we do not keep track of the original value pre-normalization.

When searching the database using $filter, you can only search against the normalized data. This means that you must always say $filter=country eq US. Because each search goes against the normalized data, you’ll always get the correct results across your entire data set.

Avalara-Specific Filters

The following filters are not defined in the Microsoft standard, but are available in the Avalara REST v2 API:

Filter Example Notes
Between
amount between 10 and 20
Matches all records greater than or equal to the first value, plus all records less than the second value.
In
id in (123, 456, 789)
Matches all records whose value is the same as one of the items in the list.
Contains
name contains 'Bob'
Matches all records whose value contains the specified filter string.
StartsWith
name startswith 'Bob'
Matches all records whose value begins with the specified filter string.
EndsWith
name endswith 'Bob'
Matches all records whose value ends with the specified filter string.
Is Null / Is Not Null
ref1 is null
Matches all records where the field's null flag is a specific value.

Conjunctions

On occasion you may need to retrieve objects that have a combination of values. To do this, you may need to search for objects that match a number of statements at once. To do this, you can use conjunctions to chain together multiple statements, and you can use parenthesis symbols to define the order of operations.

When using parenthesis, remember that all elements within the parenthesis are resolved first before any elements outside of the parenthesis. If you have multiple clauses enclosed in parenthesis, they may be resolved in any order. The following are examples of valid conjunctions:

isActive eq true AND hasProfile eq true
taxDate eq '2016-01-01' or taxDate eq '2016-01-02'
(firstName = 'bob' or firstName = 'alice') and lastName = 'smith'

Using Apostrophe or Quotation

To include apostrophe in the filtering value, use a single apostrophe sign inside double quotes:

name contains "Bob's shop"

Or a two apostrophe sign inside single quote:

name contains 'Bob''s shop'

To include quotation in the filtering value, use double quotes inside single quotes:

'Bob''s "special" recipe'

Or use two double quotation inside double quotations:

"Bob's ""special"" recipe"

Complex Examples

Now let’s put it all together, shall we?

GET /api/v2/companies/$filter=name startswith 'Bob' and (isActive eq true or hasProfile eq true)

This example retrieves all companies with a name begins with the letters ‘Bob’ and where either the isActive flag is true or the hasProfile flag is true.

GET /api/v2/companies/123/transactions?$filter=taxDate between '2016-01-01' and '2016-02-01' and status eq committed

Matches all records where the taxDate is in the month of January and the record’s status is ‘committed’.

GET /api/v2/locations$filter=(country eq 'US' and region eq 'CA') or (country eq 'CA')

Matches all locations for all companies where the location is either in the country of Canada or in California in the United States. This filter carefully ensures that even if a different country has a region whose code is “CA”, this filter will still only return those locations in the United States.

GET /api/v2/companies$filter=code startswith 'Franchise' and name contains 'Bob''s'

Matches all companies whose code begins with the characters Franchise and whose name contains the phrase Bob's. Note the apostrophe is doubled up.