How to prevent a record from disappearing

[chris]

Wondering if someone could help me with the following:

If a user searches for all customers without an email address (where email address is blank) and gets a list of 10 customers back - and then starts adding email addresses to these customers - then as each address is added, it disappears!

My users are not understanding why this is happening. I understand it's because those records no longer fit the where criteria, but we've got to find a way to prevent them from disappearing as it's totally confusing for end users.

any thoughts?

Thanks.

[mattman]

Wondering if someone could help me with the following:

If a user searches for all customers without an email address (where email address is blank) and gets a list of 10 customers back - and then starts adding email addresses to these customers - then as each address is added, it disappears!

My users are not understanding why this is happening. I understand it's because those records no longer fit the where criteria, but we've got to find a way to prevent them from disappearing as it's totally confusing for end users.

any thoughts?

Thanks.

Chris, you'll need to adjust your criteria. The sequence will go something like this.

After your search, create an array of the primary keys for the found records.

Trigger a loadRecords() and supply the array of the former found set.

It will happen so fast that users will assume it was part of the search but the records won't disappear. Because I'm not in Servoy right now, and don't have the time I don't know how exactly loadRecords() wants you to supply the array but I'm sure you can figure it out or one of the Servoy devs can post the easy way to do this.

[chris]

Thanks Matt, seems like a clever idea, and it makes sense.

However, are we going to run into this limit problem again where the found set can only contain 200 or less records? So if the user looks for all people with no email addresses, finds 3000 records, and then we implement your solution, will Servoy/SQL be able to load 3000 records?

Thanks.

[mattman]

Thanks Matt, seems like a clever idea, and it makes sense.

However, are we going to run into this limit problem again where the found set can only contain 200 or less records? So if the user looks for all people with no email addresses, finds 3000 records, and then we implement your solution, will Servoy/SQL be able to load 3000 records?

Thanks.

Probably not. You'll have to work on them 200 a whack. Although you can create a lable that will show that they are only editing 200 of X. Most of the time I wouldn't think a user would edit 3,000 records at one sitting.

It seems like the issue is more of providing good user feedback instead of not being able to technically solve the problem. But you may have other valid reasons for wanting all records loaded. You can load more records than the 200 but I think you have to issue the query directly and it depends on how long the SQL statement would be.

[chris]

I guess in this case I could load the first 200 pks from the array and then load the next 200, etc.

It's just more coding, and I'm spoiled by FM not requiring this.

Thank you

[jcompagner]

servoy loads 3000 records just fine.

Only in chunksizes of 200...

so if do a search you get the first 200 records
after that if you touch a record around the 200 it will load the next 200
so then 400 records are in the foundset..

if you absolutly want the total size of the foundset (you have to know what youre doing!!)
then you can do something like this:

while(recordIndex < maxRecordIndex)
{
recordIndex = maxRecordIndex
}

then you jump in steps of 200 to the latest one.

[chris]

Thanks Johan, but the issue is loading 3000 records from an array of primary keys, not from a direct query (which I know works fine).

As you see above, the issue is that when you load records from a query and then change a value in a field that was used in the query, that record could disappear from the found set. To prevent this (because it confuses users!) we need to build an array of primary keys and then load these primary keys.

Any tips? I'm imagining I'll have to use the loadRecords method but this only loads 200 records (because of the IN statement limitation, right?).

[jcompagner]

if i search for something in a main form.
and after i find some records i change that field, save the record.. That record is not disappearing..?!

The only time we do throw them out of the foundset if it doesn't fit anymore in the relation (so a related foundset)

[jcompagner]

The only way to really work with such large sets of marking records is to store the pk in a different table.. And let a relation do the work for you.
Then everything works.. (aggregates, sorting,printing)

FM does under the hood exactly the same thing.. (i guess..)

[chris]

Thanks Johan, so I guess I should use a find rather than a 'go to related' if I don't want records to drop out of the found set.

You're right, this is part of my effort to mark records. I still don't know how to view a found set of marked customers. Had assumed I'd use convertFoundSet but if a customer is marked more than once it will show that customer twice, as we've discussed in other posts, and I don't think you can sort or print after using convertFoundSet in this way. Do you have any ideas?

FYI - it's easy for a customer to be marked more than once because if a user clicks "mark all" on a found set of 150 records, and then finds another set of 15 records and clicks "mark all", the same customer may have been in both sets. The "mark all" method can't check each record, that would make it pretty slow.

Thanks.

[jcompagner]

how convertFoundSet works now (so duplicate entries) you can sort,print or what ever you want with it.

If we do a distinct then you can't sort and print anymore.. Because distinct on one column and sorting (or group by) on another column can't be done.

on the planning is that convertFoundSet can (optionally) create a sub select. This will not work on all databases (current version of mysql) but most are supported.

If you keep marking of records below 200 entries then there is no issue that the id is selected more then once (it won't be returned more then once)

Marking more then 200 entries then you have to insert those in a tmp table youreself. And if you want to select them again you can do that with youre own query:

controller.showRecord("select pk from mytable where pk in (select markedpk from markedrecords)");

then it also doesn't matter if the id is 2,3 or 4 times in the list.

[chris]

Marking more then 200 entries then you have to insert those in a tmp table youreself. And if you want to select them again you can do that with youre own query:

controller.showRecord("select pk from mytable where pk in (select markedpk from markedrecords)");

then it also doesn't matter if the id is 2,3 or 4 times in the list.

I thought the IN clause couldn't handle more than 200 records? So, would this work if there are 900 marked records? or 2000 marked records?

[jcompagner]

in in with a sub select can handle everything..

but the in we generate if you give as a JSDataSet with pk's is something like this:

select pk from yourtable where pk in (1,2,3,4,5,6,7,8,9,x,x,x,x,x)

and that kind of in query is limitted on most databases becaues of the size a sql statement can have! (the string size)

[chris]

in in with a sub select can handle everything..

Thanks, this is sounding close to a solution. So, if I use Firebird or Sybase, can I simply do a

controller.showRecord("select pk from mytable where pk in (select markedpk from markedrecords)")

where the markedrecords table has 3000 records, and it will work?

I didn't understand what you mean by "but the in we generate if you give as a JSDataSet ..."

[jcompagner]

controller.showRecord("select pk from mytable where pk in (select markedpk from markedrecords)")
where the markedrecords table has 3000 records, and it will work?

yes

I didn't understand what you mean by "but the in we generate if you give as a JSDataSet ..."

the way we discussed before..

generation a jsdataset (by databasemanager.getDataSetByQuery() or databasemanager.convertToDataSet() that returns a jsdataset) and setting that dataset in a form: controller.showRecords(dataset)

in the dataset we then have a PK array and with that PK array we are generating that in query