Applying Filters in OData
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):
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:
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 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"
I'm not sure though if Acumatica has implemented the substringof function.