Aggregating over an indirect relationship

Classic Servoy
1

This time I’m trying to aggregate over an indirect relationship - i.e. I have tables a, b & c where c is child of b and b is child of a.

In a form displaying table a, I need to display the aggregate of an indirectily related field in table c. What is the correct/recommended way of handling this in Servoy?

Thanks,
Neale.

2

It can be done with:

  1. create calculation with “return relation_a_to_b.relation_b_to_c.aggregatename;” and place this calc on your form
    This one is not yet explained in the manual because you cannot ‘move’ this construct from the tree/list, but you have to type it yourselfs (this works many relations deep)

  2. place the aggregate as related field in tabpanel

3

Nice solution :-)

But, I think I missed something re using a tabpanel (and clearly it doesn’t work without it) - could you please elaborate on this point.

Thanks,
Neale.

4

Jan Blok was slightly mistaken (it’s hot currently in Europe 8) ), what he mentions only applies to non-aggregate values.

To aggregate over multiple relations you have to use a SQL query, for example:

select sum(t3.numb) from t1,t2,t3 where t1.t1 = t2.t1 and t2.t2 = t3.t2 group by t1.t1;

To get the results of this query onto your form do the following:

  1. create a label and place it where you want to see the results. Fill in the name property of the label (to make it show up in the method editor)
  2. create a method that says:
var query = "select sum(t3.numb) from t1,t2,t3 where t1.t1 = t2.t1 and t2.t2 = t3.t2 group by t1.t1";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturedRows);
elements.labelxxx.text = dataset.getValue(1,1);

replace labelxxx with the name you gave to the label earlier

Add this method to the onRecordSelection event so it will be triggered each time the record is selected.

5

Ah, thanks for the clarification - it works OK :-)

I presume that if I want to apply formatting (the sum is a money value) then I should either display it in a field (via a dataprovider which is a global var) -OR- do the formatting in JS (i.e. there appears to be no way to format the label text)?

This also brought up a method form/foundset/navigation query, which I’ll take up separately.

Thanks,
Neale.

PS: comiserations on the “heatwave” - I guess that’s like us here complaining that some weeks our daily maximums aren’t much above ten degrees!

6

correct, alternatively you can also format the number in your sql statement.