Competing record selection methods 
Author Message
 Competing record selection methods

Hi, I'm trying to teach myself FoxPro 2.6 on the Mac.  I also enjoy
pushing bamboo shoots underneath my fingernails.  :-)

I have a question about selecting records and then manipulating or
reporting them, and if someone can point me to a FAQ or good book that
would be great.

In other database programs like 4th Dimension, you can search for a group
of records and then use them as a selection, as in:
   SEARCH ([file]; [file]field = "value")
   MODIFY SELECTION or PRINT SELECTION or EXPORT selection and so on,
      until the selection changes with another SEARCH command or something
      like ALL RECORDS ([file]).

I gather that FoxPro doesn't really work this way; there's no concept of a
current selection except when generated and explicitly saved by the SQL
SELECT command.  There are also filters, and also WHILE clauses for
commands like REPORT.

So what is the best way to establish a selection of records and then print
the results using a .FRX report form?  Do filters and SELECT statements
and WHEN clauses all work together, further whittling down the selection
as they're invoked?

I'm adapting an old Fox for DOS project in which the programmer uses a
subprocedure to establish a FILTER based on user input.  Using the
commands
    SELECT alias
    REPORT FORM form.frx WHILE field="value" TO PRINTER
seems to work, invoking both the filter for the file and the WHILE condition.

Is this more or less desirable than using the SQL SELECT command?  I tried
to use SELECT at first, including the HAVING condition to keep the filter
around, but I couldn't figure out how to save and use the results of the
query for the report (issuing the report immediately after the SELECT
command doesn't seem to do the trick).  Do I need to save the query
results to a cursor and SELECT the cursor object before issuing the REPORT
command?  

My head is spinning.  TIA for any suggestions; I promise to reciprocate
any help I get here in the future.

-Will D.

--

Faludi Computing     242 Townsend Street, San Francisco, CA 94107



Tue, 05 Aug 1997 04:16:05 GMT  
 Competing record selection methods

Quote:

>So what is the best way to establish a selection of records and then print
>the results using a .FRX report form?  Do filters and SELECT statements
>and WHEN clauses all work together, further whittling down the selection
>Is this more or less desirable than using the SQL SELECT command?  I tried
>to use SELECT at first, including the HAVING condition to keep the filter
>around, but I couldn't figure out how to save and use the results of the
>query for the report (issuing the report immediately after the SELECT
>command doesn't seem to do the trick).  Do I need to save the query
>results to a cursor and SELECT the cursor object before issuing the REPORT
>command?  

If you employ a filter, as in SET FILTER TO you may be dissatisfied with the
speed of all actions you take on the filtered database, expecially browsing.

Indexing with a "FOR" condition will give you very satisfying results.  However
if you are programing for a LAN environment, the indexing will require
exclusive use of the file, making it unavailable to other users.

I work in FP for DOS.  I assume that you can do the same thing I can, which
is to send the query results directly to a report file.  There is even an
option for it on the RQBE screen.  When you say that sending the results
of the query to the report doesn't seem to work, what do you mean?  I think it
should work.  Make sure yourreport form does not contain aliases of some other
database where you really intend to have data from the query.

Yet another option is to send the query results to a table/database, where
you can treat it like any other table.



Thu, 07 Aug 1997 12:03:31 GMT  
 Competing record selection methods

Quote:


> >So what is the best way to establish a selection of records and then print
> >the results using a .FRX report form?  Do filters and SELECT statements
> >and WHEN clauses all work together, further whittling down the selection

> >Is this more or less desirable than using the SQL SELECT command?  I tried
> >to use SELECT at first, including the HAVING condition to keep the filter
> >around, but I couldn't figure out how to save and use the results of the
> >query for the report (issuing the report immediately after the SELECT
> >command doesn't seem to do the trick)...

> If you employ a filter, as in SET FILTER TO you may be dissatisfied with the
> speed of all actions you take on the filtered database, expecially browsing.

> Indexing with a "FOR" condition will give you very satisfying results.
However
> if you are programing for a LAN environment, the indexing will require
> exclusive use of the file, making it unavailable to other users.

> I work in FP for DOS.  I assume that you can do the same thing I can, which
> is to send the query results directly to a report file.  There is even an
> option for it on the RQBE screen.  When you say that sending the results
> of the query to the report doesn't seem to work, what do you mean?  I think it
> should work.  Make sure yourreport form does not contain aliases of some other
> database where you really intend to have data from the query.

> Yet another option is to send the query results to a table/database, where
> you can treat it like any other table.

OK, I'm learning...

I think I've figured out the syntax of the SQL SELECT command, with the
help of creating RQBE screens and SEEing the SQL commands.  I think my old
REPORT command wasn't printing the right info only because the preceding
SELECT command was finding the wrong info.  My current procedure looks
something like this:

   CREATE CURSOR cursorname (field1 c(20), field2 c(20))
   STORE FILTER ('filename') TO filtername
   SELECT field1, field2 FROM file INTO CURSOR cursorname ;
      WHERE field2 = suchandsuch HAVING &filtername ORDER BY field1
   REPORT FORM form.frx TO PRINTER

But I gather this will work even without the CURSOR commands; the SELECT
command stores the results in some mysterious place that the REPORT
command recognizes...is this right?  (If so, this is different from what I
was told to expect from FoxPro; I had heard it can't remember a selection
from one command to the next.)  But regardless, it's handy to have the
cursor around in case I want to browse it as well.  I may also employ
cursors when doing slow searches with UDF's...the slow SELECT query could
be performed on a temporary cursor, freeing the original file to other
(future) network users.  Is this a valid use of cursors?  Is it better to
use real tables (DBF's), or is that only important if I want to make
changes to the data?

So many questions, but just one more: is it important that I put the GROUP
command with the HAVING command in the above SELECT statement?  

Also I did find aliases to databases in my report form and took them out,
just to be safe.  And I'm still not sure what you mean by "Indexing with a
'FOR' command", but I'll RTFM and get there I'm sure.

Thanks a million for the help,
Will D.

-----------------------------------------------------------------

Faludi Computing     242 Townsend Street, San Francisco, CA 94107
-----------------------------------------------------------------



Sat, 09 Aug 1997 11:01:38 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Best method - selection list of ALL records

2. Mult-user record selection?

3. Random selection of records

4. Please help me with record selection!!

5. SQL, selection records where 2 agregated fields are not equal

6. SQL-Select Record no of table1 (table 2 for selection)

7. Grid record selection

8. random record selection

9. FPW2.6 - Method for matching a million records...

10. Error method interacting with other methods

11. Custom Form Method not visible from within ActiveX method

12. how get selection?

 

 
Powered by phpBB® Forum Software