Wrong sum aggregation with search on related fields

Classic Servoy
1

I have a form based on table A which has an one to many relation with table B. On table A a sum aggregation is defined. The form is a table view with the sum aggregation in the footer section. When I do a find and search on a related field from table B. The form shows all the records from table A which have at least one related record in table B which satisfies the search condition. So far, so good. But the sum aggregation of the field in table A doesn’t show the sum of the shown records, which I expected. It shows a higher value if records from table A have more than one record in table B which satisfies the search condition.

Is this correct behavior or a bug ? Any suggestions on getting the right sum.

2

If you use the same relation for the calculation as you use for displaying the records, it should work.

I have a simmular application, but i always want to see the real total
(so also including records not shown due to a find/search).
I do this by creating a different relation (different name, the rest is the same)

I use calculations and not aggregations, but i expect it works the same way.

Regards,

Hans

3

Thanks for the reply, but I’m not using the relation to show things.
Maybe an example will clarify things:

Table A:
PK_A | Field1 | Field2
1 | First | 100
2 | Second | 50

Table B:
PK_B | FK_A | Field1
1 | 1 | Test one
2 | 1 | Test two
3 | 2 | Test one

Table A has sum aggregation on Field2: SumField2
And relation one to many from table A to B: relAtoB (PK_A - FK_A)

Show all records of table A gives:
1 First 100
2 Second 50

SumField2 150

With find on Field1 = ‘First’ gives:
1 First 100

SumField2 100

With find on relAtoB.Field1 = ‘Test one’ gives:
1 First 100
2 Second 50

SumField2 150

All seems Ok, but find on relAtoB.Field1 = ‘Test%’ gives:
1 First 100
2 Second 50

SumField2 250 !!

Servoy Developer
Version 3.5.6-build 519
Java version 10.0-b19 (Windows XP)

4

This is how sql works

your sum query is something like this:

select sum(tableb.Field2) from tablea, tableb where tablea.pk_a = tableb.fk_a and tableb.Field1 = ‘Test%’

this query will result in 3 rows :

1 First 100
1 First 100
2 Second 50

which is 250 total…

You dont see that second First because in the normal result set we filter all double pk’s. But we cant do that with a sum.