On Fri, Nov 21, 2008 at 10:53 AM, Steve Ellenoff <[EMAIL PROTECTED]> wrote: > I'm curious what the best approach for this scenario is when working > with a non-vfp backend. > > User has a custom query based form where they can enter numerous > different criteria to select which data records to include in a > report they will run. When they click proceed the app pulls down the > data into a local VFP cursor and displays the returned results in a > grid with checkboxes to allow the user to *further refine* the list > of records to include in the report to run. Once the user clicks > proceed, only the checked records are included in the final report output. > > The current VFP approach is to do the initial select * for all the > initial records wanted, and then do a set filter on the checked records. > > How do I achieve this same thing for an SQL backend? Obviously the > first part is the same, but how do I tell the backend about the > checked records? I ask because there could be well over 3000 included > records (for year end reporting and such). I cannot envision me doing > a WHERE ID = 1 OR ID = 2 .... OR ID = 3000 for example.. > > I was thinking I could leave the "checked" filtering logic in VFP as > is, and just change the initial SELECT * to hit the SQL backend, but > I was wondering what the best practice is here, as this seems > wasteful if the user only ends up selecting a few records to run on > the report, and pulling SELECT * from a SQL backend is never a best > practice if it can be avoided. > > Another idea I had was to write to a temp table on the backend the > records the user selected and then select from the temp table and > original table to get the final result. What I don't like about this > idea is having to send possibly 3000 insert statements if the user > wants to include 3000 records in the report. That sounds like it'd be > terribly slow. ---------------------------------
How would you do this today with a VFP backend? How many rows do you think could come back when the user gives the initial request? > When they click proceed the app pulls down the > data into a local VFP cursor and displays the returned results in a > grid with checkboxes to allow the user to *further refine* the list > of records to include in the report to run. Once the user clicks > proceed, only the checked records are included in the final report output. Will the report have to use SQL as it's data source or still stick with VFP containers in VFP reports? If SQL then you could try this: Iterate your grid for a checked item. Grab it's key and use that as the param for a SP on the server to do the insert. So you are executing N statements. If you were going to do this for thousands then this is a bad idea. Could you find that you had ranges from customers, or invoices? If so you have to make a diff SP(s) to handle ranges. Maybe you find that from the thousand that they are excluding only 10% so you do a bulk insert into a #temp table and delete what was not checked. -- Stephen Russell Sr. Production Systems Programmer Mimeo.com Memphis TN 901.246-0159 _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
