Fetching more records for a grid

Hello and thanks in advance for the help...

I have a grid control which is based on a small table that is extracted from
a larger table. When the user scrolls to the last row, I would like to be
able to requery and show the next (or previous if the user scroll up from
the first record) batch of records.

How do you detect all of the various method (Keyboard, Scroll Bars, Etc.)
that the user might be using to scroll up from the top or down from the
bottom.

I could put menu items on the menu to requery but it would be more natural
if they could just work in the grid.

Thanks,
Jeff

Dan
Wed Sep 26 08:32:44 PDT 2007

Wouldn't be natural at all. Highly unnatural, in fact.

This will be a lot of work and probably be confusing to users. They're used
to the last record being at the bottom, not "the last until you ask for
more".

I'm sitting here trying to think of *any* other application that works as
you've described and I can't come up with anything. I'd rethink this.

Dan

Jeff Grippe wrote:
> Hello and thanks in advance for the help...
>
> I have a grid control which is based on a small table that is
> extracted from a larger table. When the user scrolls to the last row,
> I would like to be able to requery and show the next (or previous if
> the user scroll up from the first record) batch of records.
>
> How do you detect all of the various method (Keyboard, Scroll Bars,
> Etc.) that the user might be using to scroll up from the top or down
> from the bottom.
>
> I could put menu items on the menu to requery but it would be more
> natural if they could just work in the grid.
>
> Thanks,
> Jeff

Beverly
Wed Sep 26 09:22:47 PDT 2007

to expand on Dan's response, fox tables are flat data files where it's
trivial for the app and os to move from one record to another no matter
the size of the table and indexes are simply record number (read "file
offset) pointers that fit into this schema.

If there is a reason you don't want the user to look at the master
table, post that and lettuce thrash that problem directly.

hth,
Beverly Howard

Jeff
Wed Sep 26 10:30:33 PDT 2007

My users are used to an application that "browses" the entire database. The
application (or a version of it) goes all the way back to FPD 2.x and DOS.

I theory, with the current version of the app, they can scroll all the way
from the first record of the table to the last record of the table. The
table has several million records so in practice, nobody ever goes through
the entire database.

If I'm going to convert this app to something that can use SQL and views, I
need some way to simulate the feel of the existing application.

That is why I need to be able to fetch the next (or previous) group of
records.

Right now I put buttons on the form to do it and that will probably work
fine but it would be nice if it were more elegant.

Thanks,
Jeff
"Dan Freeman" <spam@microsoft.com> wrote in message
news:OZU08JFAIHA.4612@TK2MSFTNGP03.phx.gbl...
> Wouldn't be natural at all. Highly unnatural, in fact.
>
> This will be a lot of work and probably be confusing to users. They're
> used to the last record being at the bottom, not "the last until you ask
> for more".
>
> I'm sitting here trying to think of *any* other application that works as
> you've described and I can't come up with anything. I'd rethink this.
>
> Dan
>
> Jeff Grippe wrote:
>> Hello and thanks in advance for the help...
>>
>> I have a grid control which is based on a small table that is
>> extracted from a larger table. When the user scrolls to the last row,
>> I would like to be able to requery and show the next (or previous if
>> the user scroll up from the first record) batch of records.
>>
>> How do you detect all of the various method (Keyboard, Scroll Bars,
>> Etc.) that the user might be using to scroll up from the top or down
>> from the bottom.
>>
>> I could put menu items on the menu to requery but it would be more
>> natural if they could just work in the grid.
>>
>> Thanks,
>> Jeff
>
>

Dan
Wed Sep 26 11:47:38 PDT 2007

OK, this is a general application design issue. I'd argue that an app that
browses through millions of customers is generally less useful than needed.
You, yourself, said nobody ever does that.

Users generally have *some* idea what they're looking for. Ask them to type
something and then show them a list of likely matches. Parameterized views
are *excellent* for this.

