How to handle aggregate over empty set

Classic Servoy
1

I have an aggregate (sum) over a calculated field (quantity * price) using a related table. If the record set is empty for a particular relation (e.g. an order relates to order_items where no order items exist yet) then the result of the aggregate when showing as a field on a form is blank rather than the arithmetic sum of 0.0 that I was expecting.

I would like to show 0.0 instead of an empty blank field. Tests for null and undefined do not change the result.

Is this normal behaviour?

2

you could easily create a non stored calc that does that:

if(relation_to_relation.sum_field)
{
return relation_to_relation.sum_field
}
else
{
return 0
}
3

Just to shortcut the prior suggestion – I typically use the following:

return relation_to_relation.sum_field || 0;

g.

4

both suggestions are nice but what about checking that the relation really exists?

I would do ```
return utils.hasRecords(relation_to_relation) ? relation_to_relation.sum_field || 0 : 0;

5

Hi

As Marcel has stated, test for the related records first using utils.hasRecords, if they exists then do something. This is important with all calculated fields across relationships. If you don’t test it and there is no relationships parent to child, then you will see a lot of errors in the java console.

From my own experience. :oops:

6

yes, testing first by using databasemanager.hasRecords or utils.hasRecords is indeed better! :)

7

Could you explain why it’s better?

I ran the following test code against a solution I have, alternating between the two methods (see the commented line in the loop:

var fs = databaseManager.getFoundSet( 'bonded_projects', 'estimates' );
fs.loadAllRecords();
var ct = databaseManager.getFoundSetCount( fs );
application.output( "Processing " + ct + " records..." );

var st = new Date().getTime();
for( var ix=1; ix<=ct; ix++ )
{
	var rec = fs.getRecord(ix);
	var result = databaseManager.hasRecords( rec.estimates_to_activities ) ? rec.estimates_to_activities.activity_count : 0;
	//var result = rec.estimates_to_activities.activity_count || 0;
}
application.output( new Date().getTime() - st + " ticks" );

I ran it repeatedly to average results for other possible factors effecting perfomance. The dataset is about 1100 records, which a variety of records in the relations, ranging from lots of 0’s to 5-10 records in the relation.

The “hasRecords” version averages about 94 ticks to run, the “||” version averages about 63 ticks. That’s about a 30% gain, largely due to the overhead of the additional method call, I assume.

The results are the same. Is there a reason using “hasRecords” is a better or safer way to go with this?

greg.

8

It is documented but ‘hasRecords’ checks that there is an exisiting relation and it also checks that there are records.

Basically it is the same as```
if (relation != null && relation.size() > 0) {}

9

dern it. now i’ve got to upgrade my solutions.

though I’ve never seen errors with my method. perhaps, i’m just lucky though. :wink:

greg.

10

Well, within methods you probably check more stuff.

Within calculations it could cause unpredictable behavior, hard to debug.

11

Thanks guys for the great advice. What puzzles me though is the different places which have the “hasRecords” method. I would like to know if it is actually only a single method as the target in the servoy app or if there are multiple copies that do slighltly different checks depending on the parent calling.

This is only for interest sake. But it might be useful to point this out in the user guide.

The implementation works just great in my small number of records application. Thanks again.

Tom

12

Well, it started in the utils plugin.

Then it was moved to the databaseManager since it made more sense in there.

Finally it was available in both places since the databaseManager is not availabel in calculations…