The problem with real-time reports is they only give you a picture of your data for the moment they're created. The data could change even as you preview that report. But what's more important is you usually can't later recreate the exact results of that report because the moment is gone. Being able to analyze a report and compare it to the same type of report from last month, six months ago, or five years ago is a vital tool for mapping trends. Some users may try to deal with this by saving copies of their entire data files, renaming and/or placing them elsewhere for later reference of what was going on at the time. I can just hear many of you groaning at the idea. Fortunately, by taking advantage of a not-so-well-known feature in FileMaker Pro, you can take snapshots of your reports and archive them for later review and comparison.

At the risk of opening Pandora's box, further examination reveals yet another problem in need of this solution: recreating complex reports. Let's say you've built a report that draws on too many calculations, perhaps some of them unstored, which are then sub-summarized, summarized, and grand totaled. The time required to render this report could have you staring at a coffee cup-shaped cursor on your screen for quite a while. And what if you notice something wrong in the data or in the layout that needs changing? After making the correction, you'll have to wait all over again to preview or print that same report while all those unstored calculations are processed.

Of course, you could (and should) remedy this time-wasting ritual by redesigning your data containment structure, upgrading the network, optimizing your client and server hardware, or all of the above. But short of taking any or all of these measures, I invite you to read on and discover a simple yet effective way to squash these two problematic birds with one virtual stone.

Sneak Previews

One feature many a FileMaker Pro programmer are unaware of is the ability to copy a single page of graphic data while in Preview mode. That's right; you can preview a report and take a snapshot of it. The snapshot is stored in the clipboard as a PICT image. Taking the snapshot is fairly trivial; it's what you do with the snapshot that can lead to all sorts of valuable possibilities.

