foxpro 2.6 for dos: Code

foxpro 2.6 for dos: Code

Post by Brian Copela » Sun, 13 Aug 1995 04:00:00



J>I started a program that allows for searching with you basic query
J>abilties and such. My problem is this:

J>I set a relation between two tables based a a 6-digit character code.
J>Both tables have this code field, and it functions as a key. I do the
J>opening of the databases and set relations like this-

J>USE d:\fpd26\inv\inv_desc.dbf IN 1 ALIAS inv_desc     && parent
J>USE d:\fpd26\inv\inv.dbf IN 2  ALIAS inv  && child
J>SELECT inv_desc
J>SET ORDER TO TAG inv_code
J>SELECT inv
J>SET RELATION TO desc_code INTO inv_desc

J>Ok...I get the search working based on the code that is in each table.
J>Now what has happenned is that a filed in inv_desc.dbf is out of whack
J>when displayed. I have a field called 'desc', which is a description
J>of that item relating to the 'code' field. I want the 'desc' field to
J>show as alphabetical, but foxpro manual says I must index on the field
J>I want to set the relation to. Is there any way for me to alphabetizes
J>the 'desc' field so that user can search through the descriptions with
J>ease?

J>Thanx for all the help in advance

J>Jerome

I am a bit confused by your use of 'parent' and 'child'.  Normally the
parent record is the one from which the relationship is set and the
child is the one that the relationship is set into.  Can you describe
what type of data is stored in each table?

If you want to display a table in alphabetical order then the table must
be indexed on the field and the tag must be active.

A possible work around is to use inv_desc again in another workarea with
the order set to the alpha tag.


 
 
 

foxpro 2.6 for dos: Code

Post by Christopher R. Shor » Mon, 14 Aug 1995 04:00:00



Copeland) writes:

>J>I started a program that allows for searching with you basic query
>J>abilties and such. My problem is this:

>J>I set a relation between two tables based a a 6-digit character
code.
>J>Both tables have this code field, and it functions as a key. I do
the
>J>opening of the databases and set relations like this-

>J>USE d:\fpd26\inv\inv_desc.dbf IN 1 ALIAS inv_desc     && parent
>J>USE d:\fpd26\inv\inv.dbf IN 2  ALIAS inv  && child
>J>SELECT inv_desc
>J>SET ORDER TO TAG inv_code
>J>SELECT inv
>J>SET RELATION TO desc_code INTO inv_desc

>J>Ok...I get the search working based on the code that is in each
table.
>J>Now what has happenned is that a filed in inv_desc.dbf is out of
whack
>J>when displayed. I have a field called 'desc', which is a description
>J>of that item relating to the 'code' field. I want the 'desc' field
to
>J>show as alphabetical, but foxpro manual says I must index on the
field
>J>I want to set the relation to. Is there any way for me to
alphabetizes
>J>the 'desc' field so that user can search through the descriptions
with
>J>ease?

>J>Thanx for all the help in advance

>J>Jerome

>I am a bit confused by your use of 'parent' and 'child'.  Normally the
>parent record is the one from which the relationship is set and the
>child is the one that the relationship is set into.  Can you describe
>what type of data is stored in each table?

>If you want to display a table in alphabetical order then the table
must
>be indexed on the field and the tag must be active.

>A possible work around is to use inv_desc again in another workarea
with
>the order set to the alpha tag.



(with apologies to Mr. Copeland--I can't find the original of this
post)

Jerome:

I too am confused as to what this key field does.  Do your users search
using the key or the description?  Also, how large is this database?
(and how large might it get?)  I have a program that makes labels.  It
has a numeric key (label number) and a description.  The two are not
related.  I let the user input either the number or as much of the
descripion as they remember.  If the field they enter is a number, it
gets the appropriate record with SEEK().  If they enter alpha
characters, the program uses COPY TO ... FOR to copy all records for
which the description contains that substring to a temporary file,
which is then displayed in a browse window.  (You could SORT or INDEX
this file alphabetically, thus eliminating your problem.  Since it only
has a few records at this point, it's pretty quick).  The user scrolls
down this window and presses <enter>.  Since the temporary database
contains the key field also, it is a simple matter to close that window
and SEEK() the key field in the database I am actually using.

It sounds obtuse and a Foxpro guru could probably do it in half the
code, but it works and its blindingly quick.  The only thing is that
the database I use it on has about 3,000 or so records, so I have no
idea how this method would work on a large system.

Hope I haven't wasted your time telling you useless things...  If you
want email me and I'll send you the relevant source code.  It's hardly
brilliant, but it works...

Chris Short


 
 
 

foxpro 2.6 for dos: Code

Post by jer.. » Wed, 16 Aug 1995 04:00:00



>J>USE d:\fpd26\inv\inv_desc.dbf IN 1 ALIAS inv_desc     && parent
>J>USE d:\fpd26\inv\inv.dbf IN 2  ALIAS inv  && child
>J>SELECT inv_desc
>J>SET ORDER TO TAG inv_code
>J>SELECT inv
>J>SET RELATION TO desc_code INTO inv_desc
>J>Ok...I get the search working based on the code that is in each table.
>J>Now what has happenned is that a filed in inv_desc.dbf is out of whack
>J>when displayed. I have a field called 'desc', which is a description
>J>of that item relating to the 'code' field. I want the 'desc' field to
>J>show as alphabetical, but foxpro manual says I must index on the field
>J>I want to set the relation to. Is there any way for me to alphabetizes
>J>the 'desc' field so that user can search through the descriptions with
>J>ease?
>J>Thanx for all the help in advance
>J>Jerome
>I am a bit confused by your use of 'parent' and 'child'.  Normally the
>parent record is the one from which the relationship is set and the
>child is the one that the relationship is set into.  Can you describe
>what type of data is stored in each table?
>If you want to display a table in alphabetical order then the table must
>be indexed on the field and the tag must be active.
>A possible work around is to use inv_desc again in another workarea with
>the order set to the alpha tag.


I figured out my problem, but thanx for the help
 
 
 

foxpro 2.6 for dos: Code

Post by Sea » Wed, 16 Aug 1995 04:00:00




>Copeland) writes:
>>My problem is this:

>>J>I set a relation between two tables based a a 6-digit character
>code.
>>J>Both tables have this code field, and it functions as a key. I do
>the
>>J>opening of the databases and set relations like this-

>>J>USE d:\fpd26\inv\inv_desc.dbf IN 1 ALIAS inv_desc     && parent
>>J>USE d:\fpd26\inv\inv.dbf IN 2  ALIAS inv  && child
>>J>SELECT inv_desc
>>J>SET ORDER TO TAG inv_code
>>J>SELECT inv
>>J>SET RELATION TO desc_code INTO inv_desc

[snip]

- Show quoted text -

