Want to make a sorted sub-summary part behave like a header so it repeats on each page

New to FMPro. I am creating a contact database for our high school football team. There is a field for grade (values of 09, 10, 11, 12). I want to sort the players by grade in the report so I used a sub-summary part and I included a page break before each occurrence. But I also want the grade to appear above each page like a header. It appears I don't have that option in the Part Definition box.

Ultimately I would like to substitute a text string for the field value as the header. For example, I'd like to have "Sophomores" as the page header instead 10. (How to do that is part 2 of my question.)

Thank you.
jaoharaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresidentCommented:
With regard to the top of each page showing the heading you want, simply get rid of the Header Part (or reduce it in size) and use the Sub-Summary part as the Header.

For the text string, create a calculation field using the Case() function like this:

Case( Grade = 9 ; "Freshman" ; Grade = 10 ; "Sophomore" ; Grade = 10 ; "Junior" ; Grade = 12 ; "Senior" ). You can still Sort by Grade - making sure that your sub-summary part using the Grade as the Sort order, but put the Text String Calculation into the Sub-summary part instead of the Grade field.

The sub-summary part will always display the value of the first record that comes after it so if the records are sorted by Grade, then the Sub-summary part should list the correct text string above each group of records.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jaoharaAuthor Commented:
Thanks willmcn. I am making some progress. I created the calculation field the way you said and it works with one caveat (below).

Regarding the heading on each page, I've tried using a Header part with the calculation field and it's correct but it doesn't allow me to insert a page break before each occurrence. So I end up with a mixed page of the end-of-the-alphabet seniors and the beginning-of-the-alphabet juniors with a heading of Seniors on that page. If I define the part as a Sub-summary, I get the page break that I want but it's not treated like a header repeating the calculation field each page. It seems I have to choose one or the other.

The caveat with the calculation field is that it won't allow me to input Case( Grade = 09. It always changes it to 9. Weird. In the grade field, I used 09 so that the sorting would be correct.

Any advise? And thanks so much!
0
Will LovingPresidentCommented:
If the Grade field is a number field, which it should be unless you specifically need it to include the leading zero or it includes non-numeric characters like "K" for Kindergarten, it should sort properly. A text field will sort alphabetically, a number field will sort numerically regardless of leading zeros. If you need it to be a text field and use 09, then put the Grades in the Case statement in quotes. If it's a number field you don't need to use the quotes (or leading zeros).

You can use a Sub-summary part as a header and it will repeat at the top of each page if you have the "Page Break Before" option check, the records sorted properly (and again, I recommend using Grade as a number field unless you need to do otherwise), and the sub-summary part is setup BEFORE the Body part. You won't see it in Browse Mode, but when you go to Preview, assuming the records are sorted properly, it will be there at the top of each page.
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

jaoharaAuthor Commented:
OK, I changed the Grade field to a number type and that fixed the problem. (Boy, what a rookie mistake!) Thanks!

But I'm still having trouble with the heading. Let me back up... I am using FileMaker Pro 12.0v4. I am making changes to the report in Edit Layout mode, saving, and then previewing them in Preview mode so I do see what the page results will look like. I have a part defined at the top of the page as a Sub-summary. When I right click on the tab the Part Definition box comes up. If I select Sub-summary when sorted by, I choose to sort by Grade and choose the Page break before each occurrence option. Alternatively, if I choose the Header as the definition, the sorting option and page break options go gray and are not available. Am I missing some other way to combine sorting and making a header? It's not that big of a deal really but now it's a nagging little bugger...
0
jaoharaAuthor Commented:
By the way, I just joined up with Experts Exchange today, this is my first question, and I LOVE how quickly you answered!
0
Will LovingPresidentCommented:
Normally responses take up to 72 hours though they often get a response within 24 hours. I just happen to be around today and have a little time to answer questions  :-)

A header is simply an area that appears at the top of every page; it can contain fields but does not summarize. A Title Header appears at the top of only the first page (and you can use both Header and Title Header if you want). Headers do not have sort or sub-summary options as you've seen.

In this case, you're simply using the Sub-Summary part to function as a Header for the layout. It should be the first "Part" on your layout, and since it is set to Page Break Before, it should appear at the top of every page when you are in Preview Mode IF and only if the records are sorted properly by the field selected in the Part Definition dialog.

If you still don't get it, upload a sample file and I'll take a look.
0
jaoharaAuthor Commented:
Here is my file. I started with an education starter solution. My two layouts are the form layout and directory layout. Directory layout is the one I am asking about.
Thanks.
DB-Test---Student-Record-copy.fmp12
0
Will LovingPresidentCommented:
It's working fine for me. In Preview it shows the Sub-Summary part at the top of each Grade grouping. You can also see it in Browse Mode if you change from form view to List View using the middle of the three "View As:" buttons. (though in Browse you won't see the page breaks).

Sub-summary part at top of grouping in Preview
Sub-summary in Browse Mode
0
jaoharaAuthor Commented:
Actually I wanted the header to appear at the top of EVERY page, even WITHIN the grouping. I'm making a little booklet that will be a half-page width (4.25x11) for the parents and it's easier to know who you're looking at when the header appears at the top of EVERY page, rather than just at the beginning of the new grouping. For example, some pages will be all sophomores on both the left and right sides and you won't know they are sophomores unless you go to a previous page to see the header at the beginning of the group. But again, not a big deal. You've taken a lot of time for a small problem. I appreciate it.
0
Will LovingPresidentCommented:
All you have to do is add a Header part above the Sub-Summary Part. It will repeat the Grade but it will get what you want.
DB-Test---Student-Record-copy.fmp12
0
jaoharaAuthor Commented:
Brilliant! I used your idea and changed it up a bit. I didn't know I could have a "blank" sub-summary part while still being used to sort groups. Here is my final layout.
Thanks a bunch! I will close the question.
Directory-Page.tiff
0
Will LovingPresidentCommented:
You're welcome. Some layouts will have a tiny sub-summary part with NO fields in it. It's just there to cause a page break at each sort group.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.