I’m setting up some Generic Inquiries that are exposed via OData for ingestion into a data warehouse.
Some of the GIs are comprised of data from multiple tables (Customer + Address, for instance).
In order to implement an incremental load strategy, I include a computed data field for Last Modified DateTime that looks like this:
But when I try to filter on that via an OData query such as this (where the actual date/time is filled in based on a previously captured value)
$filter=ModifiedOn gt datetime'yyyy-mm-dd hh:mm:ss'
I get an error message
Error while copying content to a stream. Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
However, if I include all of the LastModifiedDateTime values in the GI, I can successfully query data with a filter such as
$filter=LastMod1 gt datetime'yyyy-mm-dd hh:mm:ss' or LastMod2 gt datetime'yyyy-mm-dd hh:mm:ss' or ... or LastModN gt datetime'yyyy-mm-dd hh:mm:ss'
But that makes for both ugly GI presentations and ugly code that consumes the OData feed.
Is this is a bug in the Acumatica API?
Does anyone know of a workaround?
Best answer by Gabriel Michaud
This appears to be a limitation of the OData provider in Acumatica - filtering on formula/calculated columns is not possible. A possible workaround would be to create a PXProjection through code, and using that projection in the Generic Inquiry - lots of work unfortunately but that would solve your problem.
If someone from Acumatica is reading this, it is something that would be really useful to implement. Since filtering on such columns is possible through column filters, I’m sure there’s a way to make it work through OData as well!
P.S. have you checked if updating the address also changes the LastModifiedDateTime of the parent record?