loopoing through records

Classic Servoy
1

Hi there,

I have an issue with looping through records.

frmObj = forms.frmCustomerList;
frmObj.controller.loadAllRecords();

for (var i=1; i <= frmObj.controller.getMaxRecordIndex(); i++) {
   frmObj.controller.setSelectedIndex(i);
   application.output(frmObj.controller.getSelectedIndex());
}

For example if the customer table contains 600 records, the above code loops through all the 600 records if the a form attached to a table to which the form frmCustomerList is attached to or the form frmCustomerList is in front. But any one of the above forms is not in the front, the loop stops at record 200.

Any idea why?

Servoy Developer
Version R2 2.2rc2-build 319
Java version 1.4.2_01-b06 (Windows 2000)

2

The above code is written inside a global script.

It is very important that the records loaded properly as I loop through the foundset. Say for example I send a broadcast email to a 1000 people and the broadcast ends at the first 200

Thanks for your attention.

3

In order to determine the “real” number of records to loop through you can use dataprovider.getTableCount. Beware of the “expensive operation” warning !
I think you need to use a combination getTableCount, hasmoredate and getMaxRecordIndex.

HTH

4

Using the controller of a form when you don’t need to see the data is not recommended.
Also the form controller fetches batches of 200 records each.
Apparently when you don’t show the form it won’t trigger to fetch the next 200 (etc.).

What you should use is the databasemanager object.
You just ‘feed’ it the foundset of your form and you are set to go.
Like this example to get the record count:

var recCount = databaseManager.getFoundSetCount(forms.myForm.foundset);

Ofcourse this is an expensive command on large data-sets.

You say you want to use this to make mailings.
So that means you need only email addresses from that table…right?
So why not fetch all the email addresses straight into an array, like this:

var emailArray = databaseManager.getFoundSetDataProviderAsArray(forms.myForm.foundset, 'emailColumnName');

Looping through an array goes much faster than looping through the database.
And you don’t even have to loop to get these addresses into one string for usage with the mail plugin.
Just use the join method:

var emailString = emailArray.join(',');

This transforms the array into a string where the addresses are divided by a comma.

Hope this helps.

5

i will check this out why it is not running beyond that limit, it should do that.
But what you should use is the foundset itself.
Don’t go through the controller but get the foundset and walk through the foundset (by getRecord())

But it can be that you really need to set the selection then going through the controller should be ok.

6

just tested this:

var frmObj = forms.form2;
frmObj.controller.loadAllRecords();

for (var i=1; i <= frmObj.controller.getMaxRecordIndex(); i++) {
   frmObj.controller.setSelectedIndex(i);
   application.output(frmObj.controller.getSelectedIndex());
}

on a solution with 2 forms.
one form2 with many records
and form1 that has a button that triggers that piece of code.
It prints all the selected indexen.

That piece of code was first in the form1 itself and later on i also moved it to a global method. But this all kept working fine.

7

I have the same problem. I have a global method that wants to loop through the records using setSelectedIndex. It stops at record 200.

8

I have the same sort of issue looping through a related foundset. It stops at 40, while there are 100+ related records.

When I place a portal on the form I’m working on, based on the same relation I’m trying to loop through, it also shows 40 related records initially, but loads the rest when I select the fourtiest record.

Basing my for loop on

databaseManager.getFoundSetCount(relationname)

instead of:

relationname.getSize()
``` solves the issue.

Paul
9

Paul did you have this: relationname.getSize()
inside the loop?

I don’t think so! :)

Because it works for me.

10

What do you mean, inside the loop?

If got:
for (var i = 0; i < relationname.getsize(); i++)
{
yadayadayadad…
}

11

that’s strange indeed. I consider this as bug indeed.

btw: looping trough a related foundset is rather slow.
you can loop much, much quicker on the form itself, like this

forms.relatedform.loadRecords(relation_to_relation) // bring the relatedfoundset to that form

and than on the form itself loop like this:

for(var i=1; i<=foundset.getSize(); i++)
{
var record = foundset.getRecord(i);
record.dataprovider = XXXXX; // for example: record.companyname = 'company: ’ + companyname
}

12

I don’t have a form based on this relation, so I would have to create it, just for this loop.

Since the method with loop is called one rarily, I can live with not optimal performance over adding another form.

But all that besides the point… relationname.getsize() seems to have an issue…

Paul

13

what for issue?

If you loop over foundsets then you have to check the size in the loop itself.
Because the size doesn’t have to be final.

14

ok, so how to make this work then?

What should be added to the follwoing code to make it loop thorugh the entire foundset?

Cuurent code:

for (var i = 0; i < relationname.getsize(); i++) 
{ 
    yourcodehere;
}
15

no the code is:

for (var i = 1; i <= relationname.getsize(); i++)
{
    yourcodehere;
}
16

It’s always in the little details… :?

Tnx

17

I want to copy data from a foundset into new records in a loop), but I don’t get it working properly :cry:
This is what I do;

var factuur = factuurid ;
var order = ordersid ;
var query = "SELECT werktijdid FROM werktijd WHERE orderid LIKE '" + order+ "' and recordstatus = null " ;
var maxReturedRows = 200 ;
var foundset = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), query, null, maxReturedRows);

for(var i=1; i<= foundset.getSize(); i++) 
{ 
	//ga naar het gevonden record
var record = foundset.getRecord(i);
	var code = record.tariefid ;
	var omschr = record.omschrijving ;
	var aantal = record.curengewerkt ;


	//zet het record op passief, er kan dan niet meer een factuurregel van gemaakt worden
	record.recordstatus = 2 ;

	//maak nieuwe factuurregel en zet de var data erin
	forms.DEV_factuurregels.controller.newRecord(); 
	forms.DEV_factuurregels.factuurid = factuurid ;
	forms.DEV_factuurregels.controller.codeid = code ;
	forms.DEV_factuurregels.controller.omschrijving = omschr ;
	forms.DEV_factuurregels.controller.factuuraantal = aantal ;
}
18 
var foundset = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), query, null, maxReturedRows);

Change the name of the local variable “foundset” to someother name & check.

Backer

19

Backer:
Change the name of the local variable “foundset” to someother name & check.

Thanks Backer, that did solve most of the problem; the loop includes creation of records now!
One thing that still doesn’t work is set the the contents of the foundset into the var and set a value in a record from the foundset.
It all happens in this part:

//set var from record from foundset
var code = record.tariefid ;
var omschr = record.omschrijving ; 
var aantal = record.curengewerkt ; 
record.recordstatus = 2 ; //set found record column value on passive

I’ve added some comments to clearify the method a bit … :wink:

20

what do you mean or think that happens with these lines:

//set var from record from foundset
var code = record.tariefid ;
var omschr = record.omschrijving ;
var aantal = record.curengewerkt ;

You are copying record variables to you internal javascript variables.
Not to a foundset then you need to do something like this:

//set var from record from foundset
code = record.tariefid ;
omschr = record.omschrijving ;
aantal = record.curengewerkt ;

So without the var