Solved

OData error when filtering on calculated GI data field

  • 12 February 2021
  • 8 replies
  • 51 views

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:

=Max([Table1.LastModifiedDateTime],[Table2.LastModifiedDateTime],etc,etc)

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?

icon

Best answer by Gabriel Michaud 15 February 2021, 14:20

Hi,

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?

View original

8 replies

Userlevel 5
Badge +3

Hi,

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?

Gabriel,

Thank you for confirming that this is a limitation of the OData provider.

I was a bit confused because no such limitation exists in the Acumatica UI (you are free to sort and filter on a calculated field just like any other).

I checked, and updating the address/location does not update the parent record’s last modified date.

Given that the GIs are created by business analysts at my request, I don’t believe that PXProjections are a viable option.  I’ll handle it on my end one way or another.  Perhaps by querying the metadata and dynamically building the filter based on all fields whose names follow a particular pattern.  That way, I can write the code once and use it on all of the OData feeds I consume.  The number of such fields will vary from GI to GI.

--Bill

Userlevel 5
Badge +3

@jetzerb08 I just double-checked to be sure, but I confirm that updating the main address of a customer *does* trigger an update of the BAccount.LastModifiedDateTime field… you don’t need to check the Address table separately.

Interesting.  The GI that the BA built for me doesn’t include the BAccount entity -- only Customer, Address, Contact (main/default) and Contact (bill-to).

I’ll investigate further.

@Gabriel Michaud Could you walk me through your process? I tried updating the main address of a customer and it did not update the BAccount record.  I tried both

  • Using the Receivables » Profiles » Customer Locations menu item directly
  • Pulling up the Customer record first, navigating to the “Locations” tab, clicking on the location hyperlink, and making an edit to the location

In both instances, the Location/Address record was updated but not the customer or BAccount.

This is on Acumatica version 20.106.0005.

Userlevel 5
Badge +3

@jetzerb08 sorry for the confusion, I was referring to the account default address, not the main location address:

 

The locations are managed from a different screen/graph, and won’t update the customer timestamps.

@jetzerb08 is the issue scalability? So you have done this with one report and you want to be able to apply it to other GIs? Or you might want to go back and add more tables / new ‘last modified date’ fields to this GI? 
If that is the case - what is your method of extracting the OData feed itself? 

We use Power BI to pull our OData feeds from Acumatica and there are some nice tools you can use to help automate things like this. I haven’t done it myself, but I know there is a capability where you can automate the URL for the OData feed. I’m sure your analyst could look it up, but basically you can set user parameters and/or I *think* be able to supply a CSV as a feed file. Either way, you could set it up so that you have a list that the code pulls from with the column names to build the code for the $filter. In this way, you could probably copy and paste that code from the start of your inquiry to build each new inquiry. If you kept the column header names the same in each GI, you wouldn’t need to rename - just make sure the number is correct. 
Edit: I think this would work in any “Power Query” - so also in Excel.
Just a thought to make things a little easier!

@hazen35 Ideally, I could tell the BAs that each GI should include fields with names

  • CreatedBy
  • CreatedOn
  • ModifiedBy
  • ModifiedOn
  • DeletedDatabaseRecord, (and check the “Show Deleted Records” box), if the “main” table on the GI is a master-type record that provides this information
  • Released, and some finer grained “Transaction Status” field for transactional entities

The inability to filter on calculated fields means that I’ll need to make my filter more complex to deal with the fact that there will be a variable number of “Modified” fields depending on how many different tables the records in the GI pull from.

 

FYI - I’m using Azure Data Factory and/or SSIS to pull this data.

 

I think you and I are on the same page regarding automation to build the filter expression.  Earlier I had mentioned

Perhaps by querying the metadata and dynamically building the filter based on all fields whose names follow a particular pattern.

 

To expand on that: Querying https://ACUMATICA_INSTANCE_URL/OData/TENANT/$metadata returns information on all GIs available via OData, including all the fields within each GI and their datatypes.

I could obtain the metadata at the beginning of my ETL process, and use it to compile the “last modified date” filters for each GI I pull from based on all of the fields that start with “ModifiedOn” and have date/time datatype.

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2020  Acumatica, Inc. All rights reserved