Report contents page based on sub-summary

[eddyb2]

Hi all,

Wondered if this was possible.

I have a report layout that includes a sub-summary part.

Within the subsummary is 1 field called "Sector"

the body contains details for the records

Each record in the database is assigned 1 of 15 sectors.

In the sub-summary part definition I have it set to...

Sub-summary when sorted by "Sector"

And Page break before each occurrence

To run the report the script shows all records, sorts by sector, then goes to the report layout and print.

All works perfect. Each page fits 10 records on it.

Sometimes a sector has less than 10 records. So this page will just print say 3 records then starts a new page for the next sector.

For those sectors that have more than 10 records, it continues onto the next page and the next etc until it reaches the end of the records for that sector and then starts a new page for the next sector - just as I want it to.

So the report itself works fine.

What I would like to know is as each sector is set to start a new page, would it be possible to automate a contents page

So...

Sector 1..........Pg3

Sector 2..........Pg6

Sector 3..........Pg7

Sector 4..........Pg8

and so on...

Does FileMaker know which page the new sectors are starting on, and if so can I recall this information to place on a contents page?

The contents page would be part of the title header - I presume that is the best way to do this?

Many thanks

Ed

[dansmith65]

Does FileMaker know which page the new sectors are starting on

No. But you can find this information out by using scripts. For example, to display "page 1/5" on a report, you need to use the "go to last record script", then "Get(PageNumber)" function to determine that there are a total of 5 pages in the report.

would it be possible to automate a contents page

I personally hate to say that something is impossible. Although I can say that I don't know of any simple way to do this. If you can do it - it's going to take some work. Here are my ideas...

I believe the Table Of Contents would have to be on a separate layout - I don't know of any way to put it at the top of the current layout. The TOC layout would not have a "Body" part - this would have it display a listing of the sectors as you need it. The trick is in finding out and displaying the page numbers.

I think you would have to print your report, then go to the first record, loop through the records looking for the sector field to change, when the sector field changes you can use the Get(PageNumber) function to determine what page it is on. Then you have to figure out how to display that information on the report. I use a global field named pageNumber to display the total pages in a report. For this, however, you cannot use a global field, because the same field has to display a different piece of data (the page that the selector starts on) for each record.

I think you could create a new table with a field name pageNumber. When the script is on the record of the first found "new" selector, it could create a record in this related table and store the page number. Then put that related record next to the selector field in the sub summary part on the TOC layout, and presto! you have a Table of Contents!

Well, if you can decipher my rant above and you're willing to go through all that trouble, then I think it may work.

Good Luck,

- Dan

[comment]

I believe the TOC could be placed in a Title Header part of the same layout. And since the number of of records per page is constant, generating the TOC shouldn't be very difficult: for each sector, the number of pages is given by:

Ceiling ( GetSummary ( sCount ; Sector ) / 10 )

and the start page is the previously accumulated number of pages + 1.

For a (much) more elaborate method that works with variable number of records per page, see:

http://fmforums.com/forum/showtopic.php?tid/144352/

[eddyb2]

Thanks comment, what would sCount be?

[comment]

A summary field defined as Count of [any field that cannot be empty, e.g. the serial ID].

[eddyb2]

Thanks comment but I must be doing something wrong.

The sCount is working fine, returns the figure of 200 (the number of test records in my database)

The GetSummary field always appears blank though. I stripped down the calc just to test it without the ceiling() function.

So I have...

sCount = Count of ID

Then i set up the formula...

GetSummary (ID; Sector)

I sort by Sector but the field remains blank (although sCoutn is showing 200)

I made sure the getsummary field was in the same table as the breakfield as it says in the FM help pages, but dont seem to be able to get this working. Any ideas where I may be going wrong.

One other thing, my Title Header is actually my front page of the report. I tried expanding this onto 2 pages to allow for a contents page, but the 2nd page does not show. is title header restricted to 1 page?

I think I may have to look at a seperate layout. I use the PDF function in FM to make the report into a PDF, i know there is an append to existing file option with the PDF function so maybe I need 2 jobs here, 1 for the report, 1 for the contents page and append the contents page into the previously created PDF?

[comment]

GetSummary() will return a result only when records are sorted by breakField. Note also that defining a calculation field will not really help in producing the TOC*; you need a script to loop through the sectors and populate a global field with the individual count of each sector.

Title Header is the header of the first page only. You could perhaps add a dummy sub-summary leading grand summary part at the top. Or, as you say, just generate the TOC and append the rest of the report to it (*in such case, you COULD use a summary-only layout and get the pages by calculation field instead of script).

Edited January 31, 2009 by Guest
Duh!

[dansmith65]

Thanks comment, that was way easier than the long drawn-out solution I came up with.

eddyb2, you get it working yet?