Displaying Records from multiple child tables -List?

[Wickerman]

THis is related to my previous post, but different.

I'm wanting to produce an "Activity Report" for a single record in a database. Let's say we're talking about Paintings. I have two child tables off of the Paintings Table -- one keeps track of Exhibition hisotry -- one child record for every time the painting is exhibited, with fields for Date and Location. The other keeps track of Conservation activities, with fields for Date, Type, Status and Note.

So on the data entry side, I have two portals on the data entry page so the user can enter these two kinds of activity. Dandy!

Now, say I want to produce a layout for printing up All the information about each painting and also a nice listing of, first, Exhibitions and second, Conservation.

I've found creating a calculation field on the Paintings table very useful for similar but less complicated single-field entries -- lists of keywords, for instance. But here I'm trying to present multi-field info in a pleasing layout and running into trouble.

1) One approach is to create a "Full_Entry" field in the child tables that concatenates the 2-4 fields in one, and then define fields in the Paintings table like List (Child::Full_Entry). THis works, but doesn't allow me to manipulate the text formatting / spacing as flexibly as I need to do.

2) Another approach seems to be to use a pair of portals and lay out the child fields within the portal as I like -- but is this a practical approach? Won't I have to make the portal window large enough to display the largest possible field entry -- and include enough rows to display the greatest possiblenumebr of entries (dozens?) - this would extend the layout to several meters, I think! And does "Sliding / Reduce size" even work with portals (as opposedto just the fields within them?

3) I don't think think a sub-summary reporting apprpoach works here -- because wouldn't they need to be from the context of the child tables -- both of them?

Any suggestions for my best general approach?

thanks

Albert

[Søren Dyhr]

I have two child tables off of the Paintings Table

Before I get further is there something here, table or table occurence? You are mixing terminology here!

--sd

[Wickerman]

I have three tables

PAINTINGS - 1 record per painting

EXHIBITION - 1 Record per showing

CONSERVATION - 1 Record per Action

Relationships:

Paintings to Exhibitions (1 to many)

Paintings to Conservation (1 to many)

So, one Paintings TO, with pk (unique) to fk (not unique) relationships to the two other TO's.

Yes?

Albert

[bcooney]

You've covered two approaches, but there's a third. Create the report using a new table and import all the records from each child table that you'll need into this "temp" report table. The temp table can be sub-summed.

[Wickerman]

Ahhhh. . . . I suspected there might be an approach I was unaware of.

I can imagine there all sorts of subtleties and clever techniques involved in doing this -- a bunch of scripting to stash the info in a bunch of global fields maybe, I'd guess? How would I handle these multiple values from the child tables?

Would there be a demo example of this somewhere, or a text that demonstrates this sort of thing? I've got the Filemaker Pro 9 Bible and the Training Series for FMP 10. I don't think I've seen this covered anywhere . . . I'm afraid I have no idea where to start.

:-/

albert

[comment]

See also:

http://fmforums.com/forum/showtopic.php?tid/191147/post/271539/#271539