Best Way To Retrieve Data For Display & Edit

[Auguy]

I hope someone can shed some light on this subject. I have an application where the users don't need to see all of the records in a given table. Currently, I open the table and let them navigate through the records using next, prev, etc., or they can use a grid to scroll through the table. I was thinking about letting the users set some parameters and then selecting all of the fields into a cursor and doing the edits on the cursor and then doing an update back to the live table (checking for changes, etc.) Or, just selecting the unique ID from the table into the cursor and setting a relationship into the live table so the user can edit the live table directly. Anybody have any thoughts, or is there a better method?

Thanks,
Auguy

 

[Dan Freeman]

Hit the help file.

You just exactly described the behavior of updateable local views, even parameterized views.

As the spaghetti commercial used to say: "It's in there!

 

[Auguy]

OK, I've been looking into views and I have a couple of questions. On a form I normally have a grid in one of the form "Tabs" of all of the records in the forms "master" table such as Customer. If I use a view as the source for this grid and I allow the user to select a large number of records (200,000) into the view, is this going to cause any speed issues? I realize it depends on the number and types of fields in the view. On the detail "Tab" of the form is where I do all of the editing. If I use a view as the datasource of all of the controls on this "Tab", are there any speed issues in refreshing the view when the user clicks the next and previous buttons, etc.? Also, is it better to use two views when doing entry/edit on things like invoices where you have a header record and many detail records? This is a multi-user app on a LAN with occasional users using terminal server. Any suggestions, guidance, or experiences will be appreciated.

Thanks,
Auguy

 

[Dan Freeman]

Why on earth would you allow a user to run a query that returns 200,000 records? Are you going to give them a picklist? Hey, I love scrolling through hundreds of thousands of choices! ;-)

That's just plain bad design and yes, it will cause performance issues. For everyone on the LAN. People using Word will notice your database app that's slinging hundreds of thousands of records across the wire.

My suggestion to you would be to go and get a good framework. We all worked these issues out in the early 1990's and the frameworks all reflect that.

For a free framework, Google CODEBOOK. Or look for Visual FoxExpress or Visual MaxFrame. They're great learning tools as well as great productivity tools.

 

[Mike Lewis]

Auguy,

I totally agree with Dan when he says that you should not be selecting 200,000 records.

However, I disagree that the solution is to buy a framework. That sounds like a long-term solution to a short-term problem.

I've nothing against frameworks, and by all means you should consider using one, but buying a framework won't give you an immediate solution to this particular problem. In the time it takes you to learn your way round the framework, you could easily have developed a fully working solution.

My advice would be to re-think how the user will interact with this form.

For example, you could keep your navigation buttons, but add a Search button that brings up a modal search form. Let the user enter the parameters. Do a query, and put the query results in a grid (still on the search form). Let the user visually select the customer of interest, then double-click on the relevant grid row, at which point the modal form will close and return the ID of the selected customer to the main form, which will then navigate to the customer in question.

I'm not saying that's necessarily the best solution, but it's an example of how you can solve this kind of problem just by considering other ways to go about it.

Mikw

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Mike Lewis]

Mike,

Which car would you rather drive?

I've no idea. I have no interest in driving any car.

But, if I've understood your analogy, you are saying that it is always less work to use a framework than to build a system from scratch?

If so, I don't argue with that. I was simply saying that, just because Auguy had a problem with a particular form, you won't solve that problem by buying a framework. If the design of the form was wrong (as I believe it was, in this case), the best framework in the world won't put it right.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Imaginecorp]

I have very strong views on Frameworks, ALL against. They are a waste of money. You buy a framework, now you have to learn another piece of “software”. You try to customize it and if you have not initialized something in step 1, down the line in step 250 it does not work. Many moons ago when I was an independent, I have had clients uninstall frameworks which they had bought.

My advice: Write your own Class libraries rather than use somebody else’s (framework). You will be amazed at how easy it is and what you can accomplish and the best part is you will know every nuance.

Having said that;