After all, a user that's paying bills probably has in-hand a bill from the
vendor they're going to pay. Ask them who it is! This approach plays nicely
into the C/S paradigm. *NO* approach to "show everything and let them pick"
plays nicely into it, least of all changing how the standard U/I elements
work.

Dan

Jeff Grippe wrote:
> My users are used to an application that "browses" the entire
> database. The application (or a version of it) goes all the way back
> to FPD 2.x and DOS.
> I theory, with the current version of the app, they can scroll all
> the way from the first record of the table to the last record of the
> table. The table has several million records so in practice, nobody
> ever goes through the entire database.
>
> If I'm going to convert this app to something that can use SQL and
> views, I need some way to simulate the feel of the existing
> application.
> That is why I need to be able to fetch the next (or previous) group of
> records.
>
> Right now I put buttons on the form to do it and that will probably
> work fine but it would be nice if it were more elegant.
>
> Thanks,
> Jeff
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:OZU08JFAIHA.4612@TK2MSFTNGP03.phx.gbl...
>> Wouldn't be natural at all. Highly unnatural, in fact.
>>
>> This will be a lot of work and probably be confusing to users.
>> They're used to the last record being at the bottom, not "the last
>> until you ask for more".
>>
>> I'm sitting here trying to think of *any* other application that
>> works as you've described and I can't come up with anything. I'd
>> rethink this. Dan
>>
>> Jeff Grippe wrote:
>>> Hello and thanks in advance for the help...
>>>
>>> I have a grid control which is based on a small table that is
>>> extracted from a larger table. When the user scrolls to the last
>>> row, I would like to be able to requery and show the next (or
>>> previous if the user scroll up from the first record) batch of
>>> records. How do you detect all of the various method (Keyboard, Scroll
>>> Bars,
>>> Etc.) that the user might be using to scroll up from the top or down
>>> from the bottom.
>>>
>>> I could put menu items on the menu to requery but it would be more
>>> natural if they could just work in the grid.
>>>
>>> Thanks,
>>> Jeff

Jeff
Wed Sep 26 12:07:24 PDT 2007


"Dan Freeman" <spam@microsoft.com> wrote in message
news:eM8M32GAIHA.4880@TK2MSFTNGP03.phx.gbl...

> OK, this is a general application design issue. I'd argue that an app that
> browses through millions of customers is generally less useful than
> needed. You, yourself, said nobody ever does that.
>
> Users generally have *some* idea what they're looking for. Ask them to
> type something and then show them a list of likely matches. Parameterized
> views are *excellent* for this.
>
> After all, a user that's paying bills probably has in-hand a bill from the
> vendor they're going to pay. Ask them who it is! This approach plays
> nicely into the C/S paradigm. *NO* approach to "show everything and let
> them pick" plays nicely into it, least of all changing how the standard
> U/I elements work.
>

Here's the big BUT

BUT I need to maintain some type of similar UI from the old form (which used
the giant table as the recordsource for the grid) to the new one which only
loads 100 records at a time.

Here are the problems:

1. The application has always opened to the first screen full of data in the
table. It does not begin with a search. It is after the form is displayed
that the search can be performed. This is not a problem. The only real
problem is...

2. Sometimes they actually do want to browse through several hundred or even
a few thousand records. They might search for the first one but then browse
through an entire account.

In my current version of the form, they could scroll all the way to the end
of the table as I've said before.

My new version loads 100 records at a time. If I find myself pulling records
from an SQL database instead of using native DBF files then I will want to
stick to this 100 record limit.

I would, however, like to simulate the old "browse through the entire table"
format that they are used to looking at. This means that if they reach the
100th records on the form, I'd like to automatically fetch the next 100. If
they try to scroll up from the first record on the form, I want to fetch the
previous 100 records.

Thanks again,
Jeff


