Database Publishing Demo

The Ad Builder application, created using AppleScript Studio, demonstrates how the construction of a Real Estate catalog can be automated by controlling multiple third-party applications with AppleScript.

This publishing example links tagged text and graphic frames in an Adobe InDesign catalog template to corresponding images and text in Microsoft Expression Media and FileMaker Pro databases. This technique is a standard method used in professional automated database publishing workflows.

How it Works

The technique of tagged container publishing relies on a unique identifier existing in both the data sources and the target layout template. The unique identifer can be a product SKU number, a customer number, an address, or even a name, as long as it is unique to each entry record in the source database files. It the Ad Builder example, the MLS (Multiple Listing Service) number assigned to each of the real estate properties is used as the unique identifer.

In the FileMaker Pro database, the MLS number is the contents of the MLS Number field in the Listings Database:

In Expression Media, the MLS number begins the name of the related images in the database file:

To enable the automation of extracting and placing image and text data into a layout, text and graphic frames in an Abode InDesign template are tagged with MLS numbers using the Script Label pallette:

Once a template has been tagged, launch the Ad Builder application. From its application menu, select Setup Demo to have the application automatically launch Expression Media and FileMaker Pro, and open the database files used in the demo.

The Ad Builder application will display a completion dialog when the setup is complete. Next, in the Step 1 tab, select the layout design to use.

Next, click the Step 2 tab, and select the option to import both the text and graphics from the releated databases.

Click the Build Document button to begin the automated publishing process. AppleScript scripts will be run that perform the following steps:

  1. The AppleScript script in the Ad Builder application asks InDesign for a list of references to the text frames in the open template that have a tag assigned to them.
  2. The script script then iterates the returned list and extracts the tag for each page item in the list of found page items.
  3. The script then uses the extracted tag to locate a record in the FileMaker Pro database whose MLS Number cell contains the extracted tag.
  4. The script then extracts the contents of the other cells of the matched FileMaker Pro record and inserts the data into the targeted text frame in the InDesign template. The inserted text is then formatted using stylesheets.

tell application "Adobe InDesign CS3"
 activate
 tell the active document
 try
 set the_list to every text frame of every spread whose label is not ""
 on error
 error "There are no tagged text frames."
 end try
 end tell
 repeat with i from 1 to the number of items in the_list
 set this_frame to item i of the_list
 tell the active document
 set this_identifier to the label of this_frame
 end tell
 tell application "Runtime"
 activate
 tell database 1
 show (the first record whose cell "MLS Number" contains this_identifier)
 set the_data to cell "Ad Text" of the current record
 end tell
 end tell
 tell the active document
 set the contents of this_frame to the_data
 tell this_frame
 tell paragraph 1
 set applied font to "Gill Sans"
 set font style to "Bold"
 set point size to 12.0
 set leading to 12.0
 end tell
 tell paragraph 2
 set space before to 0.167
 set applied font to "Gill Sans"
 set font style to "Light"
 set point size to 10.0
 set leading to 11.0
 end tell
 tell paragraph 3
 set space before to 0.167
 set applied font to "Gill Sans"
 set font style to "Regular"
 set point size to 10.0
 set leading to 12.0
 end tell
 tell paragraph 4
 set applied font to "Gill Sans"
 set font style to "Regular"
 set point size to 10.0
 set leading to 12.0
 end tell
 end tell
 end tell
 end repeat
end tell

  1. The AppleScript script in the Ad Builder application asks InDesign for a list of references to the graphic frames in the open template that have a tag assigned to them.
  2. Then the script extracts and uses the tag to locate an image in the Expression Media database whose name begins with the extracted tag.
  3. The script then imports the image into the target graphic frame and sizes the image to fit the frame appropriately.

tell application "Adobe InDesign CS3"
 tell the active document
 set the rectangle_list to every rectangle of every spread ¬
 whose label is not "" and content type is graphic type
 if the rectangle_list is {} then
 error "There are no tagged image rectangles."
 end if
 if the class of the rectangle_list is not list then
 set the rectangle_list to the rectangle_list as list
 end if
 end tell
 repeat with i from 1 to the count of the rectangle_list
 try
 set error_indicator to 1
 set this_rectangle to item i of the rectangle_list
 set this_tag to the label of this_rectangle
 tell application "Expression Media"
 set this_image to (the path of first media item of the front catalog ¬
 whose name begins with this_tag)
 select (first media item of the front catalog whose name begins with this_tag)
 end tell
 set error_indicator to 2
 tell this_rectangle
 set this_image to place this_image
 if the class of this_image is list then set this_image to item 1 of this_image
 fit given content to frame
 set image_h to the horizontal scale of this_image
 set the image_v to the vertical scale of this_image
 if the image_v is greater than or equal to image_h then
 set horizontal scale of this_image to image_v
 else
 set the vertical scale of this_image to image_h
 end if
 fit given center content
 set color value of the fill color to {255, 255, 255}
 end tell
 on error error_message
 if the error_indicator is 1 then
 set the error_message to "Image “" & this_tag & ¬
 "” could not be found in the database."
 end if
 activate
 beep
 display dialog error_message buttons {"Cancel", "Continue"} default button 2
 end try
 end repeat
end tell

This process is repeated until all the tagged page items have been filled. That's all there is to it!

Download

NOTE: this demo requires Mac OS X Leopard, Adobe InDesign CS3, FileMaker Pro 7 or higher, and Microsoft Expression Media (available separately or included in the Media Edition of Microsoft Office 2008).

You may download the Ad Builder demo application here.