MaxRetrieveSize blocking Export with XLSReport module?

0
I have a page that contains a listview that can be exported to an excel sheet. Because it's a listview I'm using the XLSReport module for my exporting needs.    I'm running into an issue where any export over 1000 records is erroring out, due to the MaxRetrieveSize runtime setting, according to the logs.    This feels like a very small value for it to be set at so I'm concerned that increasing it specifically to allow this export might effect other areas unintentionally. Ideally I want to be exporting 40k-50k records at a time (this export would happen around once per month). Is there an established way to resolve this issue? 1000 records feels very small for an excel export so I feel like I'm either missing something or maybe misinterpreting the issue.    Any and all help is greatly appreciated!   Edit: including error from live logs A retrieve-by-path action 'Module.Helper_ExportEntity' attempted to load more objects then allowed by the `MaxRetrieveSize` runtime setting. ERRORConnector: An error has occurred while handling the request. com.mendix.systemwideinterfaces.MendixRuntimeException: Retrieve_by_ids action tried to retrieve more objects then allowed by the `MaxRetrieveSize`. Amount of guids requested: 3741, max amount of objects allowed: 1000 the retrieve by path warning is triggered because the DS for the listview will attach the objects to display to the helper entity after retrieval, for easy re-retrieval upon filtering. I'm realizing that might not be the best design. The export button uses this same DS microflow to filter the data before export so it is retrieving all the records via that association. Again that's not when it errors, but still relevant. 
asked
2 answers
1

Hi Ben,

MaxRetrieveSize is a setting related to the web client (see documentation here: https://docs.mendix.com/refguide10/custom-settings/#MaxRetrieveSize).  The XLSReport Java action is running on the server, not in the client.  So I don't think that your error is occurring due to this setting.  What I suspect is happening is that your runtime memory is filling up.  Have you tried test cases that are just a bit bigger than 1000 records (maybe 1500, or 2500) to see if you still get an error?  Another thing that may help in understanding this issue:  if you are running this in Studio Pro, after you open the page that has the microflow button you click to initiate this process, clear the console using the Clear button on the console tab.  Then, after you click the microflow button, you'll only see messages related to the microflow execution.

 

If you have tried those things, can you post further updates here with additional info you find?

 

Mike

answered
0

You can set up a batching structure where you retrieve the data in chunks of 1,000 records, iterate through each chunk, and for every record create a temporary export object (for example TempExportRow) that contains the values you want to write into Excel. Then you associate those TempExportRow objects to a single “export header” object (for example TempExport) which you use as the input/context for the Excel Exporter template. Once all batches are processed, you run the Excel generation once using that accumulated dataset and provide the resulting file for download.

 

For large exports, it’s also a good idea to run the whole flow asynchronously (for example via Task Queue). That way the user doesn’t have to wait on the page, and you can notify them when the file is ready or show it in a “Downloads” list. This also reduces the risk of timeouts and keeps your UI responsive while the export is being built in the background.

answered