more summary problems

Classic Servoy
1

I am attaching a simple test solution which demonstrates a problem with summaries not updating correctly.

Basically I have a summary field which totals a calculation based on an entered number and a global value used as a multiplier. If I change the global all the calculations correctly update, but the summary field does not reflect the changes. What appears to happen is that the summary only reflects the changes in those fields that are actually visible (implying that maybe the calculation is not updating until the row becomes visible?). Any summary placed in a grand summary part does not update at all until a field in a row is actually clicked on - then it updates to show whatever value is currently in the summary (not always correct).

Try this: in the attached ‘testbed’ change the multiplier number (a global) . The calc1 field updates in all visible rows. The summary at the end of the rows updates but only for the visible rows. Scroll the list and the summary value at the ends of the row changes as you scroll more rows into view! The summary at the bottom does not update again until a row is clicked.

Switch into and out of designer mode and the global will be cleared, but again the summary field does not update - scroll the list to the bottom and the summary value gradually drops until it reaches zero by the bottom of the list!

If anyone has a good explanation for this I would love to hear it - otherwise it looks like a bug to me.

2

sorry - first attached solution was not quite correct (summary field was incorrect). this solution ‘sumTest’ shows the problem more clearly. The above comments still apply.

3

I hope that what you do is not an example of youre real solution?
Because having a stored calculation depending on a global and over that stored calc you have an aggregate. This is sort of a worsed case scenario..

First of all if you change the global then only the stored calculations that are on screen/displayed are recalculated. Not youre complete foundset.
For that you have scroll through youre foundset youreself. One time accessing the stored calculation. We can’t do that because we don’t know if we can do that if have 1000+ records that should be updated suddenly. So you have to really know what you are doing!!

we will try to make this a bit better in the further. But this is not very easy to do..
I always try to say that if you use globals or aggregates in calculations don’t store that calculation.

4

jcompagner:
I always try to say that if you use globals or aggregates in calculations don’t store that calculation.

Since getDatasetbyQuery is such a performance boost compared to other ways of reporting data, I use it very often. Anyway, in certain circumstances I need to put togeter calculated data coming from different relations. Since my knowledge of SQL is quite limited, sometimes I find much easier to design the relation in servoy, create the calc field, store it and access the stored calc via SQL.
I know is not a orthodox way of using queries, but can “pure” SQL do ANYTHING you can do with relations and calcs in Servoy?
If yes… well, I’ll have to subtract time to Servoy to concentrate on SQL ;-)

Anyway, just to give you a couple of quick examples, is it possible to write a query that retrieves
1)
the offerid, offer_grandtotal (relation: offer_to_lineitems), offer_inprogress_total (relation:all linetems of this offer marked with “in progress”)
offer_completed_total (relation:all linetems of this offer marked with “completed”)
etc

If I have a formula that takes an amount from a field and, depending if it’s an income or an expense AND depending on payment method, puts this amount into field CASH_IN, CASH_OUT, BANK_IN, BANK_OUT.
Can this be done “on the fly” with SQL?

Forget my naivete’, but SQL is still quite an alien object, for me. ;-)

5

why would you try to do that youreself with sql?

1>
offer_grandtotal.offer_to_lineitems , offer_completed_total are just aggregates of the relation they build on.

2>
one way is to just make 4 calcs (CASH_IN/OUT, BANK_IN/OUT)
and let the calculation fill its field.

So i don’t see why these 2 are related of using aggregates or globals inside stored calculations.

6

My experiece with SQL is that if the data is in the DB, you can extract it any way you like it: summeriezed, group by, ordered etc.

For “simple” examples of SQL queries, have a look at the SQL Report generator in the CRM demo that ships with Servoy (click The SQL Report button, and then drill down one level by clicking one of the reports shown).

The thing this Report engine is missing is inner and outer joins. This means: two related tables, where a record in one table might not have a related record in the other table. If you want to include those records as well in your resultset, you have to start working with inner and outer joins.

Also, SQL gives you the oppertunity to nest select statements in other select statements, just as far as you want to go.

Now, there is one downfall: you have standard SQL and DB orientated SQL. For example an If..Then..Else kind of function: This is not part of standard SQL, but for example Oracle offers the Decode(…) statement, that replaces If…Then…Else. I think Sybase offers a simmilar function, but I haven’t looked into that.

