#AcumaticaTnT Applying Filters in OData

Hey Animal,

You can definitely do this and it's a great idea too for the reason that you mentioned: Server-side filtering is faster, especially when the amount of data in Acumatica is very large.

Let's say that I have the following Generic Inquiry in Acumatica...

... and I want to access that data in Excel.

I can use the following URL to access the Generic Inquiry (notice that ALL of the data appears):

 http://127.0.0.1/Acumatica7/odata/Testing 

Also, note in the screenshot above that any spaces in field names get removed by OData so I had to reference VendorID rather than Vendor ID.

If I want to only see Mr. Peter Lai (EP00000022), then I can add some stuff onto the end of my URL like this (note how now only one record is returned):

 http://127.0.0.1/Acumatica7/odata/Testing?$filter=VendorID  eq 'EP00000022'

Maybe I don't want to see Vendors with a Status of Inactive. If that's the case, then I can do this:

http://127.0.0.1/Acumatica7/odata/Testing?$filter=Status ne 'Active'

Or maybe I have the following Generic Inquiry in Acumatica...

... which looks like this in Excel:

Now, suppose I want to only show documents that are larger than $2,000. I can do this:

 http://127.0.0.1/Acumatica7/odata/BillsAndAdjustments?$filter=DetailTotal  gt 2000

Note: for Greater Than use gt, for Greater Than or Equal use ge, for Less Than use lt, for Less Than or Equal use le.

You can also combine filters. Maybe I want to see all documents than are between $2,000 and $3,000. I can do this:

 http://127.0.0.1/Acumatica7/odata/BillsAndAdjustments?$filter=DetailTotal  ge 2000 and DetailTotal le 3000

There are more filters available in OData (click here), but as far as I can tell, Acumatica hasn't implemented them yet.

Thanks Tim. This is definitely a start, but it would be great if Acumatica were to implement those other OData filters that you linked to at the bottom, especially useful for filtering on Dates and finding parts of names with a contains search.

Thanks to Anahi over on this thread for pointing out a way to filter on dates.

Based on the suggestion, the following works for me for records with dates that are greater than or equal to January 1st, 2015:

 http://127.0.0.1/Acumatica7/odata/BillsAndAdjustments?$filter=Date  ge datetime'2015-01-01'

And this works for me to only get records in January 2015:

 http://127.0.0.1/Acumatica7/odata/BillsAndAdjustments?$filter=Date  ge datetime'2015-01-01' and Date le datetime'2015-01-31'

I agree that it wouldn't make sense for dates, but for a text contains function search this post for "substringof"

https://www.odata.org/documentation/odata-version-2-0/uri-conventions/

I'm not sure though if Acumatica has implemented the substringof function.

Hey @gabriel,

I really like the OData filtering article that you have on your website:
https://support.velixo.com/hc/en-us/articles/360043950572-Advanced-Filtering-in-GI-Functions

You have a Contains function available which I'm trying to use in OData, but I'm not able to get it to work. Are you using the contains function that is mentioned in this OData article that you linked to?
https://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#_Toc31360982

I get results with this OData URL:

 http://localhost/acumatica2021r1/odata/production/Invoice  Lines

And I get results with this OData URL:

 http://localhost/acumatica2021r1/odata/production/Invoice  Lines?$filter=Customer eq 'ABC Studios Inc'

But, when I try to use the contains function like this:

 http://localhost/acumatica2021r1/odata/production/Invoice  Lines?$filter=contains(Customer,'ABC')

I get this error message:

An unknown function with name 'contains' was found. This may also be a key lookup on a navigation property, which is not allowed.

Any ideas?

Hi Tim,

Acumatica does not support every OData operator; contains, startswith are endswith are not supported.

Velixo has a local OData query processor and we cache the data and do the filtering locally in such cases.