Two dbfs in a single report? 2

[mpgalvin]

Let's say I have 2 tables, both of which are child tables of a master database.

For my example (a hospital), one table holds all the medication required by a patient (patient details being in the master) [1 to many] and the second holds all procedures they've had [1 to many]

Is it possible to write a report that would do the following:

Patient Name
------------

Medications

Med1
Med2
Med3, etc

Procedures

Proc1
Proc2
Proc3, etc

So, in fact, I think I need 2 detail lines, or some funky grouping...

 

[Nro]

Reporting in Fox is almost as bad as my Pinto. You can try this ;

SELECT Patient.Name, ;
Medication.med AS Det1, ;
"MED" as cType ;
FROM ...
INTO TABLE tbl1
USE

SELECT Patient.Name, ;
Procedures.Proc AS Det1, ;
"PRO" as cType ;
FROM ...
INTO TABLE tbl2

APPEND FROM tbl1
INDEX ON NAME + cType TAG PIPO

Create the report with 2 groups (based on name and cType).
It's not possible to have 2 detail lines in a report, but this trick will simulate it. It's really ugly but I always
do it (if I really have to use VFP Report writer)

Ciao.
Nro

 

[csvideo]

Assuming you have a common field in both tables(something like patient number - Pat_No). Then you can use a SQL -SELECT statment to retrieve the data from the two table base on the common field. Something like:

****************
select a.fieldname,b.fieldname from medicationtable a ,proceduretable b where a.Pat_No == b.Pat_No into cursor thecursorname
************
Then use the cursor as the data source for your report.

Just a thought

 

[mpgalvin]

chrisman, not sure how useful the result dbf would be. If a patient has 20 medications, and 1 procedure, I'll get 20 recordsl

Med1 Proc1
Med2 Proc1
Med3 Proc1, etc

Doesn't really help me in my report.

 

[ramani]

HI
I suggest the following..
SELECT procTable && procedure table
SCATTER MEMVAR BLANK
SELECT medTable && MEDICINE TABLE
SCATTER MEMVAR BLANK

SELECT patTable.pName, MedTable.*,, ;
m.procTableField1, m.procTableField2... ;
FROM patTable, medTable ;
INTO CURSOR myCursor WHERE patTable.Id=medTable.Id ;
UNION ALL SELECT patTable.pName, ;
m.MedTableField1, m.medTableField2...
proc.* FROM patTable, procTable ;
WHERE patTable.Id = procTable.ID ;
ORDER by patTable.Name

The idea of m.medTableField1 m.procTableFIeld1 etc.. is to have blank fields of same size and length into the common cursor.

Now in the report form,
1.goup the records by pName
2. Have two distinct report lines...
one line contains medicine records
second line contains procedure records
3. All these report fields shall be ALLTRIM(myField) or PRINT WHEN filled as NOT EMPTY(myField)
4. REMOVE BLANK LINES SHALL BE CHOSEN.

This will print if the cursor rows of either medicine or procedure.

If you need procedure and then medicine... have a field in the SQL cursor and fill it up as "1" for proceure and "2" for medicine... and in the ORDER by include this field also.

Hope I have explained sufficiently.
ramani :-9
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[ramani]

HI
I have just cut and paste from my earlier posting.. dont remember the link..

You can ceate a combined cursor from child 1 and child 2 and order them according to parentId and as required. Here is an example...

SELECT child1
LOCATE
SCATTER MEMVAR BLANK

SELECT child2
LOCATE
SCATTER MEMVAR BLANK

MyFile = SYS(3)

SELECT "1" as ChildNo, ParentFieldId, ;
child1.*, ;
m.cfild2Field1 as Child2Field1, ;
m.cfild2Field2 as Child2Field2, ;
&& fields of Child2 from MEMVAR BLANK
FROM child1 WHERE conditions ;
UNION ALL SELECT "2" as ChildNO, ParentFIeldId, ;
m.cfild1Field1 as Child1Field1, ;
m.cfild1Field2 as Child1Field2, ;
&& fields of Child1 from MEMVAR BLANK
child2.* FROM child2 WHERE conditions ;
ORDER BY ParentFieldId, ChildNo, etc.. order conditions ;
INTO DBF (MyFile)

Now that you have the cursor of combined Child1 & child2, create a report form with Parent fields in the top and child1 fields in the details. You can expand the 'detail lines' of the report form to have additional line to hold child2 fields as well. Remember to report each field with WHEN condition of ChildNo="1" or "2" depending on the field being from Child2 or Child1. So the field informations will amount to BLANK when CHild1 or Child2 is empty as selected in the SQL. The detailed lines with BLANK can be dropped by choice in the report Form. As a result you will get only either Child1 or Child2 record in the detail band.

This works and I have created reports of this type.

Hope I have explained in an understanding way. If you need more details, you can ask.

ramani :-9
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[mpgalvin]

Thanks, ramani, great code. Saved me a whole evening of hassle. Just in case others stumble across this code, a couple of things:

The "order by" in union Selects has to be field numbers, rather than names

The resulting fields of the 2 SQL statements have to be identical, so if you SELECT table1.* in the first part, you'll have to list each of the fields out in the second part. Unless there's an m.* statement? Probably not

The resulting report will have some rather large gaps between each detail line, especially as the ChildNo goes from 1 to 2. I got around this by putting IIFs in all the field expressions and printing either the med details or the proc details. May not work for you.