Query Parameters: in / not in / not equals to / does not contain etc

[Iain Warde]

Is there a way we can use filters to search for the following types:

• in / not in (list)
• not equals to
• does not contain
• does not start with
• does not end with

I'm looking at the following page to see examples but there's no mention of these on there:

https://developer.sage.com/200/guides/query-parameters/

[Iain Warde]

As stated on the Sage Developer Filtering Guidelines (https://developer.sage.com/people/guides/filtering/), the filter syntax uses Microsoft's REST API guidelines.  So going through the links, I've found how to do the "not equals to" using this:

https://github.com/microsoft/api-guidelines/blob/vNext/graph/Guidelines-deprecated.md#97-filtering

It turns out to do "not equals to", you need to use "ne".

However, I can't see anything for the other options, if anyone can help here that would be great!

[Chris Burke]

Hi,

It's more accurate to say that it's using OData URL conventions.  They're documented here : https://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html- but be aware that the Sage 200 API doesn't strictly adhere to any particular version. It's more version 2-ish than version 4-ish!

Anyway, you can do stuff like 'WHERE name LIKE 'A%' by using 'startswith'  :  {{baseUrl}}/customers?$filter=startswith(name, 'A')

Similary 'WHERE name LIKE '%A'' would just be  : {{baseUrl}}/customers?$filter=endswith(name, 'A')

You can negate both of those - i.e. express something like 'doesn't start with' by testing the boolean result of the query fragment. Like this:

{{baseUrl}}/customers?$filter=startswith(name, 'A') ne true

...which is 'doesn't start with 'A'. You can pull that negation trick with startwith, endswith, contains etc.  

Later versions of OData do support 'in' (the syntax being $filter=name in ('Company 1', 'Company 2') ) - but the sage 200 API doesn't appear to support this. So you'll just have to fall back on logical disjunctions I'm afraid ($filter=name eq 'Company1' or name eq 'Company2')