…just selecting the unique ID from the table into the cursor and setting a relationship into the live table so the user can edit the live table directly…

That is the way we do it. But without relationships.
We create a cursor, depending on what the user wants to see, with a unique (system generated number in Tables) ID, display it in a List box (depending on the number of records fetched) If more than 60,000 (old limitation of a List box), we use a grid. The form has the fields from the Table. In the Interactivechange / afterrowcolumn change, we do an INDEXSEEK on the table and do a form refresh. This way we do not need Next, Back buttons as the user can navigate via the list box or grid. The Add & Edit Buttons do a Table Update, The Cancel button Reverts.

For Child tables we use Parameterized Views, which will not bring too much data and display them in List boxes / grids (if user interaction is required) on the form. Again these views are Re-Queried in the InteractiveChange or AfterRowColumnChange.

But, your design has to be perfect.

ALL of our Forms do this. One brings in and manages 165,000+ records with 16 or so parameterized views.

But our apps use Terminal Servers for our multi-national clients. Our local clients, who do not have that many records use a Lan without any performance issues

Regarding Speed: Yes; bringing in 200,000 records over the wires will definitely slow the App down, depending on the Lan and the traffic. But if the tables are optimized to the Hilt, it’s not that bad, as once they are on the user’s machine everything speeds up.

Though; Nobody and Nobody wants to see or browse through 200,000 records. Display choices before creating a Cursor i.e. Open Transactions, Payments Due etc.

But you have an advantage:

This is a multi-user app on a LAN with occasional users using terminal server.

Why not for every user? Now your app will be blazing fast. If the client does not want to spring for the extra licenses, for occasional users use Remote Desktop, It’s free but has to be tweaked.

 

[Auguy]

I am not going to let the users bring back 200,000 records in a query. I was just looking for some information as to how others have used views and any associated speed or other issues they might have encountered.

Thanks to all for your comments.

Auguy

 

[Auguy]

Just thought of another question. Does it make any difference (speed, etc.) if I use a parameterized view or a cursor created with a select command to create a subset of records to use in a grid (or list box) for the user to select the record they want to see displayed in a detail form?

Thanks,
Rob

 

[Mike Lewis]

Rob,

Does it make any difference (speed, etc.) if I use a parameterized view or a cursor created with a select command to create a subset of records to use in a grid

No significant difference. A view is really just a wrapper for a SELECT. Behind the scenes, VFP does the same work in both cases.

What will make a difference is whether the query can be optimised, that is, whether it can use index tags to resolve the query. If it can, and if the table being queried is at all large, the query will run very much more quickly.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Imaginecorp]

Auguy:
As I am sure you already know, The most important thing is Design, Design & Design. (Architecture) A bad design and bad planning will make the best app look bad.
By this I mean: Tables optomized does not mean evey field has an Index, only fields that are commonly used as criteria. Create views in design mode rather than on the fly, this is also true for Select statements. i.e. A "Select" query which is often used should be converted to a view. Create as many as you want as the only thing that resides in your app is the defination (no data). They run faster than a Select on the fly as they are pre compiled.
Carfully plan the app and I am sure it will be Incredible, always remember, application development is 80% planning and 20% coding (IMO)
Good Luck

 

[Auguy]

