Generated aggregate fields

[vasa.nikola]

Is it possible to have auto-generated field inside one module which value will hold the result of some aggregate function executed on related modules. For example, I have “Accounts” and “Payments” modules which are in one-to-many relationship. So I would like to have field “current_balance” inside account which will be calculated as sum of “amount” field in “Payments” module for related rows? I would like to show that “current_balance” field inside each row of ListView “Accounts” layout. It’s not enough for me to show that in “Reports” only.

[pgr]

Hi, welcome to the Community!

Are you a developer? I need to know what level of “technical” you can handle…

There are two approaches to this:

• override the list view so that when it’s displaying a row, it will be intercepted and a new query will occur to come up with the aggregated data and show it. This will be impacting for performance. Something like this, but instead of making a formatting change, you actually run some SQL and change the value shown.

• have that value prepared beforehand to be shown. You’d have to hook every relevant update to the payments and update the total in a custom field of accounts. Then you could simply show it on the List view.

[vasa.nikola]

Hi, thanks for the fast answer. Yes, I am developer so I can handle any “technical” stuff.

I will need to have about 10 fields for each row which should be generated like this, and there are a lot of data, so performance is important. Definitely, the second option you proposed looks better. But, in the near future I have to implement an option to parametrize these queries with date range which will be used for aggregation, so I am not sure is that possible. For example, it would be great if I can setup a button (range-picker) below listView which needs to recalculate that fields based on date-range selected. Because of that variable ranges, it’s not so good for me to hook every relevant update, because I don’t know which range will be chosen by user. Any ideas?

[pgr]

Maybe something like this

Subpanels Cases

Let me try giving you a copy of a different example. My advice is to first try to get my example working as it is, without changing anything; only after it’s working you try and adapt it to your requirement, ok? Create this file custom/Extension/modules/Accounts/Ext/Layoutdefs/Leads.php with these contents: <?php $layout_defs["Accounts"]["subpanel_setup"]['leads'] = array( 'order' => 80, 'module' => 'Leads', 'sort_order' => 'asc', 'sort_by' => 'first_name, last_name', 's…

You can make your own SQL query. It’s for a subpanel, I am not sure if it will work on List views, probably yes.

This should make all the hard processing be handled by MySQL, and everything will be done with one JOIN query for all rows, instead of a separate query per row. this should probably bring the performance into acceptable levels.