aggregating fields in a related table?

Release notes for Servoy betas

aggregating fields in a related table?

Postby Neale » Mon Jun 02, 2003 5:20 am

We've been trying (unsuccessfully) to aggregate (specifically, to sum) values from a related table.

The problem appears to be that when we have a form based on the parent table an aggregate of a field in the child table **in the Trailing Grand Summary** only aggregates children of the current record.

Surely in the Trailing Grand Summary the aggregate should be over all the children of the entire foundset?

Thanks,
Neale.
Neale
 
Posts: 230
Joined: Thu May 15, 2003 6:29 am
Location: Australia

Postby maarten » Mon Jun 02, 2003 10:32 am

Hi Neale,

Let's assume we have an orderList with orderTotals based on orderLines.

We want to make an aggregate on all orderTotals, but the problem is indeed we cannot make an aggregate on the related orderLines of the WHOLE orderList.

We deliberately decided not to explicitely provide the calculation "sum(relation:fieldname)"
like in Filemaker, cause it's quite a heavy operation on the database.
(with larger datasets)

However you can do it this way:

In orders, create a stored calculation:
1) create an integer column named "orderTotal"
2) create a calculation also named "orderTotal" and enter:
----
var orderTotal = 0
for(var i = 1 ; i<=orders_to_line_items.getMaxRecordIndex() ; i++)
{
orders_to_line_items.recordIndex=i
orderTotal = orderTotal + orders_to_line_items.line_total
}

return orderTotal
----
3) make an aggregate "grandTotal" on orderTotal.
Maarten Berkenbosch
User avatar
maarten
 
Posts: 797
Joined: Wed Apr 23, 2003 10:52 pm
Location: Amersfoort, Netherlands

Postby Neale » Mon Jun 02, 2003 1:45 pm

Hi Maarten,

Thanks for the reply, but I do not find it at all encouraging.

Firstly, I am now puzzled as to how to rationalise this with the allegation in the documentation that "You can also use aggreations in related forms as the foundset will be set automatically to the relation"

