Report with records from different tables (1 page)

[Wimek]

I've got the following question:

When having a database "treatment" with two related tables "materials" and "work", where a treatment consists of used materials per m2 and work to be done per m2. The objective is to present an overview of the materials used and the price per m2 and secondly, a simular overview of the work to be done with a total of time needed per m2.

Currently I enter the data in a layout with two portals: one for materials, one for work and the totals are presented bij summary fields. My current report consists of two layouts: again one front page containing general treatment information and the the materials used in the body while a summary calculates the totals for the materials. However I do need a second layout to print the same kind of list for the work to be done... This implies that I cannot print the complete report (treatment information, materials and work) on one page. This is an issue because mostly such a complete report fits on one page. This is about how it looks like:

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

Treatment_name

discription of this treatment

material A - price: $2.50 - needed: 1.5 units - sub-total: $3.30

material B - price: $1.00 - needed: 0.5 units - sub-total: $0.50

material C - price: $2.00 - needed: 1.0 units - sub-total: $2.00

total material price: $5.80

work A - minutes needed: 3.5

work B - minutes needed: 5.0

total minutes needed: $8.5

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

I cannot find a way to do this. In a screen layout it is possible to do it with separate portals, but since it is not possible to scroll on the printed paper, this is not an option. On the other hand there can be only one body in a layout... I'm afraid it is not possible to accomplish this... unless I didn't set up my databases in a smart way...

I hope any of you has ideas to solve this!

Thanks and regards,

Wim

[RalphL]

In a similiar database I ended up with one table for both Material & Labor. A field was provided to show which it was. I sorted on this field to separate the 2 in the report. I overlaid fields like unit price and labor hours in the report. I made a text field which held the titles in the leading sub-summary for each type.

I used a title header to hold logo and Store name, address, etc. and a title footer to show the total price, taxes,etc.

[Wimek]

Thanks for the reaction Ralph,

I agree that such a construction would solve the problem. However, I don't find the solution very elegant; I hoped that I overlooked a feature of FM7... now I'm thinking whether to leave it this way or go the way you sugested...

Best regards,

Wim

[RalphL]

The method I suggested was pre 7, but I have not been able to come up with 7 method that would allow 2 related tables.