Search record in foundset w/o removing other records

[leonid]

Hi,
If somebody know how to retrieve actual record Index from foundset. I have table with 35000 records and I need to move cursor on the specific record w/o removing other records.User search records by Name. Here my code:
if (controller.find())
{
forms.custsearch.foundset.find();
name = "#%" + 'SERVOY' + "%";
var count = forms.custsearch.foundset.search();
var rec = foundset.getRecord(foundset.getSelectedIndex()) // the search retrieve only on record
controller.loadAllRecords(); // populate table view again
var curForm1 = controller.getName();
var fs2 =forms[curForm1].foundset; // create second foundset
fs2.getRecord(rec.customerid); // take record by primary key. Here a problem ,because some records can be deleted and I can stay on wrong page of records
var index = fs2.getRecordIndex(rec); // find actual record index in foundset
foundset.setSelectedIndex(index); // move cursor in main foundset and show in table view
}

This code works,but the problem is in last 3 lines. If i have big gap(more than 200 records) between actual rowindex and primary key(for example user delete records) the page never refresh and cursor doesnot moved. In last 3 lines of code I take record by customerid # ,which is PK. This procedure refresh my foundset and load other portion of 200 records in memory. After, I try to find actual Record index and move cursor in my Table View.
It will be nice if record which I retrieve using a search function contain actual rowindex from my foundset. I can make loop and populate new portions of foundset , but it take time. May be somebody know other solution.

[matthewbrice]

You seem to have a multitude of issues with your code.

First, you specify:

Code: Select all

if (controller.find())

which puts the relative form's foundset into find mode

but then the first line of your code you specify:

Code: Select all

forms.custsearch.foundset.find();

Is this a different form than the current form that you called controller.find() on? If so, why did you call it twice? if not, why call both?

In any event the next few lines may work depending on context.

Code: Select all

name = "#%" + 'SERVOY' + "%";
var count = forms.custsearch.foundset.search();
var rec = foundset.getRecord(foundset.getSelectedIndex()) // the search retrieve only on record

same with the next few lines of code:

Code: Select all

var rec = foundset.getRecord(foundset.getSelectedIndex()) // the search retrieve only on record
controller.loadAllRecords(); // populate table view again
var curForm1 = controller.getName();

depending on context it may or may not be what you intended.


The next line doesn't really do anything as it only gives you a reference (pointer) to the current form's foundset. (the one that is now loaded with the controller.loadallrecords() you did above). It does not create a second foundset.

Code: Select all

var fs2 =forms[curForm1].foundset; // create second foundset

The next line should mess you up, as the getRecord method takes a foundset index as a parameter, not the PK like you have. If you are just starting out, then the PK may be low enough that it will return a record where PK==index of record loaded in foundset, but unlikely to be the one you intented. Plus it returns a record object, but you don't save it in a variable anyway. So in the end, this line doesn't do anything.

Code: Select all

fs2.getRecord(rec.customerid); // take record by primary key. Here a problem ,because some records can be deleted and I can stay on wrong page of records

The next line will return the index of the record, but only if it is loaded into the foundset. Servoy foundsets lazy loads 200 records at a time. If this table has 35k records in it, highly unlikely it will be loaded.

Code: Select all

var index = fs2.getRecordIndex(rec); // find actual record index in foundset

And so the last line most likely will fail as no index was retrieved from previous line.

Code: Select all

foundset.setSelectedIndex(index); // move cursor in main foundset and show in table view

I think I understand what you are trying to do as i saw another thread on this recently, but not sure if a solution was developed that doesn't entail looping through entire foundset to get the record you want showing in table view. Expensive process. Probably should stay clear of that and just use find/search/record view for that. Someone else may have a better idea(?).

Hope this helps....

[leonid]

Hi,Matt
I just trying to move cursor to specific record in my table view . I use same form and you right the code is repeated. To move cursor to specific record I use foundset.setSelectedIndex(index). How to define actual index of record if Servoy loading only 200 records ? So,I try to use find and search functions , extract my PK ,create second foundset (may be I can use original ), find record which is more close to my real record (fs2.getRecord(rec.customerid);). Now, if next step f2.getRecordIndex(rec) will return -1 I have to go back through loop of foundsets, but I will start from foundset of founded record until I will find my actual record. It take time, but it will not load 175 foundsets in memory(175*200=35000).
May be somebody else have idea how to retrieve actual index of record.

[matthewbrice]

hmmmm. Ok. But the index of a record is meaningless if you change the foundset. It will only be relevant to the record you want if you do loop/iterate over the foundset to its relative position. If you search on the foundset or re-sort it or add/omit records, the index will change. If you search by the pk of the record (either in a duplicate foundset or original - doesn't matter) the searched foundset will have only one record in it and its index will always be 1. The pk is what will be unique and never change.

I understand what you are trying to do - you want to move the cursor down to the record you are searching for; no omitting records, just scroll down so to speak. I don't see how you can get to the record you want the way you want to present it without looping over foundset and loading records until it is reached. On average this would be quite expensive. Again, best to do find/search/show individual record or something similar.