Set Field Peculiarities Maybe everyone else knew this, but I just discovered it: If you don't specify a target field with Set Field then it will set the calculation to the currently selected field. I always knew this to be true with Insert Calculated Result, but I had just assumed that Set Field had to have a field reference to work.
Dull Boy FileMaker files were stored in clear text prior to FileMaker 7 so, if you opened a FileMaker file in a text editor, you could see the phrase "All work and no play makes Jack a dull boy" in the header repeated over and over. No, it's not a virus! My understanding is the developers just needed to take up some space in the header and that's what they came up with. Today, modern FileMaker files are stored in Unicode so I'm not sure if the phrase is still there.
A tremendous amount of reporting power has been packed into FileMaker Pro with an incredibly easy-to-use and flexible interface. The power and simplicity behind adding and deleting parts to organize your pages almost makes it too easy to create a sophisticated printed report. However, when a subsummary group crosses a page break, the subsummary part content doesn't repeat itself, requiring you to flip back and forth between the pages. During my five year tenure at Claris technical support, this was one of the top ten requested features. Twenty years later and there's still no way to attach a calculation formula to a part much like you can with Access Privileges to create conditional records. At least add a check box to repeat the subsummary part contents at a page break. Until some future upgrade, parts obey one set of rules embedded in the FileMaker code base. For now, your best bet is to employ workarounds for conditionally calculating subsummary content.
FYI: Subsummary parts will disappear from browse, preview and print if the break field attached to the subsummary part is not in the sort order. This is some sort of conditional display, right? However, if the view is "form", a subsummary part will display with or without a sort.
Conditional Footer This is a tough technique to grasp unless you've worked with a lot of reports. It's not complex because there are long formulas. Rather, the technique is complex because you need to have a good understanding of how FileMaker works with records and fields in the context of different parts. Let's start with a fairly simple example of a conditional footer and then progress to an example using subsummary parts.
BONUS: This article includes two example files. The first demonstrates the conditional subsummaries discussed below and the other shows how different fields react in the context of parts, views and sorts.
The idea behind this report is to display a total in the footer on the last page. Yes, you can accomplish this same task with a trailing grand summary but the total appears after the last record. I want the total to appear at the bottom of the page but not on every page. You could try a trailing footer but alas there's no such thing, only a title footer that displays on the first page only. You might even think you could accomplish this task using the Hide Object feature but there's no Get(TotalPageCount) function. You could use a script to enter preview mode, go to the last page and place the Get(PageNumber) result into a global field and then compare the current page to the global field to get a conditional formula. But, I want a self-contained calculation. I despise marrying features together unless required because you end up forgetting the two pieces go together.
That's kinda what goes on in my head when I'm thinking about how to solve a problem. I think about every advantage and disadvantage that crosses my mind and conclude the best approach to solve a problem. In another scenario, the script might be a good solution, allowing me to place a page count on my report (e.g. Page 3 of 9). It's all about picking the right tool for the right job. The more experience you have, the better you will be at adapting techniques for other uses. I'm kinda getting off on a tangent but this is important. So many of my students try to rush through my videos and look at just the parts they "think" will help them and skip lessons that will make them a better developer. Every piece of knowledge makes you a better developer.
Anyhow... what was I talking about? Oh yeah, conditional footers. LOL. The first step is to create a calculation called "rec_num" to display the current record number:
FYI: The current record number can be displayed without a calculation field using the Record Number Symbol menu item from the Insert menu while in layout mode.
It's important that this calculation field be set to unstored via the Storage Options section or the record number will not update as the found set or sort order changes.
SIDE TIP: When a stored calculation field doesn't reference a field, there is nothing to trigger the update of the result. Setting a calculation to unstored allows a formula to update every time the screen refreshes. Beware... don't make all your formulas unstored or performance may suffer!
The next step is to create a summary field that returns the maximum value from the "rec_num" field. Let's call it "rec_num_max":
The last step is to define a calculation that returns the result of the "rec_num_max" conditionally so it only appears on the last page. Title this field "conditional_footer":
rec_num = rec_num_max;
Since the record number only equals the maximum record number on the last page, it only displays in the footer part on the last page. Technically, it only displays the calculation result on the last record but that's where the footer gets field information... from the last record on the page. Plus, it's completely dynamic because of the use of the record number feature. No matter the found set or sort, the summary field will always grab the last or maximum record.
FYI: The record number is not like a serial number where it is attached to a record. The value is relative to the order of the records in the current found set.
I also want the word "Total:" to display to the left of the summary total. If I place the text in the existing formula, I'll need to change the calculation result to text. In this scenario, I won't be able to apply currency formatting via the Inspector and will be forced to calculate the currency by expanding the calculation formula. While you might have a custom function that formats text numbers, it's just as easy to define a second formula by duplicating the original. Call this field "conditional_footer_label":
rec_num = rec_num_max;
All you have to do is replace the amount_total field with the text you want to the left of the total value. Place the two fields on the layout in the footer as merge fields in the same text block and format the text block to display numbers as currency.
That's it! No you have a fairly simple solution to displaying data in the footer on the last page only. This solution won't work in the header on the last page but it will work on the first page in a header if you change the summary field to minimum. However, there's no point in doing this since you can just use a title header part to achieve the same results more easily.
FYI: A title header part replaces the header part on the first page of preview or print.
Conditional Header Let's move on to the more complicated example of a conditional subsummary. Let's say you have a database containing one record for each city your sales force services and the region of the United States where it is located. When a subsummary by region is created, the list of cities is bound to break across the page boundary. Pay close attention to the images directly below depicting a standard subsummary report. Notice the Northeast region records continue to the second page but the subsummary content of "Northeast" does not repeat. This can be confusing or at least cumbersome, requiring a reader to flip back a page or more to remember the grouping information. Remember, the information in the subsummary part might be more complex than a single field.
The second set of screen shots below include a conditional calculation field in the header part that detects when a subsummary part breaks across a page boundary. It also includes a few additional fields, that would normally be hidden, to assist you in understanding how the solution functions.
The "rec_num_min" field is a summary field that determines the minimum value in the "rec_num" field. While the summary field has the same value on every record when placed in the body part, it is context sensitive in the subsummary by region part and displays a minimum record number for the subsummary grouping. Pay attention to how the "rec_num" and "rec_num_min" field equal each other in the subsummary parts. That's because the subsummary part grabs the "rec_num" value from the first record in the subsummary grouping which is the same as the minimum.
However, on the second page, the record number and minimum record number do not equal each other. While you can't see the subsummary part on the second page, it does exist and is the same as the value from the last subsummary on the previous page. If you want to see the value, create the following formula in a calculation field and place it in the header part next to the "rec_num" field for comparison:
In order for a calculation containing a summary field to be context sensitive to a part, you must use the GetSummary function. Otherwise, the calculation will return the same value as if the summary field were located in the body part. In this example, the value would always be "1" since the minimum record number will always be the first in the found set.
Once you understand how values are stored in FileMaker depending on their context, a simple formula like the following will repeat the subsummary content when a subsummary part breaks across two pages:
rec_num <> GetSummary(rec_num_min; region);
region & " (Continued)"
Since the subsummary part doesn't actually display on the second page, the calculation must be placed in the header part. This requires a little smoke and mirrors to get it to look seamless but isn't too hard. Just make the header part a little bigger and place the calculation field at the very bottom of the part. The header contents won't show up in the exact same spot as the subsummary part contents but it's close enough.
Performance You might be concerned that the "rec_num" unstored calculation will slow down your report, especially since another calculation calls it making it unstored as well. On top of that, the formula also includes a reference to a summary field which is also unstored. And, you'd be right to be concerned. However, in my experience, it performs well. It's only when you start multiplying the number of unstored calculations on a single layout that performance issues can occur.
And, it's not just about the number of unstored calculations, it's also about their complexity. The calculations in this technique are not very complicated. Maybe complicated is a bad word to use. Sure, complicated formulas can be slow to process but it's more about relationships than complexity. The biggest culprit are aggregate functions like Sum and Count. If you include a lot of these functions to aggregate related records or even refer to lots of different relationships, things could slow down.
Last but not least, consider how many records are displaying on a single screen. A single record of unstored calculations will obviously display more quickly than a found set of twenty. The point is, it's not unstored calculations that are slow, it's the amount of them that display on the screen at one time and their complexity. Don't stop using unstored calculations, just be wary when adding them. If you test properly, you won't get that call from an angry customer when the solution slows to a halt once a bunch of records are entered.
Considerations One drawback of this technique is that header parts do not support all sliding features. Whenever the calculations in the header part return a blank result, it would be nice to reduce the size of the header part. Unfortunately, reducing the size of the enclosing part is not possible in a header or footer part. The result is a blank space at the bottom of the header which is noticeable when you compare a page that displays results for these calculations versus a page that doesn't. This may or may not affect your decision to implement this solution. That doesn't mean you shouldn't study the technique. You never know how you can apply the knowledge from one technique to another solution. Learn as much as you can about FileMaker and you will be rewarded in the long run.
Conclusion I've passed this tip onto a couple developer friends and their first reaction is, "it's going to be slow". I said try it and they were pleasantly surprised at the performance.
Glad you liked it and you are most welcome. I really appreciate comments like this cause sometimes you don't know if people are enjoying what you are writing. Thanks for taking the time to post a comment!