Solved

Erratic OData results -- data sometimes not matching GI

  • 11 February 2022
  • 21 replies
  • 183 views

I have a simple GI that pulls inventory IDs, price classes and prices.

When I load this GI in Excel (via an OData link), the results sometimes contain serious errors. Many inventory IDs are repeated, and some IDs don’t show up at all. 

Strangely, these errors don’t occur every time I load the data. If the data contains errors, I can often get the correct data by refreshing 4-5 times. 

If I run the GI in Acumatica and export to Excel, the data is always correct. 

Any ideas on what could be causing this?

icon

Best answer by peterj23 26 April 2022, 20:12

View original

21 replies

Userlevel 7
Badge +8

Hi @peterj23 

Issue seems to be with Excel and Odata. Can you try clearing the Odata cache on excel and monitor?

On Excel,  Data > Get Data > Query Options > Data Load > Data Cache Management Options > Clear Cache

Regards,

 

 

Thanks @vkumar but that didn’t work.

I opened the Excel file and the Odata feed refreshed automatically. The data loaded without errors.

I then cleared the cache and refreshed the data manually. Now the data has errors!

Is this a known issue? And is it likely affecting all my OData feeds?

Userlevel 7
Badge +8

Hi @peterj23 

Please let me know your Acumatica version and build. I will check for any known issue. Do send the error screenshot. How many lines are on GI result?

Regards,

Hi @vkumar 

Version is 2021 R2. Build is 21.200.0145.

The number of the lines in the data always seems to be correct. But the data within those lines has errors. 

This issue only seems to affects once column, INVENTORY ID. The other columns, CUSTOMER PRICE CLASS, EFFECTIVE DATE, and PRICE all match the Acumatica Excel export exactly.    

Userlevel 7
Badge +8

Hi @peterj23 

Thanks for the details. Can you upload the screenshots reflecting erroneous data on excel?

Regards

 

@vkumar 

Done. The data is correct for the first ~785 lines and then it begins to skip some Inventory IDs and randomly repeat others. 

@vkumar confirming that the issue is still ongoing. Is this a known issue? And does Acumatica have any suggestions for a fix or work around? 

Userlevel 7
Badge

Hi @vkumar would you have anything further to offer @peterj23 ? Thanks! Chris

Badge

We are having a similar issue with Odata from a GI, again with the Inventory ID.  This query is run into a WMS multiple times a day in a production environment. The problem is very sporadic and occurs about once every few months, but when it does happen it impacts multiple records.

Userlevel 4
Badge

@vkumar confirming that the issue is still ongoing. Is this a known issue? And does Acumatica have any suggestions for a fix or work around? 

Please raise support case with Acumatica with steps to replicate this issue and we will investigate.

Userlevel 7
Badge

Hi @peterj23 were you able to resolve your issue? Thank you!

Userlevel 5
Badge +2

@peterj23 What did you populate in the Object and Data Field columns on the RESULTS GRID tab of the Generic Inquiry (SM208000) screen to get the Inventory ID. Was it something like ARTran.InventoryID or was it InventoryItem.InventoryCD?

@Chris Hackett, it seems to have resolved itself without any intervention!

The OData feed has been refreshing without errors for the past few weeks. No changes were made to the GI or to the Excel file. I really have no idea what was wrong, or why it is suddenly working again.

I will update this thread if I see the problem again.

@TimRodman. I used ARSalesPrice.InventoryID.

ARSalesPrice was the only table in the GI. I’ve pasted a copy of the results fields below: 

 

 

Userlevel 5
Badge +2

@peterj23 Interesting. The reason I asked is that ARSalesPrice.InventoryID has to do a query to get the Inventory ID that you see in the user interface and I was wondering if maybe the problem was with the query. But if it just started working, that’s strange.

Another thing to check, if it happens again, is accessing the OData feed in your web browser since that’s closer to the “raw” OData to see if the data is messed up there or only when it arrives in Excel. I think we’d also need to see your Excel file to know if there is any Power Query data transformation or Pivot Tables in place that could be manipulating the data.

@TimRodman great idea to check the feed in the browser. I never thought to do that! There are some PQ transformations in the Excel file.

If the problem was related to the ARSalesPrice.InventoryID querying to get InventoryID, would I also see the problem in both the GI and in the OData feed? I’ve never had an problem with the data when I ran the GI within Acumatica

 

 

Userlevel 5
Badge +2

@peterj23 Ya, I'm not sure if you'd see the problem in both the GI and the OData feed. That's why I was wondering what it looks like in the browser. It might give you a clue to know if it's breaking between the GI and the OData feed or between the OData feed and Excel.

@Chris Hackett, @TimRodman, the problem has mysteriously returned!

As you suggested @TimRodman, I opened the odata feed directly in my browser. The data in the “raw” feed contains the exact same errors as the Excel import. 

It doesn’t appear that Excel isn’t causing the problem. And the GI continues to run flawlessly when run within Acumatica. 

I’ll raise a support case, but please let me know if you have any other suggestions. I appreciate all your kind help so far!

 

Userlevel 2

I frankly believe that Odata is incredibly buggy with respect towards acumatica and not worth utilizing. i have never been able to use it with success. I steer clients away from it. It is frankly a missed opportunity. 

Badge

One of two things changed that may have resolved issue

 

1)We were pulling from Inventory and ARtran joined. Initially we were pulling InventoryID from ARtran, we changed to pull from Inventory DAC and the problem stopped. 

 

2)We cuts the number of API requests that were running at the same time.

 

Good Luck

Userlevel 5
Badge +2

I’d love to hear if @Gabriel Michaud has seen sporadic errors with OData similar to the ones mentioned on this Discussion Topic, since Velixo is the most popular Acumatica ISV product out there and it depends completely on OData.

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 — 2022  Acumatica, Inc. All rights reserved