OData $filter Tips & Tricks

As we see a number of requests popping up in our various support forums in relation to OData queries, more specifically around $filter, I thought I’d put together a quick blog capturing some of the common scenarios developers find themselves up against.

Filtering for a field inside an object

It’s not uncommon to filter for data that is nested away inside an object, a good example of this is the following query when I’m grabbing customer contacts where the payment method is buried away inside the PaymentDetails object. You’ll notice how I’m splitting up the fields PaymentDetails/Method

{domain}/{cf guid}/Sale/Invoice/?$filter=PaymentDetails/Method eq'Cash'

Filtering for a UID (GUID)

Firstly the answer is yes you can, as all our UID fields are GUID based, a query needs to include the prefix guid to advise the HTTP request which data type we are dealing with here. For example I want to filter a list of sale invoices for a particular customer:

I can achieve this with something like the following:

{domain}/{cf guid}/Sale/Invoice/?$filter=Customer/UID eq guid'd61a6a86-453a-48bf-9402-6eb6b4ea23cf'

Working with fields of type Boolean 

To filter on a field of type Boolean, we often see developers look to treat the field type as a string i.e. eq ‘true’

A Boolean must be treated like so in the following example where I’m filtering contacts based on IsIndividual

{domain}/{cf guid}/Contact/?$filter=IsIndividual eq true

Dealing with null-able fields

Once again yes this is possible, handling a nullable field should be a matter of passing eq null

So if I wanted to filter for any customer contact address that does not include an email address as part of the Addresses array, the following will cover this: (Also uses the any operator)

{domain}/{cf guid}/Contact/Customer/?$filter=Addresses/any(x: x/Email eq null) 

Date/Time and primitive data types

It is quite common place for a developer to filter a list of transactions for a particular date range whether it be from the sale, purchase or banking collection of endpoints.

This is quite simple to do, the following example illustrates filtering a list of purchase bills where the date is equal to or greater than 01/7/2014 and less than or equal to 24/09/2014 

{domain}/{cf guid}/Purchase/Bill/?$filter=Date ge datetime'2014-07-01' and Date le datetime'2014-09-24' 

Please note: {domain} is defined as either http://localhost:8080/AccountRight/  for the desktop service or https://api.myob.com/AccountRight/  for the cloud access points.

{cf guid} is replaced with the unique ID for any given company file

For additional reading on OData please refer to our online documentation and the OData.org website