Would it be good to have a checkbox to allow the ordering table commands to apply to all records rather than just the fetched records? Currently to see the record in a big table with the last date, we have to write a query. Correct? Something like:

SELECT * FROM [Sample visits]

where [DateTime] = (select max([DateTime]) FROM [Sample visits]);

Also, perhaps the checkbox to apply commands to all records or just displayed records should be very prominent, such as top of the list of commands. Very important to know what data set is being considered: all data, fetched records, or 50,000 limit.

Mike, that doesn't seem to make sense.

The engine does not know the values of records that it hasn't fetched.

It can't order them, or do anything else with them (except count, sample and fetch).

So that checkbox could only mean "fetch everything before processing". Maybe that's what you mean. If so, it does make sense. Would it be good? Not sure. Maybe, in confident hands--and with a Cancel button.

Yes the later is what I mean. FWIW, doing a Ctrl-A on a big table and then running this query shows that all records are selected in the table.

SELECT count(*) as [Total Selected Records] FROM CALL Selection([Sample visits], TRUE);

I agree with your last paragraph wholeheartedly ["Very important to know what data set is being considered..."]. This is space shuttles falling out of the sky stuff (an O-ring issue perhaps).

I don't know that Manifold have ever quite understood this. Their excuse in the past seems to have been "we do better than the standard database companies". That is not good enough. First because "better" may be worse (returning 50,000 records rather than, say 10,000 may more readily give a false impression of completeness or comprehension). Secondly because the standard database companies may simply be wrong, dangerous to life and profit. Manifold should do better. It knows how.

Agreed it should be better. A couple thoughts, maybe add a color to the bottom quarter of the table's right hand scroll bar when the table is at the 50,000 limit. This would be visible always compared to the current indicator which is only seen when scrolled to bottom of displayed records. Then put a "Filter" and/or an "Order" in the scroll bar when there is a filter or order applied. Put them at the top when they apply to just the displayed records and at the bottom when they apply to the entire table. See example.

One problem with this is that as the scroll bar pointer gets to the bottom, things get gagged up. There is probably a much better way.

Attachments:
Capture.JPG

Mike I think you’re onto something helpful.

First, the principle: perhaps the only thing that needs to change is to make it patently obvious that (when) only a limited portion of the dataset is shown. Compared to that, the record limit and other aspects are just details.

So, how to make it manifest? Again I think your suggestions are on the right track. I would suggest this variation:

How about if (not the scroll bar but) the actual records changed their colour at the bottom of a truncated table (say, the lower 15 records) and also faded out (so that the last fetched record was almost transparent)? That would be absolutely obvious, no one could doubt what it means.

That does not directly address the problem of a partial result such as MAX or MIN. But the answer could be similar. Any result calculated only on a subset of the source table could be grey (or blue for preview, conspicuous and familiar), and perhaps in italic too.

I think that would really help.

The impression matters critically, and needs to be absolutely obvious, even to a casual or uninformed user.

(Plus maybe your button, if so then definitely with Cancel.)

Your suggestion has an important advantage over mine: that the “provisional” display status could be seen everywhere, even at the top of the table.

So how about this: if results are limited/truncated, then all shown records are in blue (the preview colour), plus the last 15 records or so fade to (almost) transparent at the bottom.

Good ideas Tim. Another thought would be to use a pale yellow as the warning color for exceeding the 50,000 limit. Perhaps it could just go in either or both of the boxes in the extreme top left and bottom right corner. Maybe just the word "Sample" in yellow to the right of the Table's name. Also, could paint the entire vertical or horizontal scroll bar yellow, but that might be too much.

Still I think there should be something that gives confidence as to whether a filter, order, or selection is of the whole table or just the sample. Maybe there is one control that determines that for filter, order, and selection commands rather than allowing a user to set that differently for each one. Need to think through the logic on that one.

