If a user goes into find mode and finds a record that is outside of the current foundset and then presses ctrl-J for ShowAll, lots of records are added to the foundset, but it doesn’t include the currently found record.
I wrote some code that I thought would work to fix this problem on our people form. The idea is that after a record is found and the user presses the ShowAll button, I reload the foundset with approximately 200 records (99 before the current record and 99 after) and then move to what was the current record.
Here is my code:
var query = "SELECT people.id_people FROM people WHERE people.id_people < " + (pkVal + 99) + " and people.id_people > " + (pkVal - 99) + " Order By people.id_people";
forms[myMain].controller.loadRecords(query)
forms[myMain].foundset.selectRecord(pkVal);
Unfortunately, it doesn;t seem to work right. What happens is I get 200 copies of the record that is 98 records before the current record.
Any idea what I can do about this, or another way to accomplish the same thing?
It is really hard to explain how you are getting 200 duplicate records. Are they actually created in ythe database or is it a screen refresh thing? Are you also using addFoundSetParam here?
Anyway, I didn’t know that controller.loadrecords() could take a SQL query as an argument. I thought it just took a foundset object. Are these one in the same, i.e. a founset is just a jsDataset which is really just a SQL query? Nice!
Anyway, I tried your code modifying it for my case (table name maint) and added the pkVal initialization and it worked fine. Here’s the code I used with 2.2b3:
pkVal = maint_id;
var query = “SELECT maint.maint_id FROM maint WHERE maint.maint_id < " + (pkVal + 99) + " and maint_id > " + (pkVal - 99) + " Order By maint.maint_id”;
controller.loadRecords(query)
foundset.selectRecord(pkVal);
You might want to try with fewer than 99 records on either side in case the 200 record limit is causing the issue, but I doubt that will help. Seems you have a really odd situation here with it duping records like that..
I have been playing around with the problem some and I found something new:
My people form has tab panels on it. If I do a find and search on a field in the people form my ShowAll method works fine, but if I search on a field in the form on the tab panel – even going to the exact same record as I found through the search on the people form field – thats when I have the problem.
So it seems to be related to the tab panels – but I’m not at all sure why…
we have a similar problem. After doing a find, if the user chooses to Show All, the selected record is lost. Yes, the form has a tabpanel. And the Show All script includes foundset.selectRecord() that should do the job?
Currently running 2.2b2.
Edit:
Ah, I found out that selectRecord() works only within the cached 200 record set. So it couldn’t find the selected record, and we need to loop through all the records. This is our working Show All script:
var i = lid;
controller.loadAllRecords();
foundset.selectRecord(i);
while (lid != i)
{
currentcontroller.recordIndex = currentcontroller.getMaxRecordIndex();
foundset.selectRecord(i);
}
But your problem seems to be related to using the SQL query?
My people form has tab panels on it. If I do a find and search on a field in the people form my ShowAll method works fine, but if I search on a field in the form on the tab panel – even going to the exact same record as I found through the search on the people form field – thats when I have the problem.
I assume the copies of the record are not really duplicates, but that you are getting a list of records that is really just a repeat of the same identical record (same pk). Is the tab panel based on a relationship? Do these forms have the seperate found set parameter checked? Does it work for you with less than 200 records?
Can you replace your global script “ShowAll” with:
var start = 0;
var end = 0;
var vPK = forms.people.id_people
var start = vPK-100
if(start<0) //add up the difference to the endpoint
{
end = vPK + 100 + (Math.abs(start) )
start=0
}
else
{
end = vPK + 100
}
var vQuery = "SELECT id_people from people where id_people BETWEEN "+start+" AND "+end
var dataset = databaseManager.getDataSetByQuery(forms.people.controller.getServerName(), vQuery, null,100000)
forms.people.controller.loadRecords(dataset)
forms.people.foundset.selectRecord(vPK)
NOTES:
-this script should work fine but you need to “globalise” it again
by removing hard coded string “people”
using pk’s to determine ranges will not work accurately anymore once you start deleting people. However, some databases support rowcounters on which you can query.