> Dan
>
> Jeff Grippe wrote:
>> My users are used to an application that "browses" the entire
>> database. The application (or a version of it) goes all the way back
>> to FPD 2.x and DOS.
>> I theory, with the current version of the app, they can scroll all
>> the way from the first record of the table to the last record of the
>> table. The table has several million records so in practice, nobody
>> ever goes through the entire database.
>>
>> If I'm going to convert this app to something that can use SQL and
>> views, I need some way to simulate the feel of the existing
>> application.
>> That is why I need to be able to fetch the next (or previous) group of
>> records.
>>
>> Right now I put buttons on the form to do it and that will probably
>> work fine but it would be nice if it were more elegant.
>>
>> Thanks,
>> Jeff
>> "Dan Freeman" <spam@microsoft.com> wrote in message
>> news:OZU08JFAIHA.4612@TK2MSFTNGP03.phx.gbl...
>>> Wouldn't be natural at all. Highly unnatural, in fact.
>>>
>>> This will be a lot of work and probably be confusing to users.
>>> They're used to the last record being at the bottom, not "the last
>>> until you ask for more".
>>>
>>> I'm sitting here trying to think of *any* other application that
>>> works as you've described and I can't come up with anything. I'd
>>> rethink this. Dan
>>>
>>> Jeff Grippe wrote:
>>>> Hello and thanks in advance for the help...
>>>>
>>>> I have a grid control which is based on a small table that is
>>>> extracted from a larger table. When the user scrolls to the last
>>>> row, I would like to be able to requery and show the next (or
>>>> previous if the user scroll up from the first record) batch of
>>>> records. How do you detect all of the various method (Keyboard, Scroll
>>>> Bars,
>>>> Etc.) that the user might be using to scroll up from the top or down
>>>> from the bottom.
>>>>
>>>> I could put menu items on the menu to requery but it would be more
>>>> natural if they could just work in the grid.
>>>>
>>>> Thanks,
>>>> Jeff
>
>

Dan
Wed Sep 26 12:24:20 PDT 2007

Jeff Grippe wrote:
> Here's the big BUT
>
> BUT I need to maintain some type of similar UI from the old form
> (which used the giant table as the recordsource for the grid) to the
> new one which only loads 100 records at a time.

So you want a paradigm change without any actual change? <g>

>
> Here are the problems:
>
> 1. The application has always opened to the first screen full of data
> in the table. It does not begin with a search. It is after the form
> is displayed that the search can be performed. This is not a problem.
> The only real problem is...
>
> 2. Sometimes they actually do want to browse through several hundred
> or even a few thousand records. They might search for the first one
> but then browse through an entire account.

As I said, parameterized views are great for this. Construct the query so
the where clause is "Like ?lcParameter", where you've tacked the wildcard
character (%) onto the user-entered parameter and requery() the view. You'll
get back whatever matches and the user can browse the result set.

You need to get away from thinking "next n" because the ONLY database where
that has any meaning is Foxpro. The concept simply doesn't exist in
databases where data is defined logically rather than physically (which is
all of them except VFP).

Dan

Jeff
Wed Sep 26 12:54:46 PDT 2007


"Dan Freeman" <spam@microsoft.com> wrote in message
news:%23d4ZXLHAIHA.5328@TK2MSFTNGP05.phx.gbl...

> You need to get away from thinking "next n" because the ONLY database
> where that has any meaning is Foxpro. The concept simply doesn't exist in
> databases where data is defined logically rather than physically (which is
> all of them except VFP).
>

So does TOP <nExpr> only work on DBF / VFP databases?

I've tried to move my users away from the "old" style of database searching
but I've been met with a lot of resistance.

Since one of my most vocal critics of the "new" style is my boss, I need to
accomodate.

So, yes, I want the new paradigm with the old look and feel.

The funny thing is that on the web version of the same application, I show
10 records at a time and nobody complains. They don't have any problem with
buttons on the web site that say "Next Page" and "Previous Page" to scroll
through the transactions. Go figure!

Thanks again,
Jeff

Rush
Wed Sep 26 13:15:15 PDT 2007