Quote:>Jerome:
>I too am confused as to what this key field does.  Do your users search
>using the key or the description?  Also, how large is this database?
>(and how large might it get?)  I have a program that makes labels.  It
>has a numeric key (label number) and a description.  The two are not
>related.  I let the user input either the number or as much of the
>descripion as they remember.  If the field they enter is a number, it
>gets the appropriate record with SEEK().  If they enter alpha
>characters, the program uses COPY TO ... FOR to copy all records for
>which the description contains that substring to a temporary file,
>which is then displayed in a browse window.  (You could SORT or INDEX
>this file alphabetically, thus eliminating your problem.  Since it only
>has a few records at this point, it's pretty quick).  The user scrolls
>down this window and presses <enter>.  Since the temporary database
>contains the key field also, it is a simple matter to close that window
>and SEEK() the key field in the database I am actually using.
>It sounds obtuse and a Foxpro guru could probably do it in half the
>code, but it works and its blindingly quick.  The only thing is that
>the database I use it on has about 3,000 or so records, so I have no
>idea how this method would work on a large system.

All I know is that a far better way (and considerably faster) to do
searches on related tables is to construct a SQL statement to do the
job. I have done horrendous things with related tables, and it never
has once necessitated a SET RELATION command. Witness this hellish
example of one of the SQL statements in a recent report I coded:

m.balbdate = (abegbal) + '.bdate'
m.balprod = (abegbal) + '.product'
m.balreg = (abegbal) + '.region'
m.balcust = (abegbal) + '.customer'
m.balbalr = 'SUM(' + (abegbal) + '.balreb) AS balreb'
m.balbald = 'SUM(' + (abegbal) + '.baldisc) AS baldisc'
m.balbalp = 'SUM(' + (abegbal) + '.balprom) AS balprom'
m.xactdate = (prexact) + '.date'
m.xactprod = (prexact) + '.product'
m.xactreg = (prexact) + '.region'
m.xactcust = (prexact) + '.customer'
m.xactireb = 'SUM(' + (prexact) + '.invreb) AS invreb'
m.xactpreb = 'SUM(' + (prexact) + '.payreb) AS payreb'
m.xactidisc = 'SUM(' + (prexact) + '.invdisc) AS invdisc'
m.xactpdisc = 'SUM(' + (prexact) + '.paydisc) AS paydisc'
m.xactiprom = 'SUM(' + (prexact) + '.invprom) AS invprom'
m.xactpprom = 'SUM(' + (prexact) + '.payprom) AS payprom'
m.balfileprc = [TRIM(&balprod) + TRIM(&balreg) + TRIM(&balcust)]
m.xacfileprc = [TRIM(&xactprod) + TRIM(&xactreg) + TRIM(&xactcust)]
m.whereclause = [UPPER(&balfileprc) = UPPER(&xacfileprc)]

m.pbegbal = 'A' + SUBSTR(SYS(3),2,7)

SELECT &balprod AS balprod, &balreg AS balreg, &balcust AS balcust, ;
        &balbalr, &balbald, &balbalp, &xactprod AS xprod, &xactreg AS xreg, ;
        &xactcust AS xcust, &xactireb, &xactpreb, &xactidisc, &xactpdisc, ;
        &xactiprom, &xactpprom ;
        FROM (abegbal), (prexact) ;
        WHERE &whereclause AND ;
        &xactdate BETWEEN &balbdate AND (enddate) ;
        GROUP BY &balprod, &balreg, &balcust ;
UNION ;
SELECT &balprod AS balprod, &balreg AS balreg, &balcust AS balcust, ;
        &balbalr, &balbald, &balbalp, '', '', '', 0, 0, 0, 0, 0, 0 ;
        FROM (abegbal) ;
        WHERE &balprod NOT IN ;
                (SELECT &balprod ;
                        FROM (abegbal), (prexact) ;
                        WHERE &whereclause) ;
        GROUP BY &balprod, &balreg, &balcust ;
UNION ;
SELECT '', '', '', 0, 0, 0, ;
        &xactprod AS xprod, &xactreg AS xreg, &xactcust AS xcust, ;
        &xactireb, &xactpreb, &xactidisc, &xactpdisc, &xactiprom, &xactpprom ;
        FROM (prexact) ;
        WHERE &xactprod NOT IN ;
                (SELECT &xactprod ;
                        FROM (abegbal), (prexact) ;
                        WHERE &whereclause) ;
        GROUP BY &xactprod, &xactreg, &xactcust ;
        INTO TABLE (pbegbal)

Amazingly enough, this statement searches through multiple databases
with thousands of records in mere seconds. Good luck!

Sean
_________________________________________________

http://snoopy.concom.com              HYPERCON
Email me about our extended Internet services.

 
 
 

foxpro 2.6 for dos: Code

Post by Bruce Sheffe » Fri, 18 Aug 1995 04:00:00


The only reason to do it the way it is shown is to avoid a subroutine
call outside the report format.  The macro expansion isn't a big deal
since it is only done once for the select statement.
 
 
 

foxpro 2.6 for dos: Code

Post by Michael Hoore » Fri, 18 Aug 1995 04:00:00



> Witness this hellish
> example of one of the SQL statements in a recent report I coded:

>m.balbdate = (abegbal) + '.bdate'
...
>SELECT &balprod AS balprod, &balreg AS balreg, &balcust AS balcust, ;
>        &balbalr, &balbald, &balbalp, &xactprod AS xprod, &xactreg AS xreg, ;
>        &xactcust AS xcust, &xactireb, &xactpreb, &xactidisc, &xactpdisc, ;
>        &xactiprom, &xactpprom ;
>        FROM (abegbal), (prexact) ;
>        WHERE &whereclause AND ;
>        &xactdate BETWEEN &balbdate AND (enddate) ;
>        GROUP BY &balprod, &balreg, &balcust ;
>UNION ;

YUKKKKK!!!
That's scary code.  I haven't tried this myself, but would it not be
faster to generate one string for the above SQL, and doing a single &
macro expansion on it?

Ie.:

        jcSQL = "SELECT " + Balprod + " AS " + balprod + "," + balreg   etc.

and then run it by going :

        &jcSQL

Does anyone know how Fox executes code like the above?  
Just wondering?

Mike.

--

 
 
 

foxpro 2.6 for dos: Code

Post by Jon Wie » Sat, 19 Aug 1995 04:00:00


Quote:>Ie.:
>        jcSQL = "SELECT " + Balprod + " AS " + balprod + "," + balreg   >etc.
>and then run it by going :
>        &jcSQL
>Does anyone know how Fox executes code like the above?  
>Just wondering?
>Mike.

I know everything works if you removed "SELECT " from the string and did:

SELECT &jcSQL

I think you need to start the command with SELECT, but I'm not sure.

Jon

 
 
 

foxpro 2.6 for dos: Code

Post by JEFF GERSCHK » Wed, 23 Aug 1995 04:00:00


JW>>        jcSQL = "SELECT " + Balprod + " AS " + balprod + "," + balreg   >et

JW>>and then run it by going :

JW>>        &jcSQL

JW>>Does anyone know how Fox executes code like the above?
  >>Just wondering?

From my experience with FoxPro it will complete a "macro" substitution
on the above, and then execute it.

Hope that helps out...

Regards,


 * 1st 2.00 #1299s * Practiss makes perfict.