CDML Subsummaries - ISO FileMaker Magazine

by Michael Smith, via <editor@iso-ezine.com>

Subsummaries on the Web
Made possible by a little help from relationships
BONUS: SUMMARY.FP3 and HTML files
PLATFORM: Mac/Win
Requires FileMaker Pro 4.0

FileMaker Pro 4.0 makes publishing databases on the Web simpler and easier than ever. Instant Web Publishing provides you with a basic FileMaker interface to your data on the Web while Custom Web Publishing gives you more control over how your users interface with that data and how it is displayed to them. CDML, the language used by FileMaker to accomplish Customer Web Publishing, allows you to reproduce many of the intricacies of FileMaker layouts faithfully on the Web. Sadly, one of your old favorites, subsummaries, is missing.

One of the key elements in nearly every FileMaker solution is a good report or two which is usually centered on a subsummary. It is difficult to imagine using FileMaker without them -- at least it was for me. So, I sought out a way to reproduce FileMaker subsummaries on the Web and found a viable work around. Several variations on this work around are discussed in this article and the accompanying bonus files.

Laying the Ground Work

My first task in finding a work around was to examine what a subsummary really is. You put the records in order by sorting them and then compare a field value in the current record to the same field value in the previous record. If they are different, you show that field. That sounded like something we could do using a relationship.

A serial number field, Serial Number, is used to order the records. Instead of using Auto Enter as we would with most serial number fields, we leave it a simple number field. This is so we can reorder the records at will and reserialize the field to match. Next, we need another field to help us identify the previous record. This field, Serial Number Decremented, is a calculation, simply Serial Number - 1. Finally, we create the relationship. The relationship is named SUMMARY and related back to the current file. This is called a self-join relationship. We select Serial Number Decremented in the current file to related to ::Serial Number in the related file (which also happens to be the current file).

We also need to setup a way to serialize the records. For this, we have the script, Serialize:

Replace [No dialog, "Serial Number", "Serial Numbers"]
Exit Record/Request

Be sure to set Initial Value equal to 1 and Increment By equal to 1 for Serial Numbers. This will reserialize the current found set. But, we also need a way to clear the previous set of serialized values from the fields. Remember that relationships work beyond the found set and with the entire set of records. If there is another 2 outside of the found set, FileMaker may relate to that 2 instead of the 2 in the found set. The script Clear Serialize takes care of this:

Find All
Replace [No dialog, "Serial Number", ""]
Exit Record/Request

Is This a Subsummary?

As I said, there are several variations to this technique. We'll start with the simplest and most familiar; we'll let FileMaker do most of the work. Remember that you get a subsummary when the current field is different that the same field in the previous record. Now that we have a relationship, we have a way to check this using a calculation field, Show Type:

If(SUMMARY::Type <> Type, Type, "")

But, there is one further case where a subsummary should appear that is not covered by this calculation. On the first record in the sequence, SUMMARY::Type has no value but we do want a subsummary to appear. The final calculation for Show Type is:

If(IsEmpty(SUMMARY::Type) or SUMMARY::Type <> Type, Type, "")

Reports often contain more than one subsummary. In our example database, we want to do a subsummary using the Bloom field. We create a similar calculation, Show Bloom:

If(SUMMARY::Type <> Type or IsEmpty(SUMMARY::Bloom) or SUMMARY::Bloom <> Bloom, Bloom, "")

This calculation looks similar to Show Type with one addition, SUMMARY::Type <> Type. This is thrown in to assure that the additional subsummary will display whenever the first subsummary (Type) does whether or not SUMMARY::Bloom <> Bloom.

Putting It On The Web

Now that we've got all the essential elements in place, we need to build a few HTML/CDML pages to put it all together. I'll assume that you've built a few CDML solutions and just point out the essential details that make this solution work. In the bonus files, you'll want to look at the files find1.htm and hitlist1.htm. After the start solution comment in find.htm, we find the input statements that define that form:

We identify the database we are using, the format file, and the error file. Next, we identify the field by which to sort the database. These are also the fields important to our subsummary. If you've ever had to troubleshoot subsummaries, the phrase "Subsummary when sorted by...?" is surely burned indelibly into your brain. In cases where the default sort order, sort ascending, is not desired, the -SortOrder parameter is used. Here we specify a custom value list, Bloom.

Next, we see -Script parameters. First, the Clear Serialize script should run before a find request is performed, hence the -Script.PreFind. Clear Serialize performs a find all request before clearing the Serial Number field. If this did not occur before the find request, we would lose the found set when the script ran. After the find request is performed, the script Serialize should run. Scripts specified using -Script run after a find (or sort) is performed. Finally, the -Max parameter is listed. I like to include this parameter even though the default value is 25. I can never seem to remember this and have spent many an hour trying to figure out why only 25 records were displaying when no -Max value was set. By the way, the maximum -Max value is 2147483647, or you can just use the word "All."

Standard text input fields follow to specify fields for a search.

The standard submit and clear buttons appear near the end of the form. The HTML entity for a non-breaking space,  , is used to put some space between the buttons.

Finally, an A HREF is provided for a -Findall.

This URL provides essentially all the information found in the input tags at the beginning of the form. You may note that the -Script.PreFind is missing. This is because the found set is the entire database. There is no need to clear the Serial Number field before serializing it with the Serialize script.

