Find only returns 60 records

Classic Servoy
1

This is baffling to me - hopefully someone can shed some light on it…

I am trying to do a find on a form that is setup to print mailing labels (Avery standard - 30/page - everything is setup properly and prints fine). The ‘addresses’ table used by this form is also used by another form in Record view.

When I do a find on either form using this table (since they share a foundset) and “simple” search criteria (e.g. address_id < 200 or state = ‘FL’), I get an appropriate number of records - sometimes 200 records (to start with, but really more than that) if the criteria is broad enough. As far as I am concerned, this appears to be functioning properly.

However, when I do a find with more complicated criteria, things get weird. Actually, it is just one set of criteria that is vital to the system: a bunch of specific address_id’s (primary keys of the ‘addresses’ table). For instance, if I have a list of 140 addresses to print, I put the form into Find mode, then create 140 records on the form with each of the primary keys that I want to print, then search. Here’s the problem: the resulting foundset from the search is always limited to 60 records when I do it this way!

I don’t know if this is related to the fact that 60 records is two pages of labels, or if that’s just a coincidence. And it happens whether I search on the labels form, or on the other form that uses the same table but is not setup to print labels. I should also mention that the 60 records that are selected are always the LAST 60 primary keys of the search.

I have checked my loops and everything else that goes into executing this search. All is correct. I even wrote a simple loop to select primary keys 1-100 in this fashion (find, create 100 records, then search), and that also brought up only 60 records (primary keys 41-100). So, I am convinced it is something outside of my code that is causing this to happen, but I am clueless as to what else to check. If there was some sort of limit you could set on the number of records returned (and I don’t think there is in Servoy), then it wouldn’t return more than 60 records when using “simple” search criteria.

Also, before you ask: the primary keys are in a seprarate table on the N side of the relation. I can’t use a relation to select the records I need. This is the only way I’ve come up with, and it works well on other tables in other methods in the same solution (no odd record limits like I’m seeing here). If you have a better way to do this, though, I’m all ears.

Thanks, as always, for any help or insight you can provide :slight_smile:

2

You say ids are in a different table.

But are they in the same database? In that case you should be able to create a query for that yourself (but a relation as well). There is nothing that Servoy does behind the scenes that you can’t except the spanning of relations over more than one database…

To be honest, from the little specific information that you disclose, I don’t think it has to be complicated.

I dont’ know but I can imagine that the limit lies in the fact that you are creating a multitude of new records within a find. I can imagine that Servoy never thought of somebody looking for doing it this way and capped it at 60 new records. No real knowledge here though, just guessing.

3

Yes, everything is in the same database. The tables are related, but I am trying to get records from the N side of the relation. One address record can appear multiple times in the table I am drawing the primary keys from.

Like I said, doing a find this way works in other tables without a limit. Are you saying I should use an SQL query to select the records using their primary keys? That’s certainly something I will try.

It’s possible I am just going about this the wrong way in general, and I’d appreciate knowing how everyone else does this (select a bunch of records in a table when all you have is a list of their primary keys). Maybe I’m being dense about the whole problem.

4

Like Marcel I also think that it could be due to a limit in the OR statement that is generated from your 60 requests (what is your database?). What I’d suggest is that you turn on the stacktrace (in your servoy_developer.bat change “javaw” into "java -DSTACKTRACE=true " and start Servoy using the bat). Then you can see the SQL statements that are fired and it will be easy to figure out what goes wrong.

5

Servoy does not have any limit which is 60…but to see which SQL is generated clear the perfomance stats and do the find, all the SQL in there after a refresh.
(Or create an support case and submit a sample solution demonstrating this)

6

Fixed! Here is some info:

databaseManager.getSQL(foundset) returned the following when I tried to do things “my” way (as described above):

select Addresses.Addr_ID from Addresses where (Addresses.Addr_ID = ? or [... repeat 58 times ...] or Addresses.Addr_ID = ?) order by Addresses.Addr_ID

It actually had the question marks there instead of the actual IDs; those are not my addition or an attempt to obscure data. But it always selected the last 60 records, which would have required valid IDs, so I don’t know why they showed up as question marks… Anyway, there were definitely 60 repetitions in the WHERE clause. It was obvious this was a bad way to do things.

I came up with this in about 5 minutes. I suspect it is the more “proper” way to do things:

var qTxt = "select Addr_ID from Addresses where Addr_ID in (";

//generate an SQL query to select the primary keys
for (i = 1; i <= controller.getMaxRecordIndex(); i++)
{
	controller.setSelectedIndex(i);
	
	if (attached_addr_id)	//if address is attached to this record...
	{
		//add a new entry in the list
		if (i > 1)
			qTxt += ", ";
			
		//copy address ID
		qTxt += attached_addr_id;
	}
}

//close the query
qTxt += ") order by Addr_ID";


//generate a dataset containing these address IDs
//using the SQL query generated above
var qDataset = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), qTxt, null, -1);

//load records based on the dataset (primary keys)
forms.p_Address.controller.loadRecords(qDataset);

Of course, this fixed the problem right away. It selected all the primary keys given to it - well beyond the last 60.

In doing this, I also (re)discovered databaseManager.convertFoundSet(), which is also a “better” way to do things than I am doing it in other places now. It won’t work for this application, but I can improve some other parts of my code with that method.

7

Can you show us the original find? Servoy will not search this way unless you create many find requests…or use massive ammounts of omit records

8

I use a global field in a relation with a tabel with orderlines. Each order contains about 120 records.

I made a method with loadrecords(“relation global file_to_orderlines”) to got a foundset for a form. At first when I run this method I only get 40 records. The second time when I run the same method I get all the records of te relation. This happens with every time with different sets of records.

9

its hard for us to get a clear picture about the scenario, please submit a small sample solution in our support system demonstrating this issue:
http://crm.servoy.com/servoy-webclient/ … oy_support

10

Jan, in your December 11th post, when you say “Servoy won’t search this way,” are you referring to the code I posted, or the description of the way I was doing it previously? In other words, is my most recent solution a proper way to do things?

If you really want an example of the “old” way, I can dig one up…

11

Servoy normally does not generate SQL with a lists of pks…so we don’t understand how you can get this SQL, that’s the reason we asked for a small sample solution (via the support system)