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.
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
As you can imagine, there’s a lot you can do with filtering. Let’s walk through the basics.
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:
|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.
The following filters are not defined in the Microsoft standard, but are available in the Avalara REST v2 API:
amount between 10 and 20
|Matches all records greater than or equal to the first value, plus all records less than the second value.|
id in (123, 456, 789)
|Matches all records whose value is the same as one of the items in the list.|
name contains 'Bob'
|Matches all records whose value contains the specified filter string.|
name startswith 'Bob'
|Matches all records whose value begins with the specified filter string.|
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.|
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"
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.