Subsummary Totals

[tftedorr]

I am trying to create a trailing grand summary that reflects the number of summaries within a report. I have records that relate to a household database. The subsummary shows the number of records that relates to that household. However, I need to show how many households are summarized at the end of the report. I have tried to do this a number of ways with no success and suspect that it is easier than I am making it out to be. Any help would be SOOO greatly appreciated.

[Lee Smith]

Hi tftedorr,

FileMaker's Online Help can be very helpful. Open it up, and type "subsummary reports" in "Index", then choose "Report with grouped data and totals troubleshooting" and then double click on it or the "Display" button at the bottom. This will open up a second box that displays the different parts of a report and some the thing you should check.

HTH

Lee

BTW, you should update your profile to reflect what platform and operating system you are using. It can be very helpful to others when responding to your questions.

[tftedorr]

Thanks for your response. I have checked out the help, unfortunately it doesn't really address what I am trying to do. If using the examples from the Filemaker help I need to show the number of regions summarized by the report as a number in the Trailing summary.

I will update the profile, but I am working in Mac OSX panther and in FileMaker 6 if that is helpful. Thanks again for your help.

[MoonShadow]

It takes 3 seconds to update your profile. You could have easily done it when you posted. See right here to the left? Under your name where it says Profile? Just click that link and it will take your directly to your version information. Select your version, save and exit it. 3 seconds. And it would help everyone here trying to help you.

[tftedorr]

Ok, updated. thanks.

[QuinTech]

Does the subsummary display correctly? If so, just duplicate the fields in there to the trailing grand summary. Make sure you're putting a summary field into both parts.

Jerry

[tftedorr]

I don't think I have done a good job of describing my dilema. I am summarizing contact records that are related to a separate household database. [so there are single households with multiple contacts ] I have been able to successfully summarize the contact information by household. In the trailing grand summary I need to have the total number of related household records. I don't know how to get that number. All of my efforts end up with the total number of contacts not the total number of households. I can go through and count up the number of subsummaries but is there any way to make FileMaker do this?

[QuinTech]

tftedorr said:

I have been able to successfully summarize the contact information by household.

How did you do this?

[tftedorr]

I created a Sub-summary by Household# with the summary field "Total=count of contacts" which gives me a total number of contacts for that household. That same field in the Trailing grand Summary gives me the total number of contacts for all households. Just not sure how to create a count of households.

I think I might be making this harder than it has to be but I feel so stuck. Thanks for trying to help me.

[QuinTech]

OH! I missed that, i thought you were asking for the total of the same field. I should try to read more carefully. Well, there's a couple different methods to do this--probably the best is to make a new summary field that is the Count of HouseHoldID. Put this new field in your grand summary and i think you'll see the result you want.

J

[Oldfogey]

That'll still give you the total number of contacts, won't it?

I think you need a relationship back to the Households file, which you already have.

Then I think Count(Relship::HouseHoldID) will do the trick.

Alternatively, define remote IDCounter = Count(HouseHoldID) in the Households file

and then use Relship::IDCounter.

[tftedorr]

This worked: define remote IDCounter = Count(HouseHoldID) in the Households file

and then use Relship::IDCounter.

YES! Thank you so much. I have been trying to figure out a way to do this so long. Yea!

Sorry for the delay in response. I was away and just got back. Thanks again so much.

[tftedorr]

Well, I was a little hasty in my response, it does show the number of household records, but not necessarily the ones related to my found set in the other database. Do you know of a script to create a found set within the household database of just the related records? I know how to go to one related record but not to find multiple.

Thanks for any insights you may have. L

[Robert Kidd]

There may be a better way but:-

Define a Calc field with a result of 1, call it say RIsOne

Define a Summary Field as a count of RIsOne, call it say SummCount.

Define a Calc field which is "1/GetSummary ( SummCount; F1)"

Where F1 is your summary break field.

Define another summary field which is the summary of the new calc field.

This when placed in your trailing grand summary should give the answer you are looking for.

[tftedorr]

1/GetSummary ( SummCount; F1) - This did the trick. Thanks, I would not have figured it out without you. I have checked it multiple times and it works each time. You are amazing.

-tftedorr