Thanks again to both of you. Generally, I let users enter parameters on a "Find" form such as customer id, invoice date range, open/closed, etc. There are usually many more parameters on these forms. I build a "where" string on the fly (ignoring the parameters they didn't enter a value for) and select the records using this where string. This method seems to work pretty well. I know I can make parameterized views, but is there a way to make a view ignore certain parameters without passing it a bunch of dummy values for the unused parameters? Just curious!

Thanks,
Auguy

 

[Dan Freeman]

Guys, I suggested a framework so that he could see how someone else had already cracked the nut he's trying to crack.

Personally, I do not currently use any of the commercial frameworks. (There! I've admitted it!) But I've learned something from every doggone one of them.

There will be other uncracked nuts in the bowl.

 

[Imaginecorp]

Mike ;

I respectfully disagree...

"On the shoulders of giants we can see farther." This begs the question, who would you define as a Giant? There are some in the Fox community that have mastered a few more tricks than the rest of us, but I have learnt more in the last 20+ years from junior programmers, who were willing to experiment by pushing the envelope than from most articles books I have read and dev cons attended.

“Building your own framework only works if you're good enough to build one…” Could not agree with you more, but does one really need a framework? Would not a few decent class libraries suffice?

I am not familiar with SPT, I am sure it is excellent. I keep away from frameworks. Having had the displeasure of working with one written by, I am sure, a mutual friend of ours and finally having the client get rid of it, much to the joy of the rest of the clients programming staff.

“…and even be able to dynamically add where clauses…” Here you have exactly described what Auguy is already doing, creating a Where clause on the fly. This for a number of scenarios is the ideal way to do it. We let our clients create ad hoc reports in this way.

“Do not mix lists and grids.” Both have their pros and cons, my suggestions were for using which ever suits. Yes, Lists, which as you know are just Arrays in disguise, used to have a limitation, but to display about 40 to 60,000 is a breeze, and are great if used for selection to display the record on a form, less baggage than a grid. Done it for a number of years. Currently display 160,000 records in one without any problems and this app is accessed by over a 100 users.

But then again, this is only my opinion…

 

[Imaginecorp]

but is there a way to make a view ignore certain parameters without passing it a bunch of dummy values for the unused parameters?

Not that I know of. The way we do it, is we open a form which a user can select from. We do not give the user free "rein". This is because the users may select too many criteria's, thus slowing the query down considerably. That will make your app look bad. Find out what criteria’s are commonly required and display those only. Also present a choice “Build your own...” For this use your method of building the Select & the Where on the fly, But warn the users that this may take some time depending on the complexity. Forewarned, the users are not likely to complain too loud…

 

[Mike Lewis]

Rob,

I let users enter parameters on a "Find" form such as customer id, invoice date range, open/closed, etc. There are usually many more parameters on these forms. I build a "where" string on the fly (ignoring the parameters they didn't enter a value for) and select the records using this where string.

I've used exactly the same technique in some of my projects, and it works extremely well.

Personally, I tend not to create local views for this type of query. I just query straight into a cursor. As I said earlier, there's no substantial difference in performance. The (slight) drawback of a local view is that you need a DBC to hold them.

But, before anyone insists that local views are better than ordinary cursors, let me say that the design you described will work perfectly well either way.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Auguy]

Thanks again to all who responded with their opinions and recommendations as to how to approach this situation. Makes great food for thought.

Auguy

 

[Imaginecorp]

It's good this is remaining civil. To many of these discussions seem to deteriorate.

Mike; I have been in this business toooooo long and know and respect many of you (fox community) either personally or through reputation and correspondence to be anything but civil.

 

[Auguy]

Mike (Yearwood), you mention a potential problem with views. Is it in using the views, or during an update, or what? Is there a work-around? I am thinking of using views only for tables such as invoice detail lines, etc. I plan on using a cursor created by building a where string from user supplied criteria for navigation purposes. Then moving the record pointer in the "Live" file to the id in the cursor and refreshing the detail view. Any insight on the multi-user issue with views would be appreciated.

Thanks,
Auguy

 

[opticalman]

Auguy

Watch out, here’s one more opinion. I am a ‘part-time’ programmer. The one application I have built, I use at my full time job as an optician. The data size is about 30,000 patients, 70,000 services, 50,000 exams, 200,000 ledger entries, and more. I use over 60 parameterized remote views and I’m adding more all the time. I also use some SPT. Especially via a class that I use on some of the forms. Performance has been good with views and SPT. I wish I had known more about frameworks. I may have used one. Whenever possible, I always start with something that limits the number of records retrieved by the views to about 100 or less. For me, the trick to multi-user and views was learning to put in TABLEUPDATE() in the right places.

Jim Rumbaugh