Finds for a report

[djc728]

Hello:

I have a main Table A with 2 one to many tables B and C. I want to create a report with base information from Table A and the child records from both Table B and Table C. What would be the best approach to accomplish this? I know I'll need 2 finds - one for each of child tables B and C. Can I assume that after performing these 2 separate finds that I will have 2 selections in memory? So if I put a 2 portals on the report layout -- one for Table B and one for Table C -- I think I can accomplish what I want. Does this sound like the right path?

Dom

[Vaughan]

There is no way to get related records from two different tables onto the same layout without using portals. Unfortunately portals aren't the best for printing.

One work-around is to print the results form each table on a separate page.

Another is to redesign the data structure and have records B and C in the same table -- this isn't always suitable.

[Fenton]

Portals will work if it all fits on 1 page, or if you're willing to dedicate a page to each portal. But portals "break" across page breaks, as in "lines chopped in half".

There are other possibilities, just not easy ones. There's Value List Items, or Copy All Records into a temporary field.* But my favorite for touchy situations like this is a Custom Function(s) GetRows, by Bruce Robertson (it's actually 2 CFs); available at:

http://www.briandunning.com/filemaker-custom-functions/list.php

It can bring what are normally fields in a portal into a calculation field in the parent table. Then you can put it (them) on the layout as a Merge type field, and it will break properly over page breaks; use Tabs to separate fields.

*ValueListItems requires a concatenated field in the child table; bloats the file; always sorts alphabetically.

*CopyAllRecords requires a dedicated layout; bloats the parent table, where the data is pasted; requires a script.