Calculated field

Classic Servoy
1

Hi,

I have a form with a related form displayed in a tabpanel.

A calculated field is shown in the main form based on fields in the related form.

Aantal = databaseManager.getFoundSetCount (bvko_offerteregels_to_inkoopspecregels.foundset) 
var _tot1 = 0.00
for ( var i = 1 ; i <= Aantal ; i++ )
{
var vRecord = bvko_offerteregels_to_inkoopspecregels.getRecord(i); 
_tot1 = _tot1 + (vRecord.inkospcrgl_vk_prijs * vRecord.inkospcrgl_aantal)
}
return _tot1;

This works fine but :

if the users executes a find/search in the tabbed form , the amount
in the calculated field changes.

That is not what I am looking for. I want the calculation to be always based on all the related records, not just the records the user has searched for.

Regards.

Hans

2

Your calculation is dependent on the foundset you are “producing”. So it has to change when the foundset changes. You can use an aggregation to accomplish what you want…

3

Thanks Patrick,

Do you mean i can put the code from the calculated field into an aggregated field or does this take more ?

4

Say you have two tables, customers and orders. In orders you can create an aggregation that sums a field with the amount. Now in customers you can create a calculation, that simply returns that aggregate over the relation. This should always be the same for each customer.

5

Maybe i am missing something :

I need to have a calculated field in the base form that is
the sum of a product of two fields in the tab-form.

so : Baseform.calc_field = sum-of-product(tab-field1 * tab-field2) for all records in tab-form

I do not see how to do this with an aggregation.

6

You are right. If you want to use an aggregation, you need to store the result of the product.

7

Is there a way to make the calculation work on a different foundset
of the same data ?
That way there would be no influence of a search in the tab-form.

8

The solution was so simple ! :idea:

Thanks to Sanneke.

Just create another relation between the two tables and use
that in the calculation.