AnsweredAssumed Answered

Group Totals/Breaks

Question asked by cpking on Jan 24, 2019
Latest reply on Jan 24, 2019 by beverly

Like • Show 0 Likes0
Comment • 6

As an old MS Access person, I sometimes find what should be the easiest thing to do in Filemaker the most arcane. So with apologies, I need help with grouping...

I have a few tables with relationships for Keys to our buildings. In my report layout, I have leading sub-summary for BUILDING, and a second sub-summary for KEYSTAMP. The BODY has relevant fields (i.e., Rec_ID, Keystamp, Person's Name, Date Issued, etc.).

Following BODY, I have a sub-summary for KEYSTAMP and another sub-summary for BUILDING. In each of those parts, I have a summary field in each of these following sub-summaries which are defined in the table as count.

My goal is to have a report print as follows:

BUILDING 1

KEY1

NAME DATE ISSUED

Total KEY1 XX

KEY2

NAME DATE ISSUED

Total KEY2 XX

Total BUILDING 1 XX

BUILDING 2

KEY1

NAME DATE ISSUED

Total KEY1 XX

KEY2

NAME DATE ISSUED

Total KEY2 XX

Total BUILDING 2 XX

No one else has this question

Outcomes

Jason Wood Jan 24, 2019 7:03 AM

Did you SORT by the fields on which the sub-summaries are based?

1 person found this helpful

Actions

cpking @ Jason Wood on Jan 24, 2019 7:15 AM

yes. I've attached a screencap of what I'm getting. The total count by BUILDING is displayed. But the count by KEYSTAMP is not.

Actions

badmonkey842 @ Jason Wood on Jan 24, 2019 7:16 AM

In general and depending on your structure you may have to work backwards in the relationship,

e.x. layout is based on the child record Table (table with the "NAME & Date Issued"), and the grouped fields based on the parent/grandparent tables(keys & buildings)

***nevermind we posted at the same time and didnt see you screenshot

1 person found this helpful

Actions

beverly

Jan 24, 2019 7:22 AM

The layout would be set up like this:

Header (if any)
Sub-summary PART (sort by BUILDING)
Sub-summary PART (sort by KEYSTAMP)
BODY PART
Sub-summary PART (sort by KEYSTAMP) - place the summary field here
Sub-summary PART (sort by BUILDING) - same summary field!
Footer (if any)

Sorting by:

BUILDING
KEYSTAMP

will give you a different report, then sorting by:

KEYSTAMP
BUILDING

Try it and see.

Beverly

p.s. what are you summarizing? do you need a COUNT (how many) or TOTAL (sum of a number)?

1 person found this helpful

Actions

cpking @ beverly on Jan 24, 2019 10:09 AM

Thanks, Beverly. That's the structure of the layout I'm using. By sorting Keystamp and Building, the report is broken up as shown below - not quite what I wanted, but closer.... I don't especially need or want the building name repeated again - just the rooms under it with totals by room and building...

In terms of counts, I want a total under each Key stamp, and then a total under each building. So, Building1 Room1=2, B1R2=3. B1R3=5. Total for Building1 would be 10. Make sense?

Actions

beverly

@ cpking on Jan 24, 2019 10:56 AM

Try the sort by BUILDING then KEYSTAMP! (show your sort dialog to verify?) And check the Sub-summaries are exactly as I specified (that order, and the correct sort fields). Your SUMMARY field should be COUNT (not total), because you are counting how many in each.

If you wish, put the summary field in the first Sub-summary part next to the Building name. You'll see that it's correct, if you sort the way they should be sorted.

Beverly

Actions

6 Replies

Jason Wood Jan 24, 2019 7:03 AM
Mark Correct
Correct Answer
Did you SORT by the fields on which the sub-summaries are based?
1 person found this helpful
Like • Show 0 Likes0
Actions
cpking @ Jason Wood on Jan 24, 2019 7:15 AM
Mark Correct
Correct Answer
yes. I've attached a screencap of what I'm getting. The total count by BUILDING is displayed. But the count by KEYSTAMP is not.
Like • Show 0 Likes0
Actions
badmonkey842 @ Jason Wood on Jan 24, 2019 7:16 AM
Mark Correct
Correct Answer
In general and depending on your structure you may have to work backwards in the relationship,
e.x. layout is based on the child record Table (table with the "NAME & Date Issued"), and the grouped fields based on the parent/grandparent tables(keys & buildings)

***nevermind we posted at the same time and didnt see you screenshot
1 person found this helpful
Like • Show 0 Likes0
Actions
beverly Jan 24, 2019 7:22 AM
Mark Correct
Correct Answer
The layout would be set up like this:
Header (if any)
Sub-summary PART (sort by BUILDING)
Sub-summary PART (sort by KEYSTAMP)
BODY PART
Sub-summary PART (sort by KEYSTAMP) - place the summary field here
Sub-summary PART (sort by BUILDING) - same summary field!
Footer (if any)
Sorting by:
- BUILDING
- KEYSTAMP
will give you a different report, then sorting by:
- KEYSTAMP
- BUILDING
Try it and see.
Beverly
p.s. what are you summarizing? do you need a COUNT (how many) or TOTAL (sum of a number)?
1 person found this helpful
Like • Show 0 Likes0
Actions
cpking @ beverly on Jan 24, 2019 10:09 AM
Mark Correct
Correct Answer
Thanks, Beverly. That's the structure of the layout I'm using. By sorting Keystamp and Building, the report is broken up as shown below - not quite what I wanted, but closer.... I don't especially need or want the building name repeated again - just the rooms under it with totals by room and building...

In terms of counts, I want a total under each Key stamp, and then a total under each building. So, Building1 Room1=2, B1R2=3. B1R3=5. Total for Building1 would be 10. Make sense?
Like • Show 0 Likes0
Actions
beverly @ cpking on Jan 24, 2019 10:56 AM
Mark Correct
Correct Answer
Try the sort by BUILDING then KEYSTAMP! (show your sort dialog to verify?) And check the Sub-summaries are exactly as I specified (that order, and the correct sort fields). Your SUMMARY field should be COUNT (not total), because you are counting how many in each.
If you wish, put the summary field in the first Sub-summary part next to the Building name. You'll see that it's correct, if you sort the way they should be sorted.
Beverly
Like • Show 0 Likes0
Actions

Group Totals/Breaks

Outcomes

Related Content

Recommended Content