Previous/Next button using SQL 2012 and VFP 1

[EzLogic]

I am using VFP9 sp2, SQL 2012


I have a form.
1-customer, enters the item number (or can click search and return item item)
2-Call a stored procedure from VFP to sql to Fetch Item record
2.5 - Call another small procedure to get Next/Previous item#
3-values appear on form.

Store Previous Item and Next Item on form property.

2 buttons:
next >
Prvious <

on click of each:
if next button:

lcNextItem = thisform.cNextItem
if not isnull(lcNextItem) and not empty(lcNextItem)
   thisform.txtcItem.value = lcNextItem
   thisform.GetValues()
   Thisform.GetPreviousNext()
   refresh the fields etc..
else
   messagebox("Last record reached",48,thisform.caption)
   return 
endif

If Previous Values

lPrevItem = thisform.cPreviousItem
if not isnull(lcPrevItem) and not empty(lcPrevItem)
   thisform.txtcItem.value = lcPrevItem
   thisform.GetValues()
   Thisform.GetPreviousNext()
   refresh the fields etc..
else
   messagebox("First record reached",48,thisform.caption)
   return 
endif

Stored procedure in SQL 2012

create procedure GetNextPreviousValuesByItem
  @tcItem char(20),
  @tnfk_Store int

as 

SELECT * FROM (
SELECT
LAG(p.Item) OVER (ORDER BY p.Item) PreviousValue,
p.Item,
LEAD(p.Item) OVER (ORDER BY p.Item) NextValue
FROM ICITEM p
where FK_STORE = @tnfk_Store
and p.Serialized = 1
and p.deleted = 0
) H
where  h.item = @tcItem

Hope this is useful to someone.



Ez Logic
Michigan

 

[Mike Lewis]

Thanks for posting this, EZ.

May a make a couple of minor observations.

First, with a client/server architecture, there is really no such thing as "previous record" and "next record". Or, rather, there is no "natural" sequence in the records. The next and previous records are in the mind of the user. I assume that your stored procedures handles that in some way (sorry if that's obvious, but it's not to me).

Secondly, I personally wouldn't do the messagebox saying that the first or last record has been reached. Much better to grey out the relevant button when you reach start/end of table. If you don't, it's like saying to the user: "Here is a button you can press to get the next record. Oh no, you shouldn't have done that after all". But if I've understood your code correctly, I can't see how you can do that.

Just a couple of thoughts off the top of my head.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[EzLogic]

Great feedback, and I am with you on the disabling of the previous/next.

I posted the code directly from my "sandbox" as I was testing building.. that is not my production code. I was trying to "show" my friends on Tek-tips some possibilities.
but, i like your idea of graying (disabling) the buttons.

In my vertical market, the item number is sequential. I think you could apply the same logic to other types, such as names? addresses? etc..

again, i was just sharing my findings, and in my application, i had a need to have a "next/previous" for item# of inventory, and i moved to sql, from vfp backend, and i wrote this logic to help me get the same result as if i were using vfp.



Ez Logic
Michigan

 

[Olaf Doschke]

There is a (rather) new mechanism in SQL Server MySQL had for a long time: fetching data in pages with the extended ORDER BY clauses OFFSET and FETCH

SELECT ...some fields... FROM Table ORDER BY some column OFFSET N1 FETCH NEXT N2

http://technet.microsoft.com/en-US/library/ms188385.aspx

You can do that with OFFSET 0 and FETCH NEXT 2 to get the first 2 records, obviously, then just remember the offset as the current "previous" record offset, increment or decrement (obviously it must stay >=0) and fetch 3 records in all cases other than OFFSET 0.

If you only get 2 records you're obviously getting to the end of the table in the given order.

What Mike says is right, data is a set, there is a physical order, but it differs much from what we know from DBFs, data is arranged in pages with each N records and they can be partly non used, but of course you can give any table an ORDER by ORDER BY, so it makes sense to make OFFSET and FETCH options of ORDER BY.

LAG and LEAD are nice to know anyway.

Bye, Olaf.

 

[EzLogic]

Olaf and Mike,

I think you are missing the point. I am not saying that the data is NOT a set, and there is physical order. I know, there is NOT a physical order.

However, just because there is no physical order, that doesn't mean, the user cannot navigate prevous and next.

and I am NOT talking bouat next/prevous of record# here or row#.

Example:
You a membership form.
Member ID is sequential
user pulls up a member
user want to go to previous member or next member

another example:
you have a form with names on it.
you pull up a customer
navigate thru the names next/previous

you have pizza order form:
you pull up an order
you want to go to previous order/ next order.

in VFP, we did set order to (what the order number) and we skipped forward or backward.

in SQL, no skipping is allowed, and we do NOT fetch the entire table. we fetch one record at a time.

my method, in my opinion, is one of the many ways it could be done in SQL server.



Ez Logic
Michigan

 

