HMG Report from 2 Tables
Moderator: Rathinagiri
HMG Report from 2 Tables
Hi,
Another question revealing my 'xBase ignorance'! I have tried using the HMG IDE report wizard for this and also looked for HMG and other examples on the internet to no avail, so I hope someone can point me in the right direction. Essentially I want to produce a report driven from 2 tables (dbf files) - the HMG report wizard and all examples I can find are for a single table only. The specific example I have is with a master file (contacts) and a 'lookup' file (contact types). I want to produce a report driven from the contacts table showing contact name from that table followed by the text of the contact type from the contact types table. The linking mechanism (fairly standard) is that the contacts table has a field containing the contact type record ID for the relevant contact type from the contact types table.(numeric self incrementing) - this record contains the relevant text.
Thanks
Roger L
Another question revealing my 'xBase ignorance'! I have tried using the HMG IDE report wizard for this and also looked for HMG and other examples on the internet to no avail, so I hope someone can point me in the right direction. Essentially I want to produce a report driven from 2 tables (dbf files) - the HMG report wizard and all examples I can find are for a single table only. The specific example I have is with a master file (contacts) and a 'lookup' file (contact types). I want to produce a report driven from the contacts table showing contact name from that table followed by the text of the contact type from the contact types table. The linking mechanism (fairly standard) is that the contacts table has a field containing the contact type record ID for the relevant contact type from the contact types table.(numeric self incrementing) - this record contains the relevant text.
Thanks
Roger L
Re: HMG Report from 2 Tables
Hi, Roger.
I don't know whether this is what you're looking for, but are you using the format Table1->Field3 and Table2->Field5 (where Table1 and Table2 are the aliases for the two different work areas) to draw the data from the two different sources?
You might also want to look up "Set Relation" for more info.
I hope that this gets you rolling, but if not it, we might need more specific info.
Regards,
CalScot
I don't know whether this is what you're looking for, but are you using the format Table1->Field3 and Table2->Field5 (where Table1 and Table2 are the aliases for the two different work areas) to draw the data from the two different sources?
You might also want to look up "Set Relation" for more info.
I hope that this gets you rolling, but if not it, we might need more specific info.
Regards,
CalScot
-
- Posts: 1275
- Joined: Tue Sep 03, 2013 4:22 am
- Location: Tecámac, México
Re: HMG Report from 2 Tables
Hola,
Para poder tener acceso a dos tablas y ambas tablas tiene un campo en comun, entonces es recomendable hacer una relación,
///////////////////////////////////////////////
Hello,
To access two tables and both tables have a field in common, then it may be a relationship
Otra manera de hacerlo es hacer una sola DBF, combinando la información de ambas, Y así solo manipulas una a la hora de mandar a imprimir.
////////////////////////////////////////////////
Another way is to make a single DBF, by combining information from both, well only manipulate one at a time to send to print.
Saludos
Para poder tener acceso a dos tablas y ambas tablas tiene un campo en comun, entonces es recomendable hacer una relación,
///////////////////////////////////////////////
Hello,
To access two tables and both tables have a field in common, then it may be a relationship
Code: Select all
SET RELATION
Relate two work areas by a key value or record number
------------------------------------------------------------------------------
Syntax
SET RELATION TO [<expKey> | <nRecord> INTO <xcAlias>]
[, [TO] <expKey2> | <nRecord2> INTO <xcAlias2>...]
[ADDITIVE]
Arguments
TO <expKey> is an expression that performs a SEEK in the child work
area each time the record pointer moves in the parent work area. For
this to work, the child work area must have an index in USE.
TO <nRecord> is an expression that performs a GOTO to the matching
record number in the child work area each time the record pointer moves
in the parent work area. If <nRecord> evaluates to RECNO(), the
relation uses the parent record number to perform a GOTO to the same
record number in the child work area. For a numeric expression type of
relation to execute correctly, the child work area must not have an
index in USE.
INTO <xcAlias> identifies the child work area and can be specified
either as the literal alias name or as a character expression enclosed
in parentheses.
ADDITIVE adds the specified child relations to existing relations
already set in the current work area. If this clause is not specified,
existing relations in the current work area are released before the new
child relations are set.
SET RELATION TO with no arguments releases all relations defined in the
current work area.
Description
SET RELATION is a database command that links a parent work area to one
or more child work areas using a key expression, record number, or
numeric expression. Each parent work area can be linked to as many as
eight child work areas. A relation causes the record pointer to move in
the child work area in accordance with the movement of the record
pointer in the parent work area. If no match is found in the child work
area, the child record pointer is positioned to LASTREC() + 1, EOF()
returns true (.T.), and FOUND() returns false (.F.).
The method of linking the parent and child work areas depends on the
type of <expKey> and presence of an active index in the child work area.
If the child work area has an active index, the lookup is a standard
SEEK. If the child work area does not have an active index and the type
of <expKey> is numeric, a GOTO is performed in the child work area
instead.
Notes
. Cyclical relations: Do not relate a parent work area to itself
either directly or indirectly.
. Soft seeking: SET RELATION does not support SOFTSEEK and
always behaves as if SOFTSEEK is OFF even if SOFTSEEK is ON. This
means that if a match is not found in the child work area, the child
record pointer is always positioned to LASTREC() + 1.
. Record number relations: To relate two work areas based on
matching record numbers, use RECNO() for the SET RELATION TO
expression and make sure the child work area has no active indexes.
Examples
. This example relates three work areas in a multiple parent-
child configuration with Customer related to both Invoices and Zip:
USE Invoices INDEX Invoices NEW
USE Zip INDEX Zipcode NEW
USE Customer NEW
SET RELATION TO CustNum INTO Invoices, Zipcode INTO Zip
LIST Customer, Zip->City, Invoices->Number, ;
Invoices->Amount
. Sometime later, you can add a new child relation using the
ADDITIVE clause, like this:
USE BackOrder INDEX BackOrder NEW
SELECT Customer
SET RELATION TO CustNum INTO BackOrder ADDITIVE
Files Library is CLIPPER.LIB.
////////////////////////////////////////////////
Another way is to make a single DBF, by combining information from both, well only manipulate one at a time to send to print.
Saludos
Re: HMG Report from 2 Tables
Thanks for those responses - they are pointing me in the right direction I think. I will now investigate relationships and aliases etc in more detail and experiment. I have the feeling that what at first view appears complex will prove to be simple and straightforward once I get my head around it.
Thanks again guys!
Roger L
Thanks again guys!
Roger L
Re: HMG Report from 2 Tables
OK - I've been experimenting - and I think I'm missing something that's probably very simple!
My two tables are defined below :-
Procedure buildTestDBF
LOCAL DBDef := {;
{"MemberID","+",4,0},;
{"MemberName","C",30,0},;
{"MemberMTypeID","N",4,0};
}
DBCREATE("Members", DBDef)
Return
Procedure buildDBF
LOCAL DBDef := {;
{"MTypeID","+",4,0},;
{"MTypeDesc","C",30,0},;
{"MTypeDues","N",8,2};
}
DBCREATE("MembershipTypes", DBDef)
Return
I have entered some data and want to print a report showing the members name followed by the text of the member type (MTypeDesc). The code I am using for the report is :-
Procedure testReport
set date british
set century on
USE MembershipTypes INDEX MTypeID NEW
USE Members NEW
SET RELATION TO MembersMTyp INTO MembershipTypes
goto 1
DO REPORT;
TITLE 'Members';
HEADERS {'',''},{'NAME','TYPE'};
FIELDS{'Members->Membername','MembershipTypes->MTypeDesc'};
WIDTHS{30,30};
TOTALS{.F.,.F.};
NFORMATS{'',''};
WORKAREA Members;
LPP 50;
CPL 80;
LMARGIN 2;
PAPERSIZE DMPAPER_A4;
PREVIEW;
SELECT;
MULTIPLE
Return
The tables create OK and I have populated them with data. The report compiles OK but fails at runtime with the error :-
Error DBCMD/2001 Workarea not in use: ORDLISTADD
Any pointers much appreciated!
Roger L
My two tables are defined below :-
Procedure buildTestDBF
LOCAL DBDef := {;
{"MemberID","+",4,0},;
{"MemberName","C",30,0},;
{"MemberMTypeID","N",4,0};
}
DBCREATE("Members", DBDef)
Return
Procedure buildDBF
LOCAL DBDef := {;
{"MTypeID","+",4,0},;
{"MTypeDesc","C",30,0},;
{"MTypeDues","N",8,2};
}
DBCREATE("MembershipTypes", DBDef)
Return
I have entered some data and want to print a report showing the members name followed by the text of the member type (MTypeDesc). The code I am using for the report is :-
Procedure testReport
set date british
set century on
USE MembershipTypes INDEX MTypeID NEW
USE Members NEW
SET RELATION TO MembersMTyp INTO MembershipTypes
goto 1
DO REPORT;
TITLE 'Members';
HEADERS {'',''},{'NAME','TYPE'};
FIELDS{'Members->Membername','MembershipTypes->MTypeDesc'};
WIDTHS{30,30};
TOTALS{.F.,.F.};
NFORMATS{'',''};
WORKAREA Members;
LPP 50;
CPL 80;
LMARGIN 2;
PAPERSIZE DMPAPER_A4;
PREVIEW;
SELECT;
MULTIPLE
Return
The tables create OK and I have populated them with data. The report compiles OK but fails at runtime with the error :-
Error DBCMD/2001 Workarea not in use: ORDLISTADD
Any pointers much appreciated!
Roger L
Re: HMG Report from 2 Tables
Hi, Roger.
I only had a quick look, but try changing
Note that the two fields being related (the one specified in the "set relation to" and the other being the index of the child table) must have the same data type (or have a function in the set relation expression to convert the parent field to match the child) for the "set relation to" to be successful. Otherwise, I think that you're very close.
Good luck!
CalScot
I only had a quick look, but try changing
toSET RELATION TO MembersMTyp INTO MembershipTypes
Not sure where "MembersMTyp" came from!SET RELATION TO MemberID INTO MembershipTypes
or
SET RELATION TO MemberMYTypeID INTO MembershipTypes
Note that the two fields being related (the one specified in the "set relation to" and the other being the index of the child table) must have the same data type (or have a function in the set relation expression to convert the parent field to match the child) for the "set relation to" to be successful. Otherwise, I think that you're very close.
Good luck!
CalScot
Re: HMG Report from 2 Tables
Clarification of my earlier post:
You could use:
Also, you must have each table in a separate work area, otherwise the second one loaded just replaces the first one loaded in the current work area. That's probably why you're getting the error you mentioned!
Check into DBUSEAREA() for the full syntax, parameters, etc. (As with the set relation text provided earlier, the Clipper 5.3 reference in the HMG help is a good place to look/start.)
Regards,
CalScot
You could use:
Code: Select all
SET RELATION TO Alltrim(Str(MemberMTypeID,4,0)) INTO MembershipTypes
Check into DBUSEAREA() for the full syntax, parameters, etc. (As with the set relation text provided earlier, the Clipper 5.3 reference in the HMG help is a good place to look/start.)
Regards,
CalScot
Re: HMG Report from 2 Tables
Thanks for that - I'm out tonight but will work on it tomorrow. One area I seem to be having difficulty with is how do you create separate work areas?
Thanks Again,
Roger L
Thanks Again,
Roger L
Re: HMG Report from 2 Tables
Hi, Roger.
You don't so much "create" separate work areas as you "select" ones that are already there, available to use!
DBUSEAREA() has a built in work-area selector, even automatically selecting the next available unused area without you having to specify it. See the previously referred to help-text for the syntax.
The other (outdated) way, that I often still use (I can see all the purists frowning at this right now, but I think it helps to explain it ) is to just select the work area, as shown below.
First, some history: In the beginning (the 80's!), dBaseIII allowed maybe a dozen work areas - A-K, I think. Then Clipper increased the maximum and went to numbers, zero to whatever, as an optional alternative. So the format below could be used with letters or numbers - but it would definitely be best to use DBUSEAREA() for forward compatibility!
Select A
Use This Index This
Select B
Use That
Index on Field3 To That
Select A
Set Relation to Field1 into That
Go Top
@ nRow, nCol Say Field1
@ nRow+1, nCol Say That->Field3
etc.
So, if you're in the current work area, you can just state the field name to access its data. If in a different work area, just prefix the field name with the alias (automatically assigned as the dbf name in the absence of a statement assigning a different alias) to access its data.
Think of it all as a bit like the difference between dialling a local telephone number, or having to put in the area code first if the number is in a different area!
Hope that helps!
CalScot
You don't so much "create" separate work areas as you "select" ones that are already there, available to use!
DBUSEAREA() has a built in work-area selector, even automatically selecting the next available unused area without you having to specify it. See the previously referred to help-text for the syntax.
The other (outdated) way, that I often still use (I can see all the purists frowning at this right now, but I think it helps to explain it ) is to just select the work area, as shown below.
First, some history: In the beginning (the 80's!), dBaseIII allowed maybe a dozen work areas - A-K, I think. Then Clipper increased the maximum and went to numbers, zero to whatever, as an optional alternative. So the format below could be used with letters or numbers - but it would definitely be best to use DBUSEAREA() for forward compatibility!
Select A
Use This Index This
Select B
Use That
Index on Field3 To That
Select A
Set Relation to Field1 into That
Go Top
@ nRow, nCol Say Field1
@ nRow+1, nCol Say That->Field3
etc.
So, if you're in the current work area, you can just state the field name to access its data. If in a different work area, just prefix the field name with the alias (automatically assigned as the dbf name in the absence of a statement assigning a different alias) to access its data.
Think of it all as a bit like the difference between dialling a local telephone number, or having to put in the area code first if the number is in a different area!
Hope that helps!
CalScot
Re: HMG Report from 2 Tables
Thankyou CalScot - that is very clear and I think I now get the concepts! I'll play around with that later today and let you know how I go. Your assistance much appreciated!
Roger L
Roger L