Automated Database Publishing


Successful businesses, large and small, rely on databases to organize and store information vital to their operations. And it is common practice to use this stored data to create reports, catalogs, and other documents used in marketing, sales, and management of products. For businesses looking for speed, accuracy, and scalability in their operations, automation is the essential element of their success.

The following workflow is an example of how stored data is quickly and accurately poured into print-layout templates using Automator, FileMaker Pro, and Pages. Note that the principles demonstrated here remain functional regardless of the scale of the project or operation, or the applications used.

The provided Automator action and demonstration files require the installation of Mac OS X v10.6 (Snow Leopard or higher), FileMaker Pro (client) v10.x, and Pages 4.x.

The Catalog


This workflow will construct a simple two-page product catalog using text and images from a FileMaker Pro database, placed into a Pages document.

Each product will be represented in the catalog by an image placed above formatted text describing the product and listing its SKU number, manufacturer, and price.

The Database


The data used to construct the catolog is kept in a FileMaker Pro database. Each record in the database contains fields for the product name, manufacturer, price, description, and SKU. The SKU of a product is a number used to identify the product, with each SKU being a unique number not shared by any other products. In addition, each record contains a summary field (below product image) that displays the text of other fields, reordered as it would appear in a catalog.

The Template


Each spread in the Pages document contains a grid of frames arranged in alternating rows of picture and text frames. Each picture frame (the red ones with the cross guides in them) will be paired to a corresponding text frame below it.

In order for automation to be possible, each text and picture frame in the layout document must be named or tagged with a unique identifier that corresponds to a field in a particular record in the source database. In this example, the unique identifier is the product SKU number, which will be used by Automator to locate the data to be placed in the tagged text and picture frames.

In Pages, frames can be tagged using the Pages • Name Selected Page Items service you installed. Simply select the frames to be tagged, run the service (from the Pages > Services nenu at the top left of the screen) and enter the tag text in the dialog. To view a frame's tag, click on it in the layout, and run the service. Any current tag or name will appear as the default answer in the dialog's text input field.

IMPORTANT: the Pages document included with the demo files you installed (see BEFORE YOU BEGIN at the top right of this page), has been pre-tagged to match the example database records.

Create the Workflow


The first step in creating this example workflow is to open the Pages document and the FileMaker Pro database included with the download.

Once the template and database files are opened, launch the Automator application, found in the Applications folder. In the forthcoming Automator window, click the Choose button to create a blank Custom workflow.

Selecte the Other category in the Library list on the leftside of the workflow window, to reveal the installed FileMaker/Pages actions. Add the FM to Named Text Boxes action to the workflow by dragging it from the Actions list to the workflow area on the rightside of the Automator window. The Action View containing the parameters for this action will be displayed.

Note that the two lists are automatically updated to display the name of the fields in the currently open FileMaker Pro database. Your selections in these lists will determine how the data in the database is to be published to the Pages template.

The list on the left is used to identify to the action which field in the database contains the unique identifier that corresponds to the tags of the text frames in the Pages document layout. In this example, the "SKU" field contains the unique identifier, so select it in the list on the left.

The list on the right side of the Action View is used to identify those fields whose contents are to be placed in the corresponding text frames in the Pages document layout. This list view allows you to select multiple fields by selecting the checkbox next to each field name, and to re-order the fields in the correct order for export. For this example, simply select the checkbox next to the field name titled "Summary."

Next, select the checkbox at the bottom right of the Action View titled "Apply Stylesheet" to enable the popup menu containing a list of the stylesheets in the Pages document. Choose the stylesheet named "Ad Text 01" from the popup menu to indicate to the action to apply the chosen stylesheet to the first paragraph of the placed text.

When the workflow is executed, the action will apply the stylesheet indicated as the Next Style of the stylesheet of the first paragraph to the second paragraph, and so on until it has formatted every paragraph in the placed text.

The next action in the workflow will import and format the product images into the Pages layout. Select the "FM to Named Picture Boxes" action from the Action List and drag it to the end of the workflow and release.

As with the previous action, the list on the left side of the Action View will indicate the database field that contains the unique identifier. Select the field name titled "SKU" in the list.

The list on the right side of the Action View will be used to indicate to the action which database field contains the image path or location. Select the field name titled "Product Image" from the list.

NOTE: the Product Image field in the FileMaker Pro database is set to only store a reference to the image file on disk, not the actual image data itself.

The final step in the workflow creation is to select Scale to Fill from the popup menu at the bottom right of the Action View. This setting will size each image so that it fits properly in its containing picture frame.

Run the Workflow


To execute the workflow, click the "Run" button at the top right of the Automator window.

  1. The action asks Pages for a list of references to the text and graphic boxes in the open template that have a name assigned to them.
     
  2. The action then iterates the returned list of object references and extracts the name for each page item in the list of found page items.
     
  3. If a page item is a text frame, the action uses the extracted tag to locate a record in the FileMaker Pro database whose unique identifer cell contains the extracted tag.
     
  4. The action then extracts the contents of the record cells chosen by the user in the action view, and inserts their data into the targeted text frame in the Pages document. The inserted text is then formatted using any stylesheet chosen by the user.
     
  5. If a page item is a graphic frame, the action uses its tag to locate a record in the FileMaker Pro database whose unique identifer cell contains the extracted tag.
     
  6. The action gets the path to the image file from the uer chosen picture cell in the matching FileMaker Pro record, then imports the image into the target graphic frame and sizes the image to fit the frame appropriately.

Automator will play a chime when the workflow has completed and the catlog created.

Summary


This example workflow is a small example of the power of automation and Automator and how it can be used to transfer and format data between applications. These actions and naming script should work with any Pages document and FileMaker Pro database.