I have a problem with quite a few for loops that I have scripted and run on reasonably large foundsets.
The problem is that the script does not seem to update all the records that it should, given the criteria in the code. What seems to happen is that the script gets about half way though the records and then suddenly skips right to the end and finishes.
When you run the script again afterwards, it has a foundset of about half the size and does the same again, only updating half the records it should.
Only when you run the script a few times does it update all the records.
As I say, this has happened on nearly all of these type of methods I have written. The following is an example of one of them. This generated an inital found set of about 10000 records. (note that I have tried “wrapping” the loop into a transaction, but with the same result).
forms.table_barcode_details.controller.find();
forms.table_barcode_details.territory_id = '^';
forms.table_barcode_details.controller.search();
forms.table_barcode_details.controller.sort('territory_code asc');
var rows = databaseManager.getFoundSetCount(forms.table_barcode_details.foundset);
for ( var i = 1 ; i <= rows ; i++ )
{
forms.table_barcode_details.controller.setSelectedIndex(i);
var terCode = forms.table_barcode_details.territory_code;
if(terCode == null || terCode == '')
{
forms.table_barcode_details.territory_id = null;
forms.table_barcode_details.controller.saveData();
continue;
}
if(terCode == lastTerCode)
{
forms.table_barcode_details.territory_id = terCodeID;
forms.table_barcode_details.controller.saveData();
continue;
}
var query = "select territory_id from territories where territory_code = '" +terCode+ "'";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query,null,1);
var terCodeID = dataset.getValue(1,1);
forms.table_barcode_details.territory_id = terCodeID;
var lastTerCode = terCode;
forms.table_barcode_details.controller.saveData();
}
Any suggestions on what I may be doing wrong would be appreciated or comments from anyone else who has seen anything similar.
I have found that using the vRecord idea to reduce the CPU load is a good way to do it. However, even when using this method, I still get the problem that the script only updates around half the number of records that it should.
patrick:
Inspite of actual problem, don’t you think you can do this by one simple SQL statement that will update that field in seconds?
I dont know how I would do this with an sql statement.
The idea of that method in the example if to populate a field on table A with a value looked up from table B based on the value from another field on table A.
Thus: TableA.FieldA = Table2.FieldA where Table2.FieldB = Table1.fieldB
Today I recognized the same strange problem with a foundset which is larger than 200 records. The code looks like as follows:
for ( var i = 1 ; i <= foundset.getSize() ; i++ )
{
var vRecord = foundset.getRecord(i);
//create activity_id
var vID = new Array(true,5);
if ( vRecord && vID && vID[1] ) vRecord.activity_id = vID[1];
databaseManager.saveData();
}
Servoy has reduced the foundset with every “databaseManager.saveData()” by 1 record per loop. The result is that only the half foundset is processed. If I remove the line “databaseManager.saveData()”, Servoy loops well through all records.
Does anybody have an idea why Servoy is reducing the foundset with the “databaseManager.saveData()” by 1 record per loop?
For me, this looks like a very bad bug which only occurs in some special situation . I’m not able to build a sample solution, I can reproduce this in our development environment only.
The sample code above is only a short example of my situation. In my case, the code looks more complex and the “databaseManager.saveData()” is executed via a sub method. I’ve attached the method as a text file. The method is hanging at a form on which “useSeparateFoundSet” is selected and the foundset is filtered via “databaseManager.addTableFilterParam()” (but not by activity_id).
Thanks for help.
Servoy Developer
Version 3.1.7-build 411
Java version 1.6.0_02-b05 (Windows XP)
application.updateUI()
var tempPayment = 0
var tempBalance = 0
controller.find()
flag_batch_payment = 1
controller.search(false, true) // reduce search
for ( var i = 1 ; i <= controller.getMaxRecordIndex() ; i++ )
{
controller.setSelectedIndex(i)
tempPayment = provisional_payment.toFixed(2)
tempBalance = provisional_balance.toFixed(2)
provisional_payment = 0
var desc = "Payment by " + application.getValueListDisplayValue("PaymentType", globals.payment_type) +
(globals.payment_type == "C" ? " - " + globals.cheque_drawer : "")
forms.invoice_items.AddPaymentRecord(invoice_id, forms.payments.payment_id, globals.payment_date, tempPayment, desc, tempBalance)
controller.saveData()
application.updateUI()
}
databaseManager.recalculate(foundset)
return
It was driving me nuts, because a) it was an intermittent fault, with some but not all large data sets, and b) it worked perfectly when stepping through with the debugger. I’ll try the tips above and let you know if that improves things. I’ve wondered if increasing the memory allocated when starting Servoy would fix things.
Is the flag_batch_payment field a (stored) calculation ?
And if so does it check any of the fields you fill in the for loop ?
If that’s the case then it might be a case of Servoy automatically updating the foundset because your records no longer match the original search criteria.
Interesting case though. When should Servoy update the foundset, at the end of the method or during? And is that in ALL cases ?
Servoy automatically updating the foundset because your records no longer match the original search criteria
led me to the solution. With the following line of code:
if ( vRecord && vID && vID[1] ) vRecord.activity_id = vID[1];
the original search criteria is no longer matched. I think, the only secure way to process such a foundset, is to use a while loop with checking the foundset about size.
hpmxxx:
I think, the only secure way to process such a foundset, is to use a while loop with checking the foundset about size.!
Or you loop down like so:
for ( var i = foundset.getSize() ; i >= 1 ; i-- )
{
var vRecord = foundset.getRecord(i);
//create activity_id
var vID = new Array(true,5);
if ( vRecord && vID && vID[1] ) vRecord.activity_id = vID[1];
databaseManager.saveData();
}
ROCLASI:
Is the flag_batch_payment field a (stored) calculation ?
And if so does it check any of the fields you fill in the for loop ?
If that’s the case then it might be a case of Servoy automatically updating the foundset because your records no longer match the original search criteria.
Good thought. I’d hit that situation before, and changed the flag_batch_payment field from a stored calc to a regular integer field, set by a method.
As Patrick said
First, setting the selected index is very CPU intensive, because of frequent screen drawings.
I can understand it’s intensive and therefore slower the the vRecord method, but it’s reasonable to expect the for/loop should still work…
I’m having the same looping problem where the loop seems to skip several records at random. Have to run the method a couple of times to get all the records.
//sets aggregates in contacts, cleans dates of time junk
var curFoundSet = databaseManager.getFoundSetCount(forms.c3net_cmt_set_aggregates.foundset)
forms.c3net_con_import.controller.setSelectedIndex(1)
for ( var i = 0 ; i < curFoundSet ; i++ )
{
forms.c3net_cmt_set_aggregates.controller.setSelectedIndex(i+1)
This doesn’t happen when you comment out the saveData() function?
It almost sounds like a timing issue where saveData() is still doing stuff while the for loop is continuing. In any case I would suggest you file a bug report in the support system.
As a side note, looping through the foundset using the getRecord() function is MUCH faster than looping through the controller because it won’t trigger any screen redraws until it’s finished.
is the foundset where you loop over and change records from a related foundset? Because those are deleting records when you change then and they don’t match the relation anymore if that is the case you could do this before looping:
var fs = yourfoundset.unrelated()
and use the fs to loop.
Normal foundsets shouldn’t through out records when they don’t match, except ofcourse if somehow you search again.
Here’s my original code where we were having skipping problems and Richard’s modification.
/*
//sets aggregates in contacts, cleans dates of time junk
var curFoundSet = databaseManager.getFoundSetCount(forms.c3net_cmt_set_aggregates.foundset)
forms.c3net_cmt_set_aggregates.controller.setSelectedIndex(1)
for ( var i = 0 ; i < curFoundSet ; i++ )
{
forms.c3net_cmt_set_aggregates.controller.setSelectedIndex(i+1)