[Dan Freeman]

Yes, we DID do that in VFP. Well, YOU did. I didn't.

If a user is going to pay an invoice, they likely have that invoice in hand and know the name of the payee so let them search for it. Don't make them go through the entire vendor table one-by-one. That's just horrible U/I made popular because it's easy demo-ware.

In my current job, we have ~1500 retail locations in the US, Puerto Rico, Canada, Mexico, and Dubai. (Don't ask.) I can't think of any situation where "next/previous" makes any sense at all in the daily doing of business. If a user is looking at a customer record when the phone rings and it's a different customer, should they next-next-next until they get there? Do YOU like being on hold when you call customer support?

I got my start as a programmer after once running the dBase III-Plus application generator, where everything was based on next/prev. I took one look and said "I can do better than that!

 

[Mike Lewis]

Dan has explained it well. There a few situations where the user might want to step through records in some order, but not many. If you are working with a particular customer's record, why would you then want to go to the next customer in alphabetical (or some other) order? More likely, you'll just search for the next customer that you need to process.

EZ, I can see your point. You have a style of working that has served you well, and you don't want to abandon that when moving to a client/server back-end. Your code provides a good workaround in that situation. But it is not the ideal solution - at least, not in my opinion.

I gave up using the next/previous model (aka VCR-style controls) around 1999. I really believe that the several applications I have developed since then have a much more intuitive user interface - and also take better advantage of the client/server architecture.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[audiopro]

I have an app which has a justifiable use for previous and next.
Every month, my client rings round outstanding accounts and the software helps him.
He clicks a button 'Outstanding accounts' and the first one appears, he rings them, threatens to cut off their credit/arms/legs/fingers etc. if they don't pay and then moves onto the next one (by hitting the next button).
He did once complain that the next button had stopped working. It was in fact greyed out as he was on the last outstanding account.
Instead of greying it out, I hid the button - then of course he complained that the button had gone altogether - so I added a caption, 'End of List, Time for Coffee', he was happy with that.

Keith

http://www.studiosoft.co.uk

 

[Olaf Doschke]

I agree to Dan and Mike, also I see there is a good use for paging, but less for next/previous reecord. Rather for next/previous page of data. And that's what OFFSET and FETCH clauses can support. You can also use it for a triple of records, as you do. In the end it's nothing wrong, if that is an accepted interface.

Nevertheless the query you did and also the OFFSET clause are slow with very large tables, SQL Server has to sort anyway and skip records you don't want to fetch. It's perhaps more economic to fetch more data, eg pages of 40 records, even if you only show 3 of them. Or use a grid and FetchAsNeeded, as Mike suggested in thread184-1725896 in his post from 3 Feb 14 14:44.

Optimizing the amount of data fetched is not the only thing you should be concerened about. In your solution you also miss the fact you query records you already have, eg each skip forward or reverse only would need to fetch one more record, not three again.

Bye, Olaf.

 

[craigber]

There is always a case for next/previous.
- audiopro has one. In this case, you query the data to retrieve all outstanding accounts and bring back that set of data. Next/Prev works on the data set retrieved.

- You search for a customer by last name, say Smith. All rows for Smith are returned. Again, next/prev work on that set of data.

Entering a specific invoice number is not one of the ways this is normally done.

In all cases, next/prev works on the set of data that's returned.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[EzLogic]

Craig, YES!!

You put it more eloquently than I did.

In my vertical market, the next/previous (data record, not record# or row#) is used often.

and in SQL backend, i do NOT want to get ALL the records. I only want to fetch 1 record at a time, that meats the criteria.

What i tried to share/show, in one way, and again, it can be re-written to fit any business rule or logic, in this case, it fits my way to solve the user's demand, a way, to have the previous item#, the current item# and the next item# of inventory.

Again, that is my way of doing and i thought i'd share my findings with other users in case someone might find it helpful, or even, someone might suggest better techniques.

I always learn from the gurus on this board, and others. and i am sure anyone can always learn.

Ez Logic
Michigan

 

[Dan Freeman]

Hi Craig,

If a user is looking for "all the Smiths" I usually display them in a grid rather than next/prev. They want to see all the relevant data on all the rows all at the same time to make the right choice. I still don't like next/prev when selecting from a list. Show me the doggone list! (That's me as a user talking.)

We've all been brainwashed to think that next/prev is the best way to show a set. I'll stand by my opinion that the best way to show a set is to show all members of that set. In a list.

JOMO

 

[audiopro]

I agree that most cases are better suited to seeing all of the data.
The exception is a case like the example I gave, where each record is being dealt with individually.

Keith

http://www.studiosoft.co.uk

 

[EzLogic]

Right. Only when you are dealing with certain scenarios, like audiopro mentioned.

anyone, hope that helped some one

Ez Logic
Michigan

 

[craigber]

I'll also present the data in a grid. I'll also provide next/previous

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog