page-breaks in reports

[macavity]

In a report with several sub-summary parts, page-breaks between parts can separate items that must be seen together. There is no obvious way to prohibit page-breaks between two parts or to force a page-break before a specific part should it occur at the bottom of a page.

Is there a work-around for this or has FileMaker actually created a function that is virtually useless for creating reports with an acceptable appearance?

[Chuck]

quote:

---

Originally posted by macavity:

In a report with several sub-summary parts, page-breaks between parts can separate items that must be seen together. There is no obvious way to prohibit page-breaks between two parts or to force a page-break before a specific part should it occur at the bottom of a page.

Is there a work-around for this or has FileMaker actually created a function that is virtually useless for creating reports with an acceptable appearance?

---

Check out the discussion in the FileMaker Pro 5 forum entitled "Enforce Page Breaks". I asked a similar question recently, and found that there really isn't a good workaround. But you might try having a part create a page break before every occurance. That wouldn't have worked for my solution, but perhaps it would for yours.

Chuck

[macavity]

Thanks for the quick response.

Actually I did see your post at the FileMaker forum - I was just hoping someone else might have thought something up.

Setting one of the parts to break at regular intervals wouldn't be of any help in my case either. But I have had another idea that seems to have potential - only I can't get it to work. Maybe someone else can:

It involves creating a new part above the part where you want to enforce a break when it occurs at the bottom of a page. This new part should be set to break at each occurence and should contain a field that is normally empty. Then loop through all the records of the report comparing the status current record number in the two parts you want to keep together, and also comparing the current page number in both parts. Whenever the record numbers are identical but the page numbers differ, then the field in the newly created part is set to a value.

Theoretically this should cause a page break then - and only then.

Unfortunately I can't get this to work, mainly because I can't get FileMaker to register what page each record is located on. I don't know if that's impossible or if I just don't know how to go about it.

Could this be a viable way to solve the problem?

[macavity]

I'm bringing this back to the surface, almost seven years later. And still no solution from FileMaker.

Has anyone found a work-around since?

Is there any conceivable way to make FileMaker aware of which record is on which page in preview mode? If so, it wouldn't be difficult to devise a script that creates page breaks as needed.

My client is ready to throw out FileMaker because it is unable to produce a decent looking multi-page report. He needs to create and send these to his clients regularly.

[Søren Dyhr]

Is there any conceivable way to make FileMaker aware of which record is on which page in preview mode?

If none of options the image above shows, really will fit the bill - Isn't it just to stripe (scripted replace in a additional field) your records with Div(Get(RecordNumber); x ) or different type of math, where x is the maximum allowed on one page, you could the make enough subsummaries to provide the required pagebreaks.

Think about it the striping could be a scripted loop, so the wheather or not a record contains a image, would some logic tell you that either 5 plain records in a page or 2 records with a Image or if one image then only 2 plain records. You would probably need to use GetNthRecord( to se what the previous was, if the issue is this crafty?

Have you really exhausted all these posibilities?:

--sd

[comment]

Why don't you try this yourself and see what you can come up with it?

[macavity]

No, none of those will do it.

The report I'm currently fighting with is created in a daughter table with an indeterminate number of line items. These are listed in the data part of the report. I want to ensure that these line items are not separated from the related "mother" table, which provides the information for the sub-summary parts. I don't mind if there is a break within the list of line items, but there should not be one between the sub-summary line and the first line item. Or, worse still (because thoroughly confusing) between two sub-summary lines.

If it were possible to export a report to, say, Word (or Excel) in a manner that required very little subsequent tweaking, that might be acceptable to the client.

[Søren Dyhr]

Could I get a sample of what it's supposed to look like, if you take it to a wordprocessor?

--sd

[macavity]

These are screen shots of two pages, taken at random, from a report.

The bottom line of page 1 shows at the left the name of the medium in which ads will be placed (Venice Biennale catalogue). That information is in one of the sub-summary parts, so all ads to be inserted in that medium will be grouped together. (In this case it is just one, but that is not typical.) The column headings next to the name are hard-typed in - simply because the space wasn't being used for anything else and they make reading the report easier. They could instead be in the header part and only display once per page. That is irrelevant to the problem.

The information pertaining to ads for the medium is in a second sub-summary part and below that in the data part. It displays at the top of page 2.

That is ridiculous and no one typing up a report like this by hand would leave it that way. You would naturally force the line with the name of the medium onto page two. And that is the kind of thing I want to be able to do in FileMaker. But how?

Edited May 26, 2007 by Guest

[Søren Dyhr]

Could you allow me keep you in suspence until thuesday, we're having bankholiday in Denmark ...so nerding isn't my wifes cup of tea!

--sd

[Genx]

Ahahahahahhaha, hahahahahah, hahahaha.

Nerding, LOL!.

I'm gonna posess ownership of that term.

[macavity]

Enjoy your holiday! (It's a holiday here, too). If you succeed in solving this you deserve a lot more holidays!

[Fenton]

The problem is that although you can tell FileMaker not to break a part at a page break, you cannot "link" 2 parts together. I would think that would be very difficult with the Body part. You'd be asking FileMaker to create any number of blank "lines" on the fly. And the result, in some cases, would be just as unattractive.

What I've done as an ameliorative is a trick called "conditional headers." It's been around for a while, but is simpler in FileMaker 8, using GetNthRecord. Basically it would show something in a Header part ONLY if the 1st body record was NOT the 1st body record for the Subsummary part. In this case it would repeat the "label" line. It doesn't solve the superfluous 1st label line, but at least it adds the labels above the body line on the next page.

I'm attaching a file (by others) which shows how to do this with different categories. There was a simpler file by John Mark Osborne, but I cannot find a link to it; perhaps he'll see this and post it.

A (crazy) alternative, for irascible clients. I've heard that developers have gone so far as to "build" the report, 1 record at a time; going into Preview mode after each record, to see if a subsummary break had occurred, creating blank body records in order to "pad" a page. Yuck.

SubsummaryHeaders.fp7.zip

[comment]

But even if you wanted to take the 'crazy' alternative - how would you tell if the subsummary is starting a new page or closing an old one?

I wonder if it's possible to export with some kind of markings that a word processor would know to translate into paragraph styles.

[Fenton]

Stop the press -] I just ran across an article and demo file in FileMaker's Tech Talk mailing list which creates page breaks using an extra Subsummary part ONLY if the subsequent ( Subsummaries + Body ) will not fit on the page. This is the post, in the General Discussion section. You must be a member of Tech Talk to access this; $99/year.

Grouping records on the same page as summary part - demo

Oliver Reid - 02:10pm May 24, 2007 PDT

[macavity]

What a fantastic coincidence! I'd thought of using an extra Subsummary part originally, seven years ago, but was stymied by the fact that as far as I could see FileMaker cannot determine what is actually located on what page in a report. I'm hoping I missed something there.

In case the solution offered is based on using the height of each page to calculate how many fields of a given height will fit on it, it probably wouldn't work in a report like mine that contains sliding text fields and even portals.

Exciting development!

[macavity]

I've also found this promising headline:

http://filemakeradvisor.com/doc/18695

I'd sign up for one or both of these if I knew they would really solve the problem, even with stacks of sliding fields. But I don't want to spend the money and be none the wiser for it.

[comment]

OK, I think I know a solution for this. Actually, I've had it all along here - I just didn't realize I did.

Make your leading sub-summary an unstored calculation field (result is Text) =

Case (

CategoryField ≠ GetNthRecord ( CategoryField ; Get ( RecordNumber ) - 1 ) ;

< your sub-summary information goes here >

)

Then place this field at the top of your body part (a merged field would work best, I think), and let it and the real body elements slide up. Do not let the body part break between pages.

[macavity]

Thank you a million times, Comment! You've saved my life - or at least my job...

I have more than one subsummary part but I used the same technique to keep them together. The only blemish, and it is a very minor one, is that the first of the two "welded" subsummaries repeats its field before each iteration. There may be no remedy for that, but it isn't a huge problem.

The only other problem rests with FileMaker which has never actually obeyed its own command not to break the body part over two pages. It's like those buttons at pedestrian crossings that claim to switch the light to red for traffic. They don't actually do anything, just make you feel less helpless.

[Genx]

Well technically... the body part doesn't break across pages -- the body part repeats e.g.

Subsummary

_

BODY

_

BODY

_

BODY

_

Subsummary

_

BODY

_

BODY

_

subsummary

etc. etc.

But it is kind of stupid that there is no setting available.

[comment]

the first of the two "welded" subsummaries repeats its field before each iteration.

I don't understand. If you have two sub-summaries - presumably a category and a sub-category - you need to calculate the two headers independently, e.g.

Case (

CategoryField ≠ GetNthRecord ( CategoryField ; Get ( RecordNumber ) - 1 ) ;

< your sub-summary by Category goes here > & ¶

)

&

Case (

SubCategoryField ≠ GetNthRecord ( SubCategoryField ; Get ( RecordNumber ) - 1 ) ;

< your sub-summary by Sub-Category goes here >

)

[Genx]

Feel like posting a sample file comment? i'm curious but have no clue what's going on...

[macavity]

In other words, a field in the body part with several lines of text or several fields below each other won't break across page boundaries. Well, I guess that's something.

Yes, I calculated the two headers independently. Although in fact the second of the two isn't a header but contains many fields, including some portals.

The second subsummary part can occur more than once per header of the first subsummary part. Now the header of the first subsummary reappears before each occurrence of the second.

"Tagesspiegel" is in the first subsummary part.

[comment]

The second subsummary part can occur more than once per header of the first subsummary part.

I am afraid I still don't understand. The calculation above should produce a category header every time the category changes, and a sub-category header every time the sub-category changes - just like "real" sub-summaries.

If you want to simulate a portal in the (simulated) sub-summary, I believe you'll need to build it yourself as well, using a custom function to write the related rows into the calc field.

[macavity]

I'm sure I'm doing something wrong, because the page still will break between the first subsummary part and the second. If I remove all fields from the first subsummary it works perfectly (but repeats the category name).

Also, simulating the contents of the sub-category (second subsummary) part would be a huge job! Each item has to be in a precise horizontal position. Some are text, some numbers, the latter have to be formatted in different ways (I can do that as text, but it would be a mess). Furthermore, in addition to the portals there are sliding fields lined up vertically, and others, with mutually exclusive contents, stacked directly on top of one another. All of that is necessary to save horizontal space as the information is required to appear as a row across the page.

[comment]

Perhaps this is the source of your problem: there shouldn't be ANY sub-summary parts (i.e REAL sub-summary parts). The calculation field that simulates the sub-summary (or sub-summaries) needs to be placed on the body part.

And yes, EVERYTHING needs to be calculated - though I don't think it would THAT difficult. Use tabs for alignment, and Case() for "mutually exclusive contents". Farming out number formatting to a custom function or two can also reduce the apparent complexity.

[macavity]

OK, now I do understand. Have you actually done this?

Whatever it is I did, it works perfectly, apart from the repetition of the category name - which I could probably sell as a feature

As for calculating the subsummary content: positioning with tabs, yes, but how would you deal with long text fields that have a determined width and break into an unknown number of lines?

And why does this not work: leave the fields separate but replace each with a calc that only shows the content if the result of the subsummary calculation is not empty? The result of the subsummary calculation would be a field that always has a value (eg. ID field).

Do you think there is a way to avoid the repetitions of the body field breaking across pages?

[Søren Dyhr]

How close are you macavity? Perhaps you noticed that the Advisor article mentioned that similar ways already existed for fm6, where I would say without having access to read it, that some of the trickery probably borrows heavily from this:

http://www.onegasoft.com/tools/fastsummaries/index.shtml

Instead of calc'fields telling where the breakers are! This means if Michael uses the same method as the article, have this been posible for quite a while!

--sd

[comment]

I have tested it briefly, and I haven't noticed any particular problems. But I only tried some very simple stuff.

I'd suggest you too start out with something simple, and add to it gradually. When you get to a problem, post a demo file.

[macavity]

I'm getting nowhere. I've been testing it with a simple file and getting erratic results. I tried a variation: a self-join between a field that gets nth record of the category field and another field that gets nth record-1 of the same category field. And made the relationship not equal. Then dragged the category field from this relationship onto the layout. That works a bit more reliably, but not really...

Sorry, I don't know how to post a file.

[Lee Smith]

Sorry, I don't know how to post a file.

It is an option on the [color:blue]New Reply screen. Down towards the bottom, ([color:red]not [color:blue]available on the Quick Reply) screen.

File Attachments: [color:red]Manage files

click on the Manage Files,

Browse (lets you select the file on your hard drive)

then

ADD (adds it to your post temporarily)

then

Finish (actually posts the file to your reply)

Complete the Body if you haven't already, and [color:blue]ADD POST

HTH

Lee

Edited May 29, 2007 by Guest

[macavity]

Thanks for the help!!

There are two summary layouts, one using the calc, the other using the relationship. Obviously I am doing something seriously wrong.

test.fp7.zip

[macavity]

OK - I got it! The category field has to be in lines.

[comment]

First, using a relationship to compare the records is not suitable: we want the report to print whatever is in the found set. A relationship will ignore the found set.

Now, the problem is your category is a related field. You are trying to determine what is the category of the previous record, but this:

GetNthRecord ( main::person ; Get (RecordNumber)-1 )

is not it. This expression is referring to some related record in the main table. If the current record number is say 5, the expression will be looking for the 4th related record in the main table. But there is only one related main record.

In this case you need to tunnel the category data into the child table - see attached.

test.fp7.zip

[macavity]

Comment - look at my post immediately before yours

I've played around with it. It is possible to add subsummary fields as separate calculation fields which will be a lot easier than working them all into one huge calculation.

I found two disadvantages:

1) Any vertical space you add between fields to separate categories repeats itself between body items. So in practice you can't use vertical space to group data.

2) Containers can't be used since they don't slide when empty.

test.fp7.zip

Edited May 29, 2007 by Guest