Help please - subsummary problem; related records

[ctz243]

Hi again!

I'm trying hard to get a report to work - but I can't figure out a simple (?) counting problem using subsummary fields. It's hard to explain here but the example file illustrates my problem and I hope someone could look at it and if possible tell me how to make it work!

I'll explain the attached file. It opens previewing a report that has an incorrect count for the number of people insured by a particular insurance company. All other counts are correct. It's that one incorrect count I'm trying to fix. Here are the details

There are two tables related by OwnerID; one owner can own multiple cars:

Owners: OwnerName, Insurer and OwnerID

Cars: CarName, CarValue, CarID and OwnerID

What I want to know in the report how many Owners are covered by a particualr Insurer, while at the same time providing detail lines of each car they own. I am trying to avoid using a portal so I can better implement page breaking. So I've built a report based on the Cars, sorted first by Owners::Insurer and then by OwnerID, with leading subsummaries (in the same order) above the Body part. It all looks exactly correct; the calculations for the total value and number of cars in each section are correct.

But I can't figure how to count the number of people insured by each insurer. The report breaks properly by insurer, but a summary count on OwnerID always returns '1' no matter where I put it or what I do.

This is such a basic thing - yet I cannot for the life of me make it work. Is there a trick to doing this?

Any takers? My hat off to anyone who can fix it.

Thanks immensely!

chris.

SubSummary.zip

[The Shadow]

The problem is the summary (Count of OwnerId) as seen by the "Cars" table will be everyone who has the same car. What you want is everyone who has the same Insurer.

To get this, I added an extra occurrence of "Owners" to the graph and called in "Same Insurers". Looking at that subset from Cars gives the correct count.

SubSummary.zip

[ctz243]

Whoa! That was quick!

What a great solution! But - and I feel so dumb asking - htf does it work???

Shadow, if it's possible... how does adding that relation do it's magic? I've wasted hours and hours trying to figure out how to do this...

Also will it work, in principle, further up a relationship tree?

Kudos, kudos! :-)

[ctz243]

Bad News!

Shadow, your solution fails if I do a find; the count of Owners always is the TOTAL count of owners, not the count of the found set of owners.

Try doing a Find in the report, set the name to >K, then go back to Preview and then re-sort. Notice that the total number of owners shows all of them (3 for that insurer) but there are only actually 2.

Is it possible to refine your concept so that it will yield the correct answer after a find?

Thanks for further advice / ideas on this.

Chris.

[ctz243]

Bad News!

Shadow, your solution always shows the TOTAL count of owners for each insurer, not the count of the found set of owners.

If, in the report, I find by name >K, go back to Preview and re-sort, the number of owners still shows all three of them - even though only two are in the list!

Is it possible to refine your concept so that it will yield the correct answer after a find?

Thanks for further advice / ideas on this.

Chris.

[The Shadow]

Yeah, I see - nothing is limiting that join to the set of owners we're looking at. Bummer.

I played around with this for a while, and couldn't come up with an elegant way - oh well, time to pull out the chainsaw... I placed a global in the owners table to hold the subset of owners mentioned in the cars found set, then added that to the Same-Insurers join to limit those counts. I added to your "Startup" script steps to recompute that global value based on the current found set, so you would need those steps to be run whenever you want to look at the report.

With the multiple-table away joins in FM7: the 1-away join is the related set, just like it would have been in FM6. The 2-table away join is the combined result of joining *all* of the 1-away related rows. This lets you do some neat things like dropping to a unique set with another self-join, or, as in this case, an expanded version.

I generally find it easier to reason about it when I can see the joins, so I'll create a layout with portals to several tables and play around with it to help me quickly see whats going on, and tweak the relationships until they're just right.

SubSummary.zip

[ctz243]

Hi Shadow....

I figured a way of counting the related 'owner' (parent) fields from the found set ... not elegant though!

What I did was add an extra 'counter' field in the 'cars' (child) table that can be set to either 0 or 1 by a script. When invoked, the script does a search, sorts all found records by owner, then loops through all found records. The first time a particular ownerID is encountered, a global stores this, and each time in the loop it is compared to the ID of the next record. Whenever they don't match, the counter is set to 1, for all subsequent records for the same owner, they do match, and it is set to 0. The sum of this counter in the 'child' table in any found set equals the number of 'parent' records with the same ID.

Since in my 'real' database a typical search is likely to return only a relatively small number of records from a much larger set, this works out fine, and gives the desired result. I was going to have to use a script anyway, so the brief loop though the found set was not a problem.

To be honest, I was really surprised that it wasn't something kind of built in to FileMaker. It seems strange that there is no easy / built-in ability to count the 'parent' records related to a found set of 'child' records.

No-one else in this forum seems to know a simple solution either... if you do come up with something easy and neat, I'd be fascinated to hear how this is 'supposed' to be done in FileMaker. There must be an easier way than my solution!

thanks for your interest

Chris.