Help with 200 record limit

Classic Servoy
1

Hi all

I know that this subject has been discussed before, but I still can’t get past the limit.

I have a calendar that shows reservations. One field of the reservation has a method attached that in theory should take me to a Edit Record Form, displaying the selected record. This works wonderfully if the record is within the first 200 Records. If it is not contained in the displayed 200 records, then the first record in the 200 group is displayed. This results is consistent whether I use a “Find & Search” function or a “Get dataset by Query” function.

I Know that I should be able to get to any record no matter where it resides, I just can’t seem to get it to work.

Any help would be appreciated.

Tom

2

HI Tom,

you could do: forms.yourform.controller.showRecords(foundset)

that will bring your total foundset AND selectedIndex over to the other form.

3

Thanks Harjo

Sorry that it has taken so long to reply. You wrote,

HI Tom,

you could do: forms.yourform.controller.showRecords(foundset)

that will bring your total foundset AND selectedIndex over to the other form.

Following is the code that I used.

if (resNum)
{
	var maxReturnedRows = -1;
	var query = "select reservation_id from reservations where reservation_number = '" + resNum + "'";
	var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
	forms.Edit_Reservation.controller.showRecords(dateset)
}

There are 351 records in the table “reservations” If the “reservation_number” that i am looking for is within the first two-hundred records everything works as desired. If the “reservation_number” that i am looking for is not within the first two-hundred records it reverts to the first record of the two-hundred records. This behavior is consistent.

Any more insights will greatly appreciated.
Tom

4

Hi TOm,

if you do this: forms.Edit_Reservation.controller.showRecords(dateset)

the foundset sharing between forms will be broken!

Why don’t you use a simple controller.find() and controller.search()
that way the foundset is shared between all forms!

5

Harjo

Here is the modified Code

if (resNum)
{
	forms.Edit_Reservation.controller.find()
	forms.Edit_Reservation.reservation_number = resNum
	forms.Edit_Reservation.controller.search()

	forms.Edit_Reservation.controller.showRecords()
}

I still get the same results. If the record is within the first 200 records everything works fine. I the record is not within the first 200 records it reverts to the first record of the first 200 records.

Tom

6

After the search, try inserting:

for(var i=1; i<=foundset.getSize(); i++) 
{ 
    var record = foundset.getRecord(i); 
}

to pull down your entire found set.

Dean Westover
Choices Software, inc.

7

Alternatively you can try this which requires a little less looping

var nFSCount = databaseManager.getFoundSetCount(foundset)
while (foundset.getSize() < nFSCount)
{
	foundset.setSelectedIndex(forms[sForm].foundset.getSize());
}
foundset.setSelectedIndex(1);
8

Hi All

I am sorry that it takes me so long to respond. No excuse.

Thanks westy & garfield for you suggestions but they did not help and I think it is my fault.

More Details might help. I have a calendar showing reservations for multiple units. This calendar is based on the table “units” and uses calculation to connect reservations to units and displays this data both graphically and text. This calendar is a list view. Selecting the field which contains the “reservation_number” triggers the method that is our focus. This method then finds the reservation record with that specific “reservation_number” and shows the “Edit_Reservation” form, displaying the correct reservation record. This works great as long as the found record is within the first 200 records. If it isn’t, the “Edit_Reservation” form displays the first record of the first 200 record.

I am sorry but there just seems to be no way around this 200 record limit.

Tom

9

Hi all

What seems to be an unsolvable problem is really never unsolvable.

I found an erroneous “controler.loadAllRecords” cmd hidden in a method that was causing it to appear that the find command was not working. Removed the code and it all works fine. :D

Thanks for all your assistance. It is greatly appreciated.

Tom

10

jgarfield:
Alternatively you can try this which requires a little less looping

var nFSCount = databaseManager.getFoundSetCount(foundset)

while (foundset.getSize() < nFSCount)
{
foundset.setSelectedIndex(forms[sForm].foundset.getSize());
}
foundset.setSelectedIndex(1);

If you want even less looping, you can do this:

var nFSCount = databaseManager.getFoundSetCount(foundset);
foundset.getRecord(nFSCount);

Having said that, this is still a relatively expensive operation. If you users need to go to the last record often, you probably need some better ways to search or filter your records.

11

Your case is a common scenario in Servoy. Your edit form does not share the same foundset as the source form – because it is related to whatever calendar item you’re on. The main issue being that if the destination foundset doesn’t contain the source foundset record, you will not arrive on the source record on the destination form.

There are three recommended solutions:

Solution #1: Search for the destination record
In the target form, search for the PK that you are looking for. A simple brute force approach that works every time. One potential downside is that you leave the user with no “context” – no records to browse that might be related to the destination record.

Solution #2: Go through the destination foundset until you hit the record
If you want to keep the destination form’s foundset and arrive on the correct record, you need to loop through the foundset until you hit the record you want and then stop. The loop forces Servoy to load the next 200 records when it reaches the end of the currently loaded records in the foundset.

The upside to this approach is that you retain the destination form’s foundset. The downside is that if your record happens to be at the 30,000th record slot – you’ve just brought your solution to its knees for a bit of time. For this reason, this approach is not recommended except in very specific situations.

This same warning applies to loading ALL of the destination forms records as outlined earlier in this thread! Not recommended unless you know what the implications are.

Solution #3: Load the source foundset into the destination foundset
(Harjo alluded to this solution near the top.) A typical example of going to a related record is when you are viewing a list of child records on a parent record form and you want to go to the detail form for the child record.

The trick is to assign the destination form the same foundset as the source form:

forms.destination_form_name.controller.loadRecords(source_form_name.foundset)

The advantage to this technique is that you get a set of records at your destination which provides context for the record you navigated to. However, this context is supplied by the source form, not the destination form. This can be a disadvantage depending on what you are trying to achieve. A bonus to this technique is that you don’t have to do anything to navigate to the correct index. Forms that share the same foundset also share the same record index. If you started from index 3,054 you will arrive at index 3,054.

Summary
Forms based on the same table typically share the same foundset and record index. In situations where they don’t share the same foundset, going to the same record in the destination form can be accomplished several different ways:

  1. find the record directly
  2. loop through the destination form’s foundset until you hit the record you want by PK
  3. assign the source form foundset to the destination form

All three techniques have their advantages and disadvantages. The first technique is fool proof but provides no context (great for edit record situations!). The last technique provides context but not necessarily the context the user is expecting. With the second technique you get the expected context but performance is a significant disadvantage.

We usually default to the last technique but it is not always a clear cut choice.

We’ve been running into this confusion with new clients and developers for so many years we wrote this all up in a tip file a long time ago!