Also, the calculation "sum(relation:fieldname)" may not be provided - but Servoy gives the appearance of providing it (or more correctly of providing "relation.sum(fieldname)". There is, I concede, some scope for ambiguity in such a construct but the placement of this in the Trailing Grand Summary should either (a) trigger a warning/error or (b) clarify the potential ambiguity and sum over all the related records.

Regarding the objection that the desired functionality would be computationally expensive: OK, that is, IMHO, good reason for a warning but not to omit what is arguably basic functionality. The fact that an operation is computationally expensive may, IMHO, be construed as a very good argument for implementing it - where the computational expense derives from the nature of the underlying question then getting a computer to labour over the matter may well be the most practical solution (I'm not volunteering to substitute myself for the computer in these matters!).

Lastly, in matters which are computationally expensive, I much prefer correct answers to either immediate answers or to misleading answers.

Thanks,
Neale.
Neale
 
Posts: 230
Joined: Thu May 15, 2003 6:29 am
Location: Australia

Postby maarten » Mon Jun 02, 2003 3:39 pm

Firstly, I am now puzzled as to how to rationalise this with the allegation in the documentation that "You can also use aggreations in related forms as the foundset will be set automatically to the relation"

You can aggregate on one parentrecord to many childRecords.
NOT on a foundset of parentrecords to all their childrecords.

Also, the calculation "sum(relation:fieldname)" may not be provided - but Servoy gives the appearance of providing it (or more correctly of providing "relation.sum(fieldname)".

Yes, Servoy does provide this as explained.

There is, I concede, some scope for ambiguity in such a construct but the placement of this in the Trailing Grand Summary should either (a) trigger a warning/error or (b) clarify the potential ambiguity and sum over all the related records.

In list views, just use footers/headers. Aggregates don't need to be in TGSummaries.


Regarding the objection that the desired functionality would be computationally expensive: OK, that is, IMHO, good reason for a warning but not to omit what is arguably basic functionality. The fact that an operation is computationally expensive may, IMHO, be construed as a very good argument for implementing it - where the computational expense derives from the nature of the underlying question then getting a computer to labour over the matter may well be the most practical solution (I'm not volunteering to substitute myself for the computer in these matters!).

This functionality isn't omitted, as example showed.

Lastly, in matters which are computationally expensive, I much prefer correct answers to either immediate answers or to misleading answers.

Aren't you lucky getting immediate AND correct anwsers twice? :wink:

So bottomline, as a rule of thumb:
1)Use aggregates in headers and footers. Use Trailing grand summaries for subsummary reports in preview mode.
2)If there's a related column in your list, that aggregates on childrecords,
create a stored calc (see example) and aggregate on it.

Thanks.
Maarten Berkenbosch
User avatar
maarten
 
Posts: 797
Joined: Wed Apr 23, 2003 10:52 pm
Location: Amersfoort, Netherlands

Postby Neale » Tue Jun 03, 2003 12:47 pm

maarten wrote:You can aggregate on one parentrecord to many childRecords.
NOT on a foundset of parentrecords to all their childrecords.

OK, point clarified - thanks. That sentence (or a slight rephrasing thereof) would be a worthy addition to the documentation (under "Creating Aggregates").

maarten wrote:
Neale wrote:Also, the calculation "sum(relation:fieldname)" may not be provided - but Servoy gives the appearance of providing it (or more correctly of providing "relation.sum(fieldname)".

Yes, Servoy does provide this as explained.

I think we have different things in mind here - I thought I was addressing the point "We deliberately decided not to explicitely provide the calculation "sum(relation:fieldname)" like in Filemaker"?

maarten wrote:
Neale wrote:There is, I concede, some scope for ambiguity in such a construct but the placement of this in the Trailing Grand Summary should either (a) trigger a warning/error or (b) clarify the potential ambiguity and sum over all the related records.

In list views, just use footers/headers. Aggregates don't need to be in TGSummaries.

AFAICT, the behaviour is the same in footers/headers and TGSummaries. I think the relevant point is that IMHO an aggregate of a child record field which is placed in either a subsummary or TGS only makes sense if it aggregates over all the children of the relevant foundset of parent records.

maarten wrote:
Neale wrote:Regarding the objection that the desired functionality would be computationally expensive: OK, that is, IMHO, good reason for a warning but not to omit what is arguably basic functionality. The fact that an operation is computationally expensive may, IMHO, be construed as a very good argument for implementing it - where the computational expense derives from the nature of the underlying question then getting a computer to labour over the matter may well be the most practical solution (I'm not volunteering to substitute myself for the computer in these matters!).

This functionality isn't omitted, as example showed.

OK, fair point. But is there any reason why we can't have it in a more convenient form?
May I propose two feature requests?...
(1) a JS function which has the functionality of your example
(2) an aggregation mode which sums/counts/etc over all the children of the relevant foundset of parent records.

maarten wrote:
Neale wrote:Lastly, in matters which are computationally expensive, I much prefer correct answers to either immediate answers or to misleading answers.

Aren't you lucky getting immediate AND correct anwsers twice? :wink:

Indeed. And it is appreciated - Thank You :-)

Thanks,
Neale.
Neale
 
Posts: 230
Joined: Thu May 15, 2003 6:29 am
Location: Australia

Postby maarten » Wed Jun 04, 2003 8:28 pm

Hi Neale,

I understand your point.

I will ask our editor to clarify the manual a bit on summarizing childrecords of a found parent set
Also added a requested feature for...

"sum(relation:fieldname)"

instead of....


var orderTotal = 0
for(var i = 1 ; i<=orders_to_line_items.getMaxRecordIndex() ; i++)
{
orders_to_line_items.recordIndex=i
orderTotal = orderTotal + orders_to_line_items.line_total
}
return orderTotal


Thanks for your input.
Maarten Berkenbosch
User avatar
maarten
 
Posts: 797
Joined: Wed Apr 23, 2003 10:52 pm
Location: Amersfoort, Netherlands

Postby Riccardino » Wed Mar 17, 2004 5:29 pm

maarten wrote:
"sum(relation:fieldname)"

instead of....


var orderTotal = 0
for(var i = 1 ; i<=orders_to_line_items.getMaxRecordIndex() ; i++)
{
orders_to_line_items.recordIndex=i
orderTotal = orderTotal + orders_to_line_items.line_total
}
return orderTotal



What happens if I create an aggregation in the related table and then
create a calc field in the parent table retrieving this value via a relationship?
For instance, let's say that line_items.order_total is given by aggregating line_items.line_total, is it wrong or too expensive to create a calc in
orders table returning orders_to_line_items.order_total?
User avatar
Riccardino
 
Posts: 911
Joined: Thu Apr 24, 2003 11:42 am
Location: Ferrara, Italy

Postby jcompagner » Wed Mar 17, 2004 11:21 pm

no that will work fine..

that is the way it should be done what is asked in the first post..
create a stored calc in orders that stores the aggregate of the relation.
Then make an aggreation over that stored calculation and you are in business. And is much faster then the loop through all the related records.
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8828
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Postby antonio » Thu Apr 27, 2006 1:07 am

maarten wrote:
Also added a requested feature for...

"sum(relation:fieldname)"



Has this feature been added, and would it be quicker than using an agregate field in the child table, which seems to slow down with large datasets.

Or... would I be better using an SQL query, to optimize spped?
Tony
Servoy 8 - 2022.03 LTS
antonio
 
Posts: 638
Joined: Sun Apr 02, 2006 2:14 am
Location: Australia

Postby jcompagner » Mon May 08, 2006 12:08 pm

antonio,

What do you mean an agregate field is slow and you can optimize it with SQL query?

A agregate field is just an sql query: select sum(dataprovider) where relation where args.

So sum(relation.fieldname) is just the same as relation.aggregate so we have that support.

Or i am missing something from this old thread?
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8828
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Postby antonio » Mon May 08, 2006 2:44 pm

Hi Johan, no you're not missing anything, I just wasn't sure if an aggregate field was functionally different to an SQL query. Now I know!
When I show a form with a list in a tab, viewing the form slows when sum fields are displayed on the main part of the form (eg invoice total, payments, balance). Any advice on the best way to optimise the performance?
Tony
Servoy 8 - 2022.03 LTS
antonio
 
Posts: 638
Joined: Sun Apr 02, 2006 2:14 am
Location: Australia

Postby jcompagner » Mon May 08, 2006 3:52 pm

look if it really are the sum queries.. You can see that in the db performace tab on the admin pages.

If it is make the right indexes for that sum query
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8828
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet


Return to Latest Releases

Who is online

Users browsing this forum: No registered users and 17 guests