System was not able to get record count message when running GI

  • 12 April 2021
  • 6 replies
  • 47 views

We have a custom GI which is showing this message in the status bar:

Same query exists in two tenants but only one of them (the one with more data) shows the message.


6 replies

Userlevel 6
Badge +3

Hi, @RoyceLithgo,

I have verified in my system, GI is showing a record count for 10 million records as well.

Could you please let us know how much huge data you are trying to retrieve in the GI?

Thanks for the reply @Naveen B. I found that the problem had to do with some sort of corruption with the Site Map. When I removed the Query from the UI and added it back again (no other changes made at all), the record count appeared. So weird. 

Userlevel 6
Badge +3

That's great :) Thanks for sharing the update.

Userlevel 1
Badge

Hi @RoyceLithgo ,

I have also seen this message with complex custom GIs.  It can be intermittent, and in my experience it seems to be associated with the runtime of the query, not necessarily the final number of records returned.  In addition to the warning message, we noticed that aggregate totals do not display in this situation.  If you continue to have issues, depending on your requirements, a workaround may be to build a custom report using Report Designer.  On the report you can of course include the aggregate totals, and there is also the ability to define an Excel export if needed.

Laura

Userlevel 6
Badge +6

To calculate the count *and* display the results, the system needs to run two queries. To ensure responsiveness of the application, the Generic Inquiry system will wait for a predetermined amount of time (a few seconds -- I don’t remember the exact length). If the count of records is not returned in the allotted time, the query is canceled and the GI will only display the records.

More than likely, this means that you have a complex join expression or condition that requires full table scans instead of faster clustered index operations.

Analysis of the SQL Query Execution plan through SQL Management Studio is recommended in such cases. In some cases it can simply be because you’re missing a field in the relationship (ex: joining by OrderNbr instead of OrderType,OrderNbr). You might be missing an index somewhere as well.

Userlevel 6
Badge +3

Thanks a lot @Gabriel Michaud for the detailed explanation.

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