Filtering in AvaTax REST

Filter Operation Symbols

Filter operations are used to test the value of a field and return only those records where the test is true. When combined with pagination, filtering can allow you to select a subset of records and browse through all information stored in AvaTax in a performant, consistent manner.

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.

Using Pagination

AvaTax supports pagination using the $top and $skip parameters. These allow you to handle pagination within your own software and to only fetch those records that need to be shown, avoiding the overhead of processing any information that is not desired.

  • The $top parameter controls how many records should be returned. You can think of this as your "page size". If you pass in a value of 50, then you will receive only the first fifty records matching your filter.
  • The $skip parameter controls how many records you skip before first returning results. If you pass in a value of 100, AvaTax will only begin returning results after the first 100 records have been skipped.
  • For example, if you are currently showing page 3 of records, and your page size is 100, then you would pass in the values $top=100&skip=200. This skips the first 100 records on page one, the second 100 records on page two, then shows the next 100 records on page three.

Pagination results are returned in the @recordsetCount and @nextLink variables. Regardless of your page size, the total number of records that match your filter is returned in the @recordsetCount variable. To determine the number of pages of information available, divide the @recordsetCount value by the $top value, rounding up. This would allow you to display a link that allows the user to click on the specified page number and move to it directly.

Alternatively, if you simply want to allow the user to click a “Next” button to jump directly one page, you can use the @nextLink variable. That variable contains the URL that you could hit to get the next page immediately following the current one.

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 two apostrophe signs inside single quotes:

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 quotes inside double quotes:

"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.