Grouped list

Dear Mendix folks, I'm new to Mendix and we're currently evaluating performance. I'm running into a problem where I could really use your help. I have a table containing policy information records for each premium period. Lets say, it looks like this: PolicyNumber;Year;Month;Premium;CommissionA;CommissionB What I would like to display, is a list of policies grouped by policy number, giving me a list like this: PolicyNumber;SUM(Premium);SUM(CommissionA);SUM(CommissionB) The source table is about 25M records large, with each policy number having between 1 and 100 premium periods, so I'm looking for a solution that's workable with these kind of numbers. How can I do this in Mendix? I've tried using a MicroFlow with a Retreive and AggregateList, but it doesn't seem to be what I'm looking for. Maybe I'm looking in the wrong direction. Thank you for you help! Kind regards, Stijn
2 answers

OQL datasets might do the trick.

Or, if premium and commissions are always fixed to policies, you could aggregate this data on commit as well, so that you can retrieve it really fast. (Assuming that you divide the data over different entities and relate them to each other with associations, rather than to dump everything into one type of records which is yuck)


I wrote this as an answer due to the limited comment space, but this is a reply to Marks answer:

Hello Mark,

Thank you for your fast reply. OQL seems to be able to do what I'm looking for, but further on this forum it's said that it isn't possible to use OQL results in microflows or datagrids.

This is however exactly what I'm looking for. I'm looking for a way to have this data available in a paged datagrid easy mode. I guess this only leaves your suggested option, to have a table for the totals, which is updated on each commit.

My suggestion would be to have the ability to have an entity that's read only and linked to a view or stored procedure. This would provide me with all the freedom in the world regarding complex lists and would still let me use the entity in datagrids and microflows.

If there is anyone else with other solutions that might help me I'd love to hear them!

Kind regards,