When I do this:
var array = databaseManager.getFoundSetDataProviderAsArray(forms.find_layout.foundset,'pk_contact');
var dataSet = databaseManager.convertToDataSet(array)
forms.contact_layout.controller.loadRecords(dataSet)
there seems to be a limit of 1000 records returned. Can this be bypassed?
Thanks,
Jim
Load records is limited to 200 - the same number that the “IN” query is limited to in SQL.
How, then, to transfer a foundset of >1000 from one place to another? (There doesn’t seem to be a limit of 200, but 1000, in my testing, Bob; e.g. where 4700 records are in the original foundset, when moved via this otherwise useful method 1000- not 200- will be returned in the new foundset.)
Thanks,
Jim
Hi Jim,
I think Bob is confused with the controller only loading 200 records at a time when scrolling.
Anyway if you want to move foundsets from one form to another (that use the same table) you simply use the following:
forms.contact_layout.controller.loadRecords(forms.find_layout.foundset);
That’s all
Love to see your code, Jim.
When you do a query with controller.loadRecords() - then it IS 200. I promise you.
To be precise: if you use controller.loadRecords(dataset), there is a limit (I thought it was 1000?!). But when you use controller.loadRecords(query), there is no limit.
Love to see your code, Jim.
When you do a query with controller.loadRecords() - then it IS 200. I promise you.
This will reliably do it, Bob:
var array = databaseManager.getFoundSetDataProviderAsArray(foundset,'pk_contact');
var dataSet = databaseManager.convertToDataSet(array)
controller.loadRecords(dataSet)
Just substitute your primary key for pk_contact.
Jim
In one case, I have a contact find screen with useSeparateFoundset checked. In such a case, Robert’s solution looks like it will do the job. In other cases, however, I want to gather all attendees at an event, or receipts of some type, (where n may be > 1000). In these cases, I am loading the array with fk_contact values, and then loading that dataset into a form based on pk_contact. It works great! Except that 1000 record limit.
If I understand you correctly, Patrick, I may be able to get the desired result by loading records from a query, not the dataset. Sorry, I realize that for sql gurus this may seem obvious, but how then can I do a query to replicate a current foundset? The foundset may have been arrived at by any number of means (adding or omitting records one by one, whatever…).
For example, there are 1350 participants in an event. The user goes in and omits 50, one at a time. She then wants to transfer that group of 1300 (by fk_contact) into the main contact screen, in order to do any number of things (e.g. send an email to them). How would she “query” the foundset (of its fk_contacts), to then load the records in the contact screen (by pk_contact)?
Thanks,
Jim
jim:
…but how then can I do a query to replicate a current foundset? The foundset may have been arrived at by any number of means (adding or omitting records one by one, whatever…).For example, there are 1350 participants in an event. The user goes in and omits 50, one at a time. She then wants to transfer that group of 1300 (by fk_contact) into the main contact screen, in order to do any number of things (e.g. send an email to them). How would she “query” the foundset (of its fk_contacts), to then load the records in the contact screen (by pk_contact)?
Thanks,
Jim
I would use another forms.main_contact.controller.loadRecords(forms.other_form.foundset) but if you want you can obtain the SQL used to generate a foundset with this:
databaseManager.getSQLParameters(foundset)
Or, what you do is this: create a table called user_selection or whatever you like with columns for table_name, user_id, pk_data. Place a checkbox in your form that points via a relation to that table and let the user “mark” the records instead of omitting them. You can do this for any table. I understand that the concept of omitting records is user friendly, but it is not exactly database friendly. Somehow you need to always remember those keys and with every query or sort you need to say something like “give me all records with xy but not the one with these pks: …”. If you simply store these records in a table as I suggested you will even be able to “remember” or save these selections.
In other cases, however, I want to gather all attendees at an event, or receipts of some type, (where n may be > 1000). In these cases, I am loading the array with fk_contact values, and then loading that dataset into a form based on pk_contact. It works great! Except that 1000 record limit.
Jim: if I understand this right, then that really should be done with SQL. That is 200 times faster. Is the question: give me all “contacts” that attended and event? If yes, then you will have the ID of the contact stored in some attendance table. The attendance table is related to events. If that is the case, you only need to know an “array” of event ids to find all attendees (contacts) of those events by using simple joins in a query.
And for the other problem of passing foundset, I would follow Robert’s advice of using forms.b.controller.loadRecords(forms.a.foundset).
Thanks to all for the many fine suggestions! I have a lot to work out now.
Out of curiosity: is controller.loadRecords(dataset) limited to 1000 records by Servoy? By SQL standards? For a good reason, or just… because?
While proceeding via SQL may be, as Patrick suggests, 200 times faster, I don’t think that would be an issue with the number of records most of our users are likely to be moving. The example I gave of 4700 records (just loading 1000) is almost instantaneous (and typical for our users). 4.7*really_fast still = fast. If we were talking a million records, it might be an issue.
Imagine how cool: if getFoundSetDataProviderAsArray had a “unique value” argument, and loadRecords were without limit (perhaps via argument also), it would be real easy to move foundsets around, even moving parents from multiple-instance child records. Maybe people who need to move foundset of a million records would choose an alternate route, but for n<25,000 say it would be useful, I would think. Three easy lines of code, minimal mental gymnastics:
var array = databaseManager.getFoundSetDataProviderAsArray(forms.origination_layout.foundset,'match_field', unique);
var dataSet = databaseManager.convertToDataSet(array)
forms.destination_layout.controller.loadRecords(dataSet, -1)
where match_field is either primary key (same table) or foreign key (different table).
But, if it ain’t gonna happen, then I’ll just have to (get to) learn another way!
Thanks again, helpful friends…
Jim
Hello Jim,
what happens with the datasets is this: when you call getDataSetByQuery(), your query is sent to the database and returned as a dataset. When you then do a controller.loadRecords(dataset), Servoy takes the primary keys from the dataset and does a
SELECT pk FROM table WHERE pk IN (pk1, pk2, pk3, ..., pkn)
which is the same as asking
SELECT pk FROM table WHERE pk = pk1 OR pk = pk2 OR pk = pk3 ...
So when you do this, there is two downsides:
The 2. reason is the real problem. While that might work with some databases, it does not work with all databases. To avoid any problems this has been limited to 1000. But then, again, this kind of query is very bad in the first place.
I hope this clears things.
Thanks, Patrick, that is indeed clarifying!
Jim