This is a multi-part message in MIME format.
--------------050502020204070302010504
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jeff Grippe wrote:
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:%23d4ZXLHAIHA.5328@TK2MSFTNGP05.phx.gbl...
>
>
>> You need to get away from thinking "next n" because the ONLY database
>> where that has any meaning is Foxpro. The concept simply doesn't exist in
>> databases where data is defined logically rather than physically (which is
>> all of them except VFP).
>>
>>
>
> So does TOP <nExpr> only work on DBF / VFP databases?
>
> <snip>
As I understand it, TOP <nExpr> returns the top <n> records of the
result set, which is a [filtered][sorted] cursor - not the "top <n>"
records of the data source.

- Rush


--------------050502020204070302010504
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Jeff Grippe wrote:
<blockquote cite="mid:13fle2pps0nm37a@news.supernews.com" type="cite">
<pre wrap="">"Dan Freeman" <a class="moz-txt-link-rfc2396E" href="mailto:spam@microsoft.com">&lt;spam@microsoft.com&gt;</a> wrote in message
<a class="moz-txt-link-freetext" href="news:%23d4ZXLHAIHA.5328@TK2MSFTNGP05.phx.gbl">news:%23d4ZXLHAIHA.5328@TK2MSFTNGP05.phx.gbl</a>...

</pre>
<blockquote type="cite">
<pre wrap="">You need to get away from thinking "next n" because the ONLY database
where that has any meaning is Foxpro. The concept simply doesn't exist in
databases where data is defined logically rather than physically (which is
all of them except VFP).

</pre>
</blockquote>
<pre wrap=""><!---->
So does TOP &lt;nExpr&gt; only work on DBF / VFP databases?

&lt;snip&gt;</pre>
</blockquote>
As I understand it, TOP &lt;nExpr&gt; returns the top &lt;n&gt; records
of the result set, which is a [filtered][sorted] cursor - not the "top
&lt;n&gt;" records of the data source.<br>
<br>
&nbsp;- Rush<br>
<br>
</body>
</html>

--------------050502020204070302010504--

Dan
Wed Sep 26 13:14:08 PDT 2007

Jeff Grippe wrote:
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:%23d4ZXLHAIHA.5328@TK2MSFTNGP05.phx.gbl...
>
>> You need to get away from thinking "next n" because the ONLY database
>> where that has any meaning is Foxpro. The concept simply doesn't
>> exist in databases where data is defined logically rather than
>> physically (which is all of them except VFP).
>>
>
> So does TOP <nExpr> only work on DBF / VFP databases?

Top n works just fine. Which n? <g>

Your boss will change his tune when his network gets bogged down to the
point of not being usable.

> The funny thing is that on the web version of the same application, I
> show 10 records at a time and nobody complains. They don't have any
> problem with buttons on the web site that say "Next Page" and
> "Previous Page" to scroll through the transactions. Go figure!

Funny thing that. I've noticed that you can tell users "it's the internet"
about just about *anything* (speed, lack of flashy U/I, whatever) and
they'll shrug and say "Oh, OK then". LOL

Dan

Jan
Wed Sep 26 23:10:26 PDT 2007

Jeff,

The concept you are describing is similar to the TBROWSE function in the old
clipper days and now (apparently) carried forward to visual objects. However
in all the chatter on this thread there were several issues overlooked, What
if a record is added that logically becomes the next record when the user
arrows down, because you have an extract of the real table, the user will
not see this unless they drop out and go back in again or scroll past the
current 100 and back again.

The same issue applies to if another user deletes a record, the deleted
record will still be visable to this user until the data is refreshed. What
happens though if the user decides to action the deleted record?

