display oldest date

Classic Servoy
1

Hello,

I am trying to create a calcualtion in my parent record that displays the most recent order date from the related child records.

Thanks in advance!

2

did you try an aggregate trough a relation?

create an aggregate MAX (for newest date) or MIN (for oldest date) on that date and than do this is an a calculation:

if(parent_to_child.myaggregate) {
return parent_to_child.myaggregate
}

you could also do that by a relation: parent_to_child and sort asc or desc on that date, and take the first one.

3

Harjo:
did you try an aggregate trough a relation?

create an aggregate MAX (for newest date) or MIN (for oldest date) on that date and than do this is an a calculation:

if(parent_to_child.myaggregate) {
return parent_to_child.myaggregate
}

you could also do that by a relation: parent_to_child and sort asc or desc on that date, and take the first one.

I would vote for that second approach. MAX and MIN can be very expensive functions in a database in my experience.

kazar

4

As we learned yesterday at the VUG (thanks Adrian) Servoy will trigger the aggregates when you load the foundset no matter if you needed the aggregate or not.
So depending on your need you might even get the MIN(date) via a getDataSetByQuery() function so you have full control on when this is triggered.

Hope this helps.

5

thanks for the help. I did have it originally displayed as a sorted relation, and did not think about creating a agg. They both work well i just thought that there might simple code that i was missing that might be more effiecnt. It can’t be much easier then through the relation. Thanks for the help again!

6

ROCLASI:
As we learned yesterday at the VUG (thanks Adrian) Servoy will trigger the aggregates when you load the foundset no matter if you needed the aggregate or not.

thats only partial true.

If you load the foundset but dont touch any of the aggregates, the aggregates wont be queried.

But if you ask for 1 aggregate we do a query for all of them at once so that we dont have to do a aggregate query per aggregate.

But yes this has the drawback that if 1 of the aggregates are expensive that that if you ask for the another 1 first that you have to wait for that expensive one also.

7

What Robert was trying to say, that if you insert a new record, trhough a relation. like this:

parent_to_child.newRecord()
databaseManager. saveData()

also a LOT of queries are fired and aggreates are fired, instead of just one insert query

This was shown in the VUG meeting, Adrian presented.

8

just test it your self then

make a orders form and a orderdetail form in a tabpanel through a relation.
add an aggreate on the orderdetail table

place a button on the orders form that does newRecords() on the relation.

first dont show the aggregate. (but do have 1)

then you see when you press the button only 1 sql statement is generated and that is the insert.

now place the aggregate in the ui.

then you see when creating a new record 2 sql statements. 1 insert and the other the aggregate.

So an aggregate is only being queried when you ask for 1 of the aggregaties of that table.

Also if you see normal (none aggregate) select statements on the orderdetail table then that means that you have other related foundsets that also find that that new record
could fall into his own set. And those relations will update them selfs. But that is also only done if that relation is used somewhere. So in the ui or in scripting.