So, in conclusion: SQL pretty much does all you want. The only constraint is performance, but then again: If you try to achieve the same through Servoy relations, Servoy also has to collect the data and since Servoy, because of being so flexible cannot do it just as efficient as a single SQL select, I guess this is no issue here.

As for your example 1:

Select o.offerid,
sum(li.amount) as offer_grandtotal,
sum(sub1.amount) as in_progress,
(select sum(li3.amount)
from lineitems li3
where li3.marked = ‘completed’
and li3.offerid = o.offerid) as offer_completed_total
from offer o,
lineitems li,
(select *
from lineitmes li2
where li2.marked = ‘in progress’) sub1
where o.offerid = li.offerid
and sub1.offerid(+) = o.offerid

Explanation:
Basically, this SQL relates two existing tables directly (offers and lineitems).

Then it also links in a fictional table, which you create by a subselect (sub1). This sub1 is also linked to the offer table. The (+) means that if there are no related records in the sub1, the parent record in the offertable will still be displayed in your resultset. The field In_Progress will hold a nullvalue in your result set. (The (+) is the Oracle notation for defining inner and outer joins, so will not work on Sybase.)

The third part is the subselect in the Select … part of the SQL statement. This subselect results in the “offer_completed_total” field. This subselect is linked to the mainselect by “and li3.offerid = o.offerid” within the subselect. If this subselect would not find any records, the matching field in the resultsset will be null again.

Hope this all makes sense and will get you going. If there are questions, don’t hesitate to ask and I’ll try to answer… :D

Paul

7

jcompagner:
why would you try to do that youreself with sql?

1>
offer_grandtotal.offer_to_lineitems , offer_completed_total are just aggregates of the relation they build on.

2>
one way is to just make 4 calcs (CASH_IN/OUT, BANK_IN/OUT)
and let the calculation fill its field.

So i don’t see why these 2 are related of using aggregates or globals inside stored calculations.

Because showing them on a list view is VEEEERY slow (see http://forum.servoy.com/viewtopic.php?t=2043), so I’m storing those calcs so I can retrieve them with an SQL query.
But I also need to have a summary of them, so I’d like to know if storing them and then base a summary on them can be wrong or cause me problems.

8

pbakker:
Hope this all makes sense and will get you going. If there are questions, don’t hesitate to ask and I’ll try to answer… :D

Paul

First of all: it makes perfect sense and your example is precious (I’ll study it carefully) :)
What, in the past, stopped me with complex SQL queries (I often use simple ones and I’m pretty enthusiastic about them) was that not every command (such sub-queries, if I remember well: I was using MySQL and there was something wrong with them) is part of standard SQL grammar. Since I want to develop database-independent solutions, I couldn’t use them.
But if there’s no need to use non-standard SQL instructions to get the result I need… :wink:

9

what happens if you show youre list not as a listview but as a table view?
Then the loading of the relation.aggregation is done in the background. So you should scroll through the list pretty fast.

10

jcompagner:
I hope that what you do is not an example of youre real solution?
Because having a stored calculation depending on a global and over that stored calc you have an aggregate. This is sort of a worsed case scenario..

I have a tab with a list that is filtered out by up to 8 different criteria based on global field entries. In addition, a couple of calculations use global dates to work out how many repetitions of a task will fall into the specified date range and the cost of these tasks depends on the number of repetitions. At the bottom of the list I need a total of the costs. So yes, I do have calculations that use global values. Is it better to use the foundSetUpdater to put these values into real fields first and have the calculations based on these?

I always try to say that if you use globals or aggregates in calculations don’t store that calculation.

…but if I need a sum total aggregation of the calculation it has to be stored?

As the sum total is only needed when this list is viewed, what is the best way to temporarily get the total of a value in a found set of records where the value is a calculation? (I have a feeling that sql may have an answer)

11

I guess I’m dealing with a similair problem here. (I’ll attach a picture to show what I mean).

Column ‘n07’ is a calculation that returns ‘1’ if portalrow is red. In the aggragation column ‘koppelingendata_to_koppelingentransfer’ I SUM n07, but the SUM only works if n07 is visible in the portal. How can I have a valid SUM without having the no7 field in the portal?

12

a calculation will not be calculated if it is not asked for once..
So the value won’t be updated in the database so that the sum can find it.
You have to touch the calculation somehow..

13

a calculation will not be calculated if it is not asked for once..
So the value won’t be updated in the database so that the sum can find it.
You have to touch the calculation somehow..

I understand. Thanks for the info!