Generating printed Index for a report

[Paolo]

This tutorial demonstrates how to generate a printed index for a printed report.

(Attached there is an archive with the sample FileMaker 6.0 file PriceList.fp5 and the IndexMaker application [MacOs X] w/source code. The sample can be easily ported to higher versions of Filemaker.)

Suppose you are a reseller and want to print, for your customers, a price list of the items you sell trought your store.

For each item the following informations are provided: id, description, brand name and price.

Open the PriceList.pdf file and run the script “Show Price List”: you can see it. It is ready for printing, sorted by brand.

(For this sample it is populated with random data; random descriptions are quite odd, sorry).

Since the Price List is quite long it would be useful to have a printed index of it. For example you may want to print a listing of the brands available and show, for each one, the page number where that brand can be found on the printed Price List.

To do this you first need to know at what page of the price list each record is printed.

The only way to do that in FileMaker is to write a Script that does the following:

show up the entire price list

(1)

enter preview mode

go to last page

save the page number in a global field

enter browse mode

go to last record

write the saved page number on the record

omit it

go to (1) and repeat until all records are omitted

This Script would work but, for a long price list, with several thousands items, may take several hours to execute.

Since time is money you want to do this faster. The only way (I founded) to do this faster is doing it outside FileMaker with the aid of the IndexMaker application, I wrote for this purpose.

If you run the script “Show Price List for Index Generation” you see a report similar to the Price List. The graphic layout is the same however each item is replaced with the string in the format @@nn@@pp@@; where nn is the item id and pp is the page number.

Now print that report to a PDF and name the PDF File XPriceList.pdf.

Now run the application IndexMaker that comes with this tutorial. Once the application has launched click the button to open the file XPriceList.pdf (You can do this also by dragging the PDF file onto the application icon).

The application parses the PDF file searching for strings in the form @@nn@@pp@@ and generates a tab separated text file with item ids (key) and page numbers.

The generated file have the same name of the source PDF, with extension “.txt”, in this case XPriceList.txt.

It’s important to note that the @@nn@@pp@@ string takes the same space and appears in the same position and page of the original item’s string in the “Price List” layout. Please note also that the item id is unique (and works as key) in the database.

Now go back to FileMaker and run the Script “Import Page Numbers” selecting, when requested, the file XPriceList.txt (Pay attention to select "Tab Separated Text File" type).

The generated file is imported and page number is associated to each item of the price list.

Then the Script operates to setup an index by brand and shows it up.

Obviously if you later add or remove records or change the layout the operation need to be performed again: generate pdf for index generation, launch IndexMaker and generate the tab separated text file, import the text file and generate index data.

This example produce just a simple index by brand of a simple price list. But as you have the page number for each record you can make any kind of index, glossary or reference for any kind of report (the explanation of how to generate complex indexes is out of the scope of this tutorial).

The only condition that must be satisfied is that each record can be replaced by the @@nn@@pp@@ string without affecting the position (in term of page number) of other records.

Paolo.

MakingIndex.zip

[comment]

Your application will not launch on my system (10.3.9).

However, it seems that you want to generate a group index, i.e. only showing the page on which a group of records starts. If so, it is rather inefficient to loop by a single record. Instead, a method similar to Edoshin's FastSummaries can be utilized - as shown in the attachment. Using the data from your file, it takes me less than 20 seconds to produce the index.

With this type of report - where every record has a fixed height - there is even a more efficient way, and that is to compute the page number. A script to find the actual page number is only required when some objects are set to slide and also reduce the size of the enclosing part.

GenerateIndex.fp7.zip

[Paolo]

Your application will not launch on my system (10.3.9).

The application make use of the PDFKit Framework, available from Mac OS X Tiger.

However, it seems that you want to generate a group index, i.e. only showing the page on which a group of records starts.

That's what I do in the example. But I wanted to cover the most general case where I need to know the page number of a large amount of records (if not all).

The sample file is a siplified adaptation of a larger work I recently did. The original work was a 300 pages document generated from a 30.000 records file. For that document I had to do 3 indexes, for a total of 2.000 indexed records.

I think that performing "go to last page" script step with 2.000 records where the last page range from 1 to 300 will take far more that 20 seconds.

With this type of report - where every record has a fixed height - there is even a more efficient way, and that is to compute the page number.

Of course I wanted to cover the general case where the page can't be (safely) calculated.