Let us now virtually click the Find button or the Findall link and move on to the hitlist1.htm page. The [FMP-Record], [/FMP-Record] tags will display the HTML and CDML enclosed within them for every record in the found set. The first row display the field Show Type. Remember that Show Type is the calculation which displays a subsummary value when appropriate. The next row shows the field Show Bloom which displays the second subsummary value when appropriate. Via the magic of most browsers, table rows are not displayed when there is no value within them. In the cases that [FMP-Field: Show Type] or [FMP-Field: Show Bloom] are empty, those rows will not display at all. This will be important when we move on to Example 2. The following rows simply display a few more fields which represent the body part in a report layout with subsummaries. There you have it -- your first subsummary on the Web.

But Can't I Make it Look Better?

Example 1, shown in find1.htm and hitlist1.htm, is somewhat limited. Try adding a horizontal rule, <HR> to hitlist1.htm, and you'll find that the rule is displayed for every record. Why is this? Its the magic of HTML tables and most browsers. They don't display a table row if there's no data in it. Before, when the [FMP-Field] tag didn't result in any data, there was nothing to make the row display. The <HR> tag is considered data in this case and that row displays when we don't want it.

The file hitlist2.htm shows how we get around this snag. It looks essentially like hitlist1.htm. We only want to display an <HR> for the second subsummary, Bloom. So, we've added [FMP-If], [/FMP-If] tags around that row:

[FMP-If: Show Bloom.neq.]
<TR>
<TD COLSPAN=2>[FMP-Field:Show Bloom]<HR></TD>
</TR>
[/FMP-If]

Everything between the [FMP-If] tags will display only when Show Bloom has a value.

But What If I Want Another Subsummary?

Now you've hit upon the great weakness of Examples 1 and 2. They are clear and straightforward to implement in CDML but if you want to make changes such as add another subsummary or change the field you are using in a subsummary, you are back in the FileMaker database mucking with calculation fields. Example 3 shows another method which, once you have the initial setup complete, will allow you to create and modify subsummaries completely within CDML. For Example 3, you will need the fields Serial Number and Serial Number Decremented. You will also need the scripts Serialize and Clear Serialize. But, that's all.

As in our previous examples, there are essentially no changes to find3.htm. The file hitlist3.htm is where all the changes lie. Within the [FMP-Record] tags are the [FMP-If] tags which make this solution work. They are commented to help in deciphering their purpose. Essentially the [FMP-If] statements mirror the logic found in the FileMaker calculation fields but we have to look at the cases from a different perspective.

[FMP-If: Serial Number.eq. 1]
<TR>
<TD COLSPAN=2 BGCOLOR="#000000"><FONT COLOR="#FFFFFF"><STRONG>[FMP-Field:Type]</STRONG></FONT></TD>
</TR>
<TR>
<TD COLSPAN=2><STRONG>[FMP-Field:Bloom]</STRONG><HR NOSHADE></TD>
</TR>
[/FMP-If]

We need to look at when circumstances occur and what we want to happen when that circumstance occurs. Think about it as if a script were looping through our set of records. Our first case is for the first record in the found set. For the first record, we want both subsummaries to appear. Once we get past the first record, we want the first subsummary to appear whenever SUMMARY::Type <> Type. We also want the second subsummary, Bloom, to appear whenever the first one appears.

You might wonder why this case doesn't cover our first record as well. For the first record, SUMMARY::Type is blank so it doesn't equal Type. But, for some reason, it doesn't work this way and the separate case is necessary. Also note the use of "Field: Type" on the right side of the [FMP-If] tag. In order to let FileMaker know that it's a field on the right side and not a constant, the "Field:" specification is necessary. Technically its necessary on the left side as well but FileMaker doesn't call us on that one.

We're still missing one more case. The second subsummary, Bloom, displays whenever the first subsummary, Type, does. But, it should also display whenever SUMMARY::Bloom <> Bloom. We don't want it to appear twice in the case that this is true and the where the first subsummary is true. To accomplish this, we integrate an [FMP-Else] tag into the code above. This portion will only be evaluated in the case where the first subsummary is false.

[FMP-If: SUMMARY::Type.neq. Field:Type]
<TR>
<TD COLSPAN=2 BGCOLOR="#000000"><FONT COLOR="#FFFFFF"><STRONG>[FMP-Field:Type]</STRONG></FONT></TD>
</TR>
<TR>
<TD COLSPAN=2><STRONG>[FMP-Field:Bloom]</STRONG><HR NOSHADE></TD>
</TR>
[FMP-Else]

[FMP-If: SUMMARY::Bloom.neq. Field:Bloom]
<TR>
<TD COLSPAN=2><STRONG>[FMP-Field:Bloom]</STRONG><HR NOSHADE></TD>
</TR>
[/FMP-If]
[/FMP-If]

To make changes to the subsummaries used in Example 3, you change the fields referenced in find3.htm and hitlist3.htm. You will need to change both the fields referenced in the -Sort parameters as well as those in the [FMP-If] and [FMP-Field] tags. But, you will not need access to the FileMaker Pro database to make any changes.

Once the basic setup is in place, any of the three methods can be used to reproduce FileMaker subsummaries on the Web. You will want to choose one of the first two methods if you are new to CDML and find the [FMP-If] structures daunting. You will also want to choose one of the first two methods if speed is important to you. Given that the Clear Serialize and Serialize scripts must be performed, the solution is not particularly fast anyway. The addition of complex [FMP-If] and [FMP-Else] statements further tax the solution's speed. But, if you expect to change subsummaries often or expect to create multiple subsummaries, method #3 is for you.

## END ##

ISO FileMaker Magazine

Site navigation

User menu

You love new FileMaker information right?