We use unstored calculations in our solution.
This works fine.
But with certain calculations there is a problem when the calculated field is displayed in tableview.
The calculation is :
if(databaseManager.hasRecords(bvko_full_inkorderkaarten_to_inkorderspecregels))
{
Aantal = databaseManager.getFoundSetCount(bvko_full_inkorderkaarten_to_inkorderspecregels.foundset) // (getfoundsetcount ivm >200)
var _tot1 = 0.00;
for ( var i = 1 ; i <= Aantal ; i++)
{
var vRecord = bvko_full_inkorderkaarten_to_inkorderspecregels.getRecord(i);
_tot1 = _tot1 + (vRecord.inkorsrgl_ik_prijs * vRecord.calc_aantal_totaal)
}
return _tot1;
}
else
{
return 0;
}
In Recordview and Listview this always works.
In Tableview this gives an error, most of the time.
If i start the form in Tableview it goes wrong 18 out of 20 times with same data and form.
then the calculated field of some records is empty and there is an error (error triangle lower left corner)
But then again somtimes all fields have the correct calculation. So it is intermittend.
The error is :
Exception executing calculation: calc_ink of table inkorderkaarten, error: TypeError: Cannot convert null to an object. (calc_ink; line 9)
java.lang.RuntimeException: Exception executing calculation: calc_ink of table inkorderkaarten, error: TypeError: Cannot convert null to an object. (calc_ink; line 9)
TypeError: Cannot convert null to an object. (calc_ink; line 9)
I already created a case, but i am wondering if abyone has seen this before.
Hans Nieuwenhuis:
then the calculated field of some records is empty and there is an error (error triangle lower left corner)
This usually indicates that you have a problem that is due to invalid related data, check the error by doubleclicking the yellow triangle it will give you more hints about what you need to fix. Probably you have some child record with some null values and you need an extra check.
but this can not be an error due to invalid related data.
The same form with the same calculated field and the same data is oke in RecordView and ListView,
but gives errors in TableView. And one out of 10 times the tableview does not give an error.
Again with the same form. same data and same calculation.
I still think it’s due to some invalid child data, you can try this to find it out for sure: go to your record view form and navigate through the parent records until you see the yellow triangle indicating the error, when you see it check the realated record to see if there is incomplete data. If you NEVER see the error icon for the first 200 records, then I’m wrong and the problem lies elsewhere.
{
Aantal = databaseManager.getFoundSetCount(bvko_full_inkorderkaarten_to_inkorderspecregels.foundset) // (getfoundsetcount ivm >200)
var _tot1 = 0.00;
for ( var i = 1 ; i <= Aantal ; i++)
{
var vRecord = bvko_full_inkorderkaarten_to_inkorderspecregels.getRecord(i);
_tot1 = _tot1 + (vRecord.inkorsrgl_ik_prijs * vRecord.calc_aantal_totaal)
}
return _tot1;
}
else
{
return 0;
}
BTW, try to use this code instead, it’s faster and it’s not limited to 200 records:
if(databaseManager.hasRecords(bvko_full_inkorderkaarten_to_inkorderspecregels))
{
var _tot1 = 0.00;
for ( var i = 1 ; i <= bvko_full_inkorderkaarten_to_inkorderspecregels.foundset.getSize() ; i++)
{
var vRecord = bvko_full_inkorderkaarten_to_inkorderspecregels.getRecord(i);
_tot1 = _tot1 + (vRecord.inkorsrgl_ik_prijs * vRecord.calc_aantal_totaal)
}
return _tot1;
}
else
{
return 0;
}
And keep in mind that if for some child record you have a null inkorsrgl_ik_prijs or a null calc_aantal_totaal you will get a nullPointer exception (you cannot multiply per null), that is probably the reason for your sporadic errors.
First of all, in RecordView and ListView i can navigate trough all records without ever getting an error (triangle)
All of the records have a correct calculated value.
So it has to be something else…
b.t.w. getSize does not work with more then 200 childrecords i just tested this again on a foundset of more then 1000 and getSize
returns 200. getFoundsetCount returns the correct number of child records !!!
Maybe getSize works if you navigated trough all the records.
First of all, in RecordView and ListView i can navigate trough all records without ever getting an error (triangle)
All of the records have a correct calculated value.
So it has to be something else…
Search the child table for records that have inkorsrgl_ik_prijs or calc_aantal_totaal set to null or empty, if there are none then it could be a Servoy issue and then you will have to create a case for that.
Hans Nieuwenhuis:
b.t.w. getSize does not work with more then 200 childrecords i just tested this again on a foundset of more then 1000 and getSize
returns 200. getFoundsetCount returns the correct number of child records !!!
Maybe getSize works if you navigated trough all the records.
Regards and thanks again,
This is the correct syntax to loop on a foundset, if the foundset.getSize() is inside the for{} block it is evaluated at every recurrence, that’s why when you check it in the debugger you see 200, but if you check it again after the i>200 you will see 400 there. That’s the correct way to do it.
What i realy need is the sum of the calculated field of related child records.
I managed to simplify the calculation by creating a calculated field on the child record
In the calculation of the master record i can now just summarize the calculated field of the
related child records.
if(databaseManager.hasRecords(bvko_full_inkorderkaarten_to_inkorderspecregels))
{
Aantal = databaseManager.getFoundSetCount(bvko_full_inkorderkaarten_to_inkorderspecregels.foundset) // (getfoundsetcount ivm >200)
var _tot1 = 0.00;
for ( var i = 1 ; i <= Aantal ; i++)
{
var vRecord = bvko_full_inkorderkaarten_to_inkorderspecregels.getRecord(i);
_tot1 = _tot1 + vRecord.calc_inkoop_prijs_totaal
}
return _tot1;
}
else
{
return 0;
}
This helps, but it still gives an error sometimes (not nearly as much as before) in tableview.
Harjo : You suggest to use a query.
Can you use a query in an unstored calculation ?
And will it be recalculated automatically if the child records are changed ?? i don not think so.
I could also simplify things by using stored calculations, but i think it is not
good practice to store information twice.
I just created a calculation using a sql-query :
(B.t.w. this is the way i was used to do it in Oracle Forms, love to see that this also works in Servoy !!! )
var _maxReturnedRows = -1
var _query = 'select nvl(sum(s.inkorsrgl_aantal * inkorsrgl_ik_prijs * v.verkor_aantal),0) from inkorderspecregels s, verkorderregels v where s.inkpordkrt_inkpordkrt_id = ? and s.verkor_verkor_id=v.verkor_id'
var _args = new Array();
_args[0] = inkpordkrt_id
var _dataset = databaseManager.getDataSetByQuery('berp2', _query, _args, _maxReturnedRows);
return(_dataset.getValue(1,1))
This works fine and also there are no more errors in tableview !!
It also seems to get recalculated when i change the underlying childrecords, although i do not understand why is does this.
Maybe a Servoyan can explain the mechanism that is used to update (unstored) calculations ?
I would also like to have confirmation from Servoy that using a query in a calculation is supported
Harjo and Nicola thanks !! you put me in the right direction !!
I don’t think that sql queries are supported in calculations, because that’s why you dont see the databaseManager when you are inside a calculation.
They did that, because you can do all kind of crazy things there, that would Servoy choke!
But if you really know what you are doing, this is working great!
We use it also in some situations.
If another user changes one of the child records the databroadcasting takes care of updating the child record
on my screen and also the calculated fields off that child record.
But the new calculation on the master record with only the sql query is not updated.
The old calcuation I used (with the loops) does get updated if another user changes a child record.
I think this is due to the fact that there is no field of the child record used in the calculation of the master record.
I changed the calculation and put in a few dummy lines that use a field from the child record.
And now the master calculation is also updated if another user changes one of the child records.
New code :
//next 5 lines are dummies to take care of databroadcasting
if(databaseManager.hasRecords(bvko_full_inkorderkaarten_to_inkorderspecregels))
{
var vRecord = bvko_full_inkorderkaarten_to_inkorderspecregels.getRecord(1);
var _dummy = vRecord.calc_inkoop_prijs_totaal
}
//
var _maxReturnedRows = -1
var _query = 'select nvl(sum(s.inkorsrgl_aantal * inkorsrgl_ik_prijs * v.verkor_aantal),0) from inkorderspecregels s, verkorderregels v where s.inkpordkrt_inkpordkrt_id = ? and s.verkor_verkor_id=v.verkor_id'
var _args = new Array();
_args[0] = inkpordkrt_id
var _dataset = databaseManager.getDataSetByQuery('berp2', _query, _args, _maxReturnedRows);
return(_dataset.getValue(1,1))
Harjo, did you also notice this and did you also create some kind of workaround ?
Tested again with only the sql statement in the calculation and now it does update when another user
changes the child records.
var _maxReturnedRows = -1
var _query = 'select nvl(sum(s.inkorsrgl_aantal * inkorsrgl_ik_prijs * v.verkor_aantal),0) from inkorderspecregels s, verkorderregels v where s.inkpordkrt_inkpordkrt_id = ? and s.verkor_verkor_id=v.verkor_id'
var _args = new Array();
_args[0] = inkpordkrt_id
var _dataset = databaseManager.getDataSetByQuery('berp2', _query, _args, _maxReturnedRows);
return(_dataset.getValue(1,1))
Maybe it happenend because i tested by starting two smartclients on the same laptop and logging
in twice as the same user ? or there was a problem with databroadcasting during the first test.
Again i would like to know what the mechanism is that updates calculatations.
Aantal = databaseManager.getFoundSetCount(bvko_full_inkorderkaarten_to_inkorderspecregels.foundset) // (getfoundsetcount ivm >200)
here you define Aantal as top level variable, and so the calc won’t be thread safe anymore (in tableview the calc for each row is done asynch),
that is why you got the error.
try do define the var as method variable :
var Aantal = databaseManager.getFoundSetCount(bvko_full_inkorderkaarten_to_inkorderspecregels.foundset) // (getfoundsetcount ivm >200)
I changed this and it works fine now.
But, i will not use these kind of complicated calculations anymore, i am now using stored functions.
I normaly always (almost always) declare all variables, this proofs how important that is.