Totally lost regarding subsummaries and reports

[jmobley]

Please excuse if this is too simplistic of a question...

I've been building a project/task tracking db and I've just now tried to create some reports. Ulitmately, I want to see all my projects and their associated tasks in a printable report.

I've followed the instructions in the FM help and in the "Sams Teach Yourself FM 7" book, but I'm having a heck of a time getting subsummaries to do what I think they should do. Even using the sample files from the "Teach Yourself..." book -- I keep having the same problem.

Take Parties and Guest for example. One party can have multiple guests. There's a parties table with a field called "Event" and a Guests table w/ a field called "LastName". When I try to create a simple report w/ a subsummary part that sorts info based on Parties, and LastName is in the body, I get a report that only lists the first guest, but no others.

The layout is based on records from the party table. The first break field is based on Events from the party table, then the body only has one field "LastName" (from guests table).

Can anyone explain why this report only lists the first guest, but omits all the rest?

Interestingly enough, I've even been able to show the reverse of this behavior. I switched the report so that it's based on a Guests table. The I sort on Events from Parties table in the subsummary. In this scenario, I get the first party with all the guests, but no other parties.

So it seems that when running reports I can either show one party with all its guests, or all the parties, with one guest at each. (Again, the party/guest thing is just an example of behavior I seem to be repeating in other db files.)

I'll happily answer questions if any of this is hard to follow.

Please help. I've got to be able to provide project/task reports to a new client and I've been stumped on this for DAYS. Now I'm frustrated and demoralized.... **sigh**

thx,

Janet

[Fenton]

You need a line items table, with an entry for every unique combo of Party-Guest. So, if 30 people came to a particular party, there would be 30 records in that table.

The only fields needed would be the PartyID and the GuestID. All other visible data could be referenced via relationships on the 2 IDs. This is assuming you have a Guest table, with an ID for each person; that a person can attend more than one party. Otherwise you don't need a guest table, just put the people straight in this "attendance" line items table. But that's not a very intelligent structure, if you have any other data about the person.

You would not do the report in the Party table, because that's not where the "many" records are. Especially if you're wanting to show individuals in the Body, which occurs once per record in the found set.

[jmobley]

Fenton,

Thanks so much for your speedy reply. I got the "parties & guests" example to work. Then I moved on to trying to do what I really wanted to do in the first place.

I have a table called "Projects" and I have several instances of the same table that allow me to create infinite sub-projects. Each "sub" inherits the ID of it's parent in a field called "parent ID". Then each instance in a series all share the same "root" id -- which is the id of the very first project in the series.

I'm actually not experienced enough to have created this myself. I'm using a file called "infinite hierarchies" from Matt Petrosky (sp?) from his filemakermagazine.com site.

Everything works great in browse mode and I've been happily creating all sorts of subtasks within my projects. The catch is that you can only look at one project's (or sub-project's) set of associated info at a time.

Now I need a report that shows this hierarchy which is all based on records from the same table.

I created a new set of table occurances trying to mimic the infinite hierarchies down 4 or 5 levels and then I based a few reports on those tables with somewhat satisfactory results. I'm getting project names and their sub tasks, but the reports don't behave exactly right.

If you're really curious (and incredibly generous w/ your time) you can take a look at the mess I've made in the attachments.

The main file is FatCat.v4 Report Testing.fp7. It's linked to Infinite Hierarchies.fp7. The report in question is "multi-level sort test report"

Be kind, all this is a work in progress by an incredibly inexperienced newbie.

FatCat.v4Reporttesting.fp7.zip