I am new to FileMaker but am familiar with database structures (I am a Crystal Reports consultant).

I have what seems to be a simple need: I want to be able to show two levels of one-to-many relationships on the same screen.

I have a database for interior decorators. I have a Job table, a Room table and a Room Detail table. I want to create a Job screen that will show multiple rooms with multiple detail items for each room.

At first I thought you could put a portal within a portal, but that doesn't work. Is there a way to group the top level and then insert a portal? I'm used to grouping using Crystal Reports but I can't seem to be able to figure it out in FileMaker.

Thanks.

If you just need to generate a report showing information grouped from three tables, you can just construct a subsummary report from the "many" (Room Detail) table, and sort (group) the records first by Job, then by Room, and then by Room Detail (or whatever you want the room detail records sorted by) If you only want a specific Job or Room, just do a find (query) of the Room detail table for records belonging to that Job. You can search related fields just by placing them on the layout and allowing entry in find mode.

With subsummary reports you can only view them in preview mode, so you can't have portal scrollbars or buttons like you can in browse mode. (It's really meant for printed reports)

Alternatively, you could just have a portal in the Job Table that shows the Rooms, and place a Go to related record script step button on the portal. This script could open a popup window that will display the Room record along with the second portal for room detail. Not a portal within a portal, but it works well for browsing rooms and details.

Dana

Hi BigMike,

I'm unsure if this would fit your needs because you didn't describe enough for me to get the complete visual picture. But I've attached a demo file which shows one way the information might be displayed for you.

The room details value list could be concatenated from your detail fields any way you wish and could contain statistics about the rooms instead. I just couldn't get a visual on how you tracked the data.

LaRetta

job.zip

Hi,

There are neat techniques of hierarchies with FM7, but I'm not sure it can apply in your case.

While it breaks the common rules, and would rely on a scripted approach (not implemented here) so that you make sure there's one and only one instance of each, you may also introduce your Rooms into the Many side this way...

Sorry, not very explanative.

ParentAndChild.fp7.zip