Codewise to achieve what you want is not onerous, in clipper the TBROWSE
function was only about 50 lines of code and catered for drawing the boxes
(grid) and maintaining the scrollbar on the side etc. The philosophy of the
code was to read the first page full of records into an array and display
the array (say 10 records). When the record pointer was moved the array was
refreshed, thus the data was always current. When the user issues a page up
Skip back 20 recs (or whatever the nuimber of visable rows x2 and refill the
array then update the display. HOME and END were also catered for (top &
bottom).

Under VFP and Windows all this control was given over to the grid control,
hence all the problems with grid highlights and data filtering etc that the
grid was notorius for in the early days, some of which still linger on.

But back on topic, I use grids in a similar fashion, with a search box and
an option group to specify display and search sequences. What this allows me
to do is match to the data sought as the user types ie Smith, John. I use
the comma to concatenate fields, so Smi,joh might find John Smith where Smi
finds Smith, the comma says pad out the key with the contents of the current
record (ie Smith) then look for the contents of the next key ie John.

This idea leads to very fast data location and is flexible enough to allow
the user to find smith then scroll through the neighbouring records for the
exact match.

I base my grid and search field directly on the table and I don't use SQL.

Rgds
Jan
"Jeff Grippe" <jeff@door7.com> wrote in message
news:13fkra877o77lb2@news.supernews.com...
> Hello and thanks in advance for the help...
>
> I have a grid control which is based on a small table that is extracted
> from a larger table. When the user scrolls to the last row, I would like
> to be able to requery and show the next (or previous if the user scroll up
> from the first record) batch of records.
>
> How do you detect all of the various method (Keyboard, Scroll Bars, Etc.)
> that the user might be using to scroll up from the top or down from the
> bottom.
>
> I could put menu items on the menu to requery but it would be more natural
> if they could just work in the grid.
>
> Thanks,
> Jeff
>

Jeff
Thu Sep 27 05:57:27 PDT 2007


"Jan" <Nomail@Nowhere.com> wrote in message
news:O84zDzMAIHA.4568@TK2MSFTNGP02.phx.gbl...
> Jeff,
>
> This idea leads to very fast data location and is flexible enough to allow
> the user to find smith then scroll through the neighbouring records for
> the exact match.
>
> I base my grid and search field directly on the table and I don't use SQL.
>

This is what I do now as well but there are some issues that I have to face.

1. The table will eventually hit the 2GB wall. I either have to purge data,
redesign it so that it is smaller, or move to an SQL database. The first two
options are only temporary fixes but they allow me to keep using the browse
method of displaying data.

2. For reasons that I don't completely understand, VFP (7 SP 1) is
inconsistant in the way it deals with data from multiple tables. For Example

The main table is ARTRAN. I have:
SET RELATION TO CLIENT + DEBTOR + CHK_NUM + DTOS(CHK_DATE) INTO CHECKS
and the CHECKS table is set to an index that matches the SET RELATION

The ARTRAN table is that recordsource for the grid

I have a dynamicbackcolor for the column with the check number which says:

IIF(CHECKS.BOUNCED, RGB(255,0,0), RGB(255,255,255))

Which means that I want the check number in a red background if the check
bounced.

This works fine on some of my users systems, works eratically on others, and
doesn't work at all on others.

All users are using HP PC's with Win XP Pro and all users are using the same
app.

When I use a query that includes data from both tables and the
dynamicbackcolor only has to look at the query results, it works on every
system every time.

Go figure!

Thank again,
Jeff

> Rgds
> Jan
> "Jeff Grippe" <jeff@door7.com> wrote in message
> news:13fkra877o77lb2@news.supernews.com...
>> Hello and thanks in advance for the help...
>>
>> I have a grid control which is based on a small table that is extracted
>> from a larger table. When the user scrolls to the last row, I would like
>> to be able to requery and show the next (or previous if the user scroll
>> up from the first record) batch of records.
>>
>> How do you detect all of the various method (Keyboard, Scroll Bars, Etc.)
>> that the user might be using to scroll up from the top or down from the
>> bottom.
>>
>> I could put menu items on the menu to requery but it would be more
>> natural if they could just work in the grid.
>>
>> Thanks,
>> Jeff
>>
>