Digging into ODATA filters

There is an incredible wealth of data in any given MYOB AccountRight file, and our MYOBapi is a great tool for getting access to this data. But what if you want to fetch specific bits of the information, filtering it to get data that matches a specific query?

The MYOBapi includes the oData framework which allows you to craft queries to filter the data that is found and returned. There are various versions of oData available, the MYOBapi uses version 3 of the oData specification.

We have a page on our documentation site that introduces oData and gives some basic examples that help you get started. Recently we’ve had a few people wanting to do more with the oData and so I thought a quick blog post with some examples might help here. I’m not going to cover things like $top, $skip and $orderby as these are covered in our docs. We also have an earlier blog post that touches on some $filter tips & tricks. In this post I’m going to touch a bit more on $filter and some of the questions that have come through our support desk lately.

endswith & startswith

Can I filter data based on the start or end characters of a given field?

This question popped up when talking to a developer about the change in “Invoice Numbers” where in an older version the number was padded with leading 0s. In the newer versions these invoice numbers are no longer padded. This meant for that developer they couldn’t search for ‘00001234′ because it wouldn’t match with ‘1234′.

oData includes the ability to filter by endswith - which is perfect for us in this scenario. To filter by this we craft a url that looks like:

/Sale/Invoice/?$filter=endswith(Number, '1234')

This endswith querry will return all matches on the invoice endpoint where the invoice number ends with 1234.

The opposite of endswith is of course startswith and it’s a great way to find content beginning with something. Often times an integration will prepend an identifier to our DisplayID (or similar) to help them know it’s data that originated with them. Lets take an example where an ecommerce platform is creating customers in AccountRight, it’s starting the DisplayID with ‘WOO’ so that you know the customer came from WooCommerce. A simple filter to look for these customers would be:

/Contact/Customer/?$filter=startswith(DisplayID, 'WOO')

Now you have a list of all the customers that were created with 'woo’ starting their DisplayID.

Note on case sensitivity: both endswith and startswith are case sensitive.

substringof

Can I find items where the memo field contains 'xxxx’?

This question was raised on our community forums where the developer wanted to find specific data within a date range where the memo field contained a specific code.

Starting with the ability to find something within a field lets take a look at substringof. This is a filter that allows us to question if something does or does not exist. So lets say we are looking in the JournalTransaction endpoint for all items that have the term 'WooCommerce Sale’ in the description.

/JournalTransaction/?$filter=substringof('WooCommerce Sale', Description) eq true

This querry would give us all the data where 'WooCommerce Sale’ was included somewhere in the text found in the Description field.

Our original question asked could we find all 'WooCommerce Sale’ items between a given date range. That’s pretty simple to do by extending our filter. Here’s a query that will look for these items from the 01 Jan 2014 to the 30 Jun 2014.

/JournalTransaction/?$filter=DateOccurred ge datetime'2014-01-01' 
	and DateOccurred le datetime'2014-06-30' 
	and substringof('WooCommerce Sale', Description) eq true

Note on case sensitivity: substringof is case sensitive, meaning that 'WooCommerce Sale’ will not match 'Woocommerce sale’

trim

Can I ensure the data coming back doesn’t contain white space?

We know from time to time white space creeps into data and at times it causes some issues. One of our developers asked via our support if there was a quick way to ensure that a specific field could be trimmed so that it didn’t contain white space. Turns out oData to the rescue here too with the trim function.

Lets say you want to ensure that the Description field on the JournalTransaction endpoint doesn’t include whitespace. Here’s a simple filter to do just that.

/JournalTransaction/?$filter=length(trim(Description)) eq length(Description)

Here we are telling oData to trim the Description to the length of the description, which will remove any leading or trailing whitespace from the data.

concat

I need to search by Name, but MYOBapi splits FirstName and LastName?

We had a question from a customer where their tool stored the customer name as a single item eg: Name: 'Donald Duck’, where MYOB AccountRight stores this as FirstName: 'Donald’, LastName: 'Duck’ and they wondered if there was a quick way to filter data without them needing to write code to split the customer names first.

oData has the concept of concat which allows us to perform a filter based on two (or more) items concatenated together. Here’s an example querry looking for Donald Duck.

/Customer/?$filter=concat(concat(FirstName, ' '), LastName) eq 'Donald Duck'

Notice that the filter is saying we want to join FirstName to Lastname with a space (’ ’) in between. You can of course tell it to concatenate with any character or set of characters, eg:

/Contact/Customer/?$filter=concat(concat(FirstName, ', '), LastName) 
	eq 'Donald, Duck'

This example would look for Donald, Duck within the dataset.

Note on case sensitivity: concat is case sensitive, meaning that 'Donald Duck’ will not match 'donald duck’

Working with dates

I’d like a quick way to find all the transactions for a given month

Earlier we gave an example where we wanted all transactions within a given date range, in this case the developer asked if there was a way to filter data for a given month. One way of course is to be explicit in doing this using the datetime filter.

/GeneralLedger/JournalTransaction/?$filter=DateOccurred ge datetime'2014-01-01' 
	and DateOccurred le datetime'2014-01-31'

This example would find all transactions for Jan 2014. It does however require you to provide a start and end date, and know the number of days per month etc (which of course isn’t too hard really). But there is a simpler way. oData provides year, month, day, hour, minute and second filters. Lets take a look at a quick way to find all transactions for Jan 2014.

/GeneralLedger/JournalTransaction/?$filter=month(DateOccurred) eq 1 
	and year(DateOccurred) eq 2014

That will find all transactions that match the 1st month (Jan) and the year 2014. Simple.

If you wanted to find all transactions that happen in June regardless of the year, you could just do:

/GeneralLedger/JournalTransaction/?$filter=month(DateOccurred) eq 6

More …

This has been a bit of a deeper exploration into oData, but it is by no means exhaustive. You can learn a lot more about other queries you can build for MYOBapi with oData over on the oData Version 3 URL Conventions page.

One thing to note - oData does include the concept of Select, this however is NOT supported in the MYOB AccountRight API. So no, you can not craft select style queries.

We hope that helps you to understand some of the flexibility of oData and how you can leverage it to refine the data returned to you via the MYOB AccountRight API.