If just one control for filter/order/selection, perhaps put a checkbox within in the yellow box mentioned above to indicate those commands apply to just the sample when checked and to the whole table when not checked. Hover over the box and it tells you what the checkbox does.

Great ideas!

Here's a little bit more from me:

1. We definitely have to do a better job showing when ordering / filtering is done only on part of the table vs the entire table. (It might not really be necessary to show anything extra when there is no ordering / filtering applied, because you are just browsing through the table, and yes, the list of records ends before the table ends, but you will see that when you scroll down to the end. Maybe the final record could be made more prominent, but that's about it. The real problem is with ordering / filtering where you can do it and get a result without noticing that it is partial. This is bad, agree completely.)

How specifically to indicate that ordering / filtering is done only on part of the table is secondary - there are multiple options, we'll choose something that would work for similar cases in other windows. But it has to be noticeable. We will try to do it.

2. The real issue, however, is what happens when, OK, you know that ordering / filtering is done only on part of the table, but you still want to do it on the entire table.

There is an obvious unsolvable case when the table does not have an index on the involved fields and has to be retrieved in full *and* when it is also so big that retrieving it in full will take hours (or days, or years - like with the table backing Bing imagery for the entire planet). But that unsolvable case aside, there are also cases where the involved fields do have indexes (not even required to be unique) and in those cases we can use those indexes to perform ordering / filtering without retrieving the table in full. And there are also cases where the involved fields do not have indexes, but the table is not exorbitantly large and can be retrieved in full in some significant but still acceptable time - say, in minutes instead of in hours.

We can do this: (a) if the table has an index on the involved fields, use that for ordering / filtering without asking anything, (b) if the table does not have an index on the involved fields, offer to fetch it in full, allow canceling the process. And, for good measure, (c) allow clicking the final record of the partially fetched table to fetch another 50,000 records, also with an ability to cancel the process.

Glad to see your on top of this Adam!

but you will see that when you scroll down to the end.

One last nudge for the uneducated user :-) fPlease look for an acceptable way to add an indicator that the table being viewed is a sample without having to scroll to the bottom.

We can do this: (a) if the table has an index on the involved fields, use that for ordering / filtering without asking anything, (b) if the table does not have an index on the involved fields, offer to fetch it in full, allow canceling the process. And, for good measure, (c) allow clicking the final record of the partially fetched table to fetch another 50,000 records, also with an ability to cancel the process.

This sounds ideal to me.

On reflecttion I didn't like any of my UI ideas much. In particular, I think showing all records in blue (if the table has been only partially fetched) would be ugly, and distracting.

A variation that appealed for a moment: if partially fetched, always show the last (say) 5 records shown in blue. Somewhat less ugly perhaps, and perhaps more obvious and more informative.

How about a smaller change. Instead of showing the "more records" button only when the user has scrolled to the last record, always show that button under the last record shown on the current screen, whenever a table has been only partially fetched. Similarly, always allow clicking on this button to fetch another batch of records, regardless of where we are currently scrolled to in the current fetched set. That is for an ordinary click; the same button could have a second function if clicked with a modifier key (e.g. Shift): this could mean "fetch all records, stopping if I Cancel". (Too discreet?)

I think the "more" button or its record could also be made more prominent. It's hard to see how it could be made wider, while still fitting with the general design. (It's not wide enough to have the word "More" stamped on it, for example.) Maybe the first field in the empty record beside the button could contain an ellipsis rather than being blank; perhaps all the fields in the empty record could be shaded in darker grey.

(In other words, I agree with the point that Mike stresses above.)

Attachments:
more.png

Currently to see the record in a big table with the last date, we have to write a query. Correct? Something like:

SELECT * FROM [Sample visits]

where [DateTime] = (select max([DateTime]) FROM [Sample visits]);

..or:

--SQL9

SELECT * FROM [Sample visits] ORDER BY [DateTime] DESC;

The resulting table will not be editable, however.

I'll reply to the central point of the thread above.