Selecting Copy from the Edit menu while in Preview mode creates the snapshot. You can also script this, which brings us to the example files (available at http://Advisor.com/Article/ryleg03). You can download the example files, or choose to build them yourself (and integrate them into your own solution) by following along.

First, start with a data file, which will hold the data and be the source for creating reports. This is called Data.FP5. I'll assume you know how to create a script that generates a basic report: Go to a formatted layout, find, sort, preview/print, and return to the original layout. The sample Data.fp5 file has a script called "Run Weekly Sales Report" that does just that.

Figure 1: Letting it all layout -- Here's an example of how the Layout Setup dialog should look.

Figure 2: Contain yourself -- Make sure your container field is sized to fit the entire page and graphically formatted as shown here.

Second, create another file to store the individual pages of each report as snapshots. Call this file Reports.fp5. For the first part of this example, the Reports.fp5 file requires only one field, a container type, in which to store the page images. (Later, you'll add more fields to facilitate searching and browsing the file.) In Layout mode, go to Layout Setup... (in the Layouts menu), name the layout Report, check the Fixed Page Margins check box, set all margins to 0.00, and click on OK (figure 1).

Now, remove the Footer and Header parts and drag the Body part down so it's directly on the page break boundary. Next, place the container field on the layout, and resize it so it completely fills one entire page, vertically and horizontally. Last, click on the container field to select it, choose Graphic... in the Format menu, select Crop with center/center alignment, and click on OK (figure 2).

Take a picture, it'll last longer

I'll leave you to handle the first file and the report on your own, because the contents and look of the report are irrelevant to this example. After it's created, go to the layout you use for the report, sort as necessary, go into Preview mode, and select Copy from the Edit menu.

Note that whether you manually select the Copy command or script it, FileMaker Pro copies the entire page being previewed to the clipboard. You don't have to maximize or otherwise size the window. However, if your report is more than one page long, the image of only the page currently being displayed is copied to the clipboard. Also, FileMaker Pro takes the dimensions of the PICT image placed on the clipboard from the default page size in your Page Setup (or Print Setup) on your computer, regardless of your FileMaker Pro layout's dimensions. Thus, if this solution is shared over a network and different users have different printers installed or different print defaults, they potentially will each get different size PICT images.

Now navigate to the Reports.fp5 file, create a new record, click inside the big container field (it's the only one in the layout and it fills the entire page) and select Paste from the Edit menu. Voila! There's your report. Well, at least the first page of it. If you have more pages, navigate back to the Data.fp5 file (which should still be in Preview mode where you left it), click on the lower half of the "rolodex" icon to advance to the next page, and repeat the Copy and Paste process.

Force fields

Now you should organize your Reports.FP5 file so you can script the process, and keep track of which records (i.e., snapshots) go together to recreate your reports properly. You'll want to create fields to store the report name and page number of each snapshot, as well as a few global and calculation fields to assist the scripting process. The field definitions should look like this:

Field Name	Type	Options
oReport_Image	Container
tReport_Name	Text
nPage	Number
cnPages	Calculation	Status (CurrentFound Count)

The field names shown here incorporate a naming convention that indicates each field's type. For example, the field tReport_Name begins with "t" to indicate it's a text field, and the field nPage begins with "n" to show it is a number field. And cnPages uses "cn" to denote it is a calculation with number result.

It's a good idea to integrate such a naming convention into all your solutions, as it can be helpful when working on files that sport dozens (if not hundreds) of fields that may share similar names. Also, the use of an underscore character (_) is preferred to a space because you never know if you might want to Web-enable your FileMaker Pro solution sometime later on, and spaces are among the many illegal characters you should avoid.

You'll want to make a second layout in the Reports.fp5 file as well, so you'll have one for creating new records, pasting snapshots of reports, and entering descriptive information, and the other layout for previewing and even reprinting a hard copy, when and if necessary.

After pasting the snapshot image of the first page of your report, enter the name of it into the tReport_Name field. You should try to use the same name as that of the script used to create the report, assuming it is fairly intuitively named. Something like "Weekly Sales Report" ought to do it. Although the current date could automatically be entered into the date field, it might be a good idea to add the date to the name of your report (i.e., "Weekly Sales Report for 11/3/01").

Then, of course, you'd enter the page number the snapshot you just took represents into the nPage field. As you add new records, consider using the Duplicate Record command to save time; you only have to update the nPage field with the current number, and, of course, paste in the new snapshot. The cnPages field automatically indicates the total number of pages in each report when you do a search using the report name as your criteria. This assumes each report is assigned a unique name.

Script to my Lou

Although it's nice to simply be able to store snapshots of your reports, the real wonder of this solution comes from scripting. There's no limit to how far you can take the role of automation in this solution. For the purposes of this article, I restrict myself to merely automating the act of populating the database. But I'm sure you can think of lots of other handy things you can add to it, based on your particular needs.

You'll have to add a few more fields to the Reports.fp5 file, as well as modify some of the existing ones to use the auto-enter option, like this:

Field Name	Type	Options
gnPage_Count	Global	Number
gtReport_Name	Global	Text
tReport_Name	Text	Auto-enter Calculation [gtReport_Name]
gnPage_Count	Global	Number
nPage	Number	Auto-enter Calculation [gnPage_Count]
cnConstant	Calculation	1

You'll use the cnConstant field to create a "constant" link to the Data.fp5 file, regardless of the record currently being viewed. You must also add the following fields to the Data.fp5 file:

Field Name	Type	Options
cnConstant	Calculation	1
gtReport_Name	Global	Text

Your script in the Reports.fp5 file (the one that will be called as an external script from the Data.fp5 file) needs to create a new record, Paste the copied image into the container field, and number the page. You'll use the gnPage_ Count field to keep track of the current page number. So, you'll first have to prepare the Reports.fp5 file for the oncoming archival process, with this script:

Script Name: Prepare
  Show All Records
  Show Omitted
  Set Field ["gnPage_Count", "1"]
  Go to layout ["Report"]

The reason you want to show all records, then show omitted, is so the process begins with no records found, which means when you're finished archiving the pages of your report in the Data.fp5 file, the new records in the Reports.fp5 file will be isolated in a found set ready for your review.

The script used for pasting the copied report page should look like this:

Script Name: Paste Image
  New Record/Request
  Paste ["cReport_Image"]
  Set Field ["gnPage_Count", "gnPage_Count + 1"]

Now, create the master script to the Data.FP5 file for archiving the report. This script is similar to the basic report script, but you'll have to add a few steps immediately following the Enter Preview Mode step (be sure to remove the Pause option) that will let the user name the report and prepare the Reports.fp5 file. Then, follow this up with a loop that cycles through each page of the report, triggering an external script in the Reports.FP5 file that will paste the snapshot and populate the data fields in a newly created record.

To complete the master script, you'll have to create a layout with the gtReport_Name field so the user can name the report he's about to archive. You'll also have to create a relationship using the gtReport_Name field to the tReport_ Name field in the Reports.fp5 file, so you can make sure the report name the user chooses has not already been taken. This is optional, but necessary if you use the report name as the means to link records that go together in the Reports.fp5 file, which (again) means each report must have a unique name.

Similar to using a field-naming scheme, you should name the relationship in a manner that will indicate both the local and foreign key field names, as well as the related file's name:

Relationship Name: gtReport_Name|tReport_Name [Reports]
Relationship:      gtReport_Name = ::tReport_Name
Related File:      Reports.fp5

You will also be using a universal relationship to move the name of the report to a global text field in the Reports.fp5 file. This isn't absolutely necessary either, but later we'll be using the gtReport_Name field in the Reports.fp5 file to browse sets of pages for each report, so it makes sense to take advantage of it now because you're likely going to want to view the archived version of the report when finished to make sure it's there.

Relationship Name: cnConstant|cnConstant [Reports]
Relationship:      cnConstant = ::cnConstant
Related File:      Reports.fp5

This is your universal relationship, meaning it will always relate the first file to the second regardless of which record is currently selected.

The master archive script in the Data.fp5 file should look like this (assuming you've already found and appropriately sorted the records in your report):

Script Name: Archive Weekly Sales Report
  Allow User Abort [Off]
  Go to Layout ["Name Report"]
  Go to Field ["gtReport_Name"]
  Loop
    Pause/Resume Script
    Exit Loop If ["not IsValid(gtReport_Name|tReport_Name
      [Reports]::tReport_Name)"]
    Beep
    Show Message ["A report has already been archived
      with this name. Please choose a different name.";
      Button1="OK"]
    Go to Field ["gtReport_Name"]
  End Loop
  Freeze Window
  Set Field
    [cnConstant|cnConstant[Reports]::gtReportName",
    "gtReportName"]
  Perform Script [Sub-scripts, External: "Reports.fp5";
    Script="Prepare"]
  Go to Layout ["Report"]
  Enter Preview Mode []
  Loop
    Copy []
    Perform Script [Sub-scripts, External: "Reports.fp5";
      Script="Paste Image"]
    Go to Record/Request/Page [Exit after last, Next]
    Exit Record/Request
  End Loop
  Enter Browse Mode []
  Go to Layout [original layout]
  Show Message ["Archival complete."]

I should point out a few things about this script. First, always make sure the Allow User Abort step is set to On until you've fully tested your script. Second, it is absolutely necessary to insert the Exit Record/Request step after you advance to the next page. This is one example among many where strange things can occur unless you insert this script step. In this case, the Copy function only works on the first page of your report unless you use the Exit Record/Request step. Try it, and you'll see.

Report on the double

Now that you've gotten the images of your reports in to the Reports.fp5 file, you'll want to create a method for browsing through them. First, create a layout with a smaller version of the container field (set to reduce while maintaining original proportions), one that lets you preview a thumbnail version of the first page of each report. You'll also want to add buttons for navigating between records (i.e., pages) and previewing an actual size version of the report by using your original layout and maximizing the window in a script. Then, add the fields nPage and cnPages so the user can see which page he is viewing in the report out of the total number of pages in the report.

You'll also want a portal listing each report. You don't want to see all records in the portal, because then a report's name would appear as many times in the portal as there were pages in the report. Therefore, use the cnConstant field to create a self-join relationship with the nPage field. Because the cnConstant field always equals 1, you only see the first page of each report in the portal.

Relationship Name: cnConstant|nPage [Reports]
Relationship:      cnConstant = ::nPage
Related File:      Reports.fp5

You'll also need another self-join relationship to group the pages of a report together, after one has been selected from the portal list. For this relationship, use the gtReport_Name field linked to the tReport_Name field.

Relationship Name: gtReport_Name|tReport_Name [Reports]
Relationship:      gtReport_Name = ::tReport_Name
Related File:      Reports.fp5

Place a button labeled Select in the portal alongside the cnConstant|nPage [Reports]:: tReport_Name field, and link it to the following script:

Script Name: Select Report
  Set Field ["gtReport_Name", " cnConstant|nPage [Reports]:: tReport_Name"]
  Go to Related Record [Show, "gtReport_Name|tReport_Name [Reports]"]

If you really want to spice things up (figure 3), create a global container field and place in it a rectangle object using a highlight color (such as light yellow), then create a calculation field with a container result to use the global container's contents if the value in gtReport_Name equals that of tReport_Name. Define these two fields as follows:

Field Name	Type	Options
goHighlight	Global	Container
coHighlight	Calculation	Case(tReport_Name= gtReport_Name, goHighlight)

Place the cnConstant|nPage [Reports]::coHighlight field precisely beneath the cnConstant|nPage [Reports]::tReport_ Name field in the portal, and you'll be able to highlight the report in the portal row currently being viewed. Don't forget to choose a transparent fill for the cnConstant|nPage [Reports]::tReport_Name field, or you won't be able to see the highlight behind it.

Figure 3: Spice things up -- Your navigation window could allow for selecting reports from a portal, and include a "thumbnail" preview of each report.

Off and running archives

The advantages of archiving reports in the manner I've described has numerous possibilities and benefits. You can expand beyond these techniques to include the tracking of other related information, such as the date the report was created, by whom, or whatever. You can then incorporate a robust search interface to let the user more easily find past reports. Yet another compelling use for this technique is to give users on a network the ability to preview a report while in Browse mode. This is particularly useful in secure solutions that don't allow full menu access to all users. And after you've created this interface for viewing PICT images, you may want to take advantage of the fact it can be used for archiving just about anything, not just images created while in Preview mode.

The one disadvantage of this sort of report archive is that you're only storing images and not the data from the reports, which could be used for further trend analysis and historical comparison. But that I'll leave for another time, and another article.