entertainments: EmData builds annual commercial real estate directory ? Em Software

August 31, 2012 by cpr - Comments off

Mike Docker, InData developer extraordinaire, shares with us how he used InData with InDesign to build an annual commercial real estate directory.

Introduction

Estates Gazette (EG) is the weekly magazine for the UK commercial property trade, and each year it produces a directory of warehousing and distribution parks across the country with space available for rent or purchase.

The data is held in an Excel spreadsheet, which is updated from information provided by property agents. This comes into the EG offices at different times and is handled by different staff members. Also, each distribution park can be represented by multiple agents, so it is not uncommon for one park to be entered into the spreadsheet several times.

As publication date approaches the agents are reminded to check and amend the data they have provided, and the updated spreadsheet becomes the source for the printed directory.

The magazine?s production team had been using cut-and-paste techniques to get the text on to the page but they approached the task with dread as each year rolled around. I persuaded them to give InData a try.

Design

For the 2011 directory a layout was prepared by EG?s art director using the magazine?s existing fonts and colour scheme. It allows for three entries per A4 page, each set on a tinted background with a heading in white reversed out of colour.

Each entry consists of a park name (the heading); address; website, if any; a list of measurements, prices, road/rail connections, etc; up to 50 words of descriptive copy; the agents? names and contact details; and agents? logos, where requested.

As the directory is divided into regional sections, each region is assigned an identifying colour which is used in the page headers, park names and subheadings.

Up to four agents? logos are accommodated in each entry. Where there are four they are set in a square block, two above and two below. Any fewer than four are aligned vertically on the right of the entry.

As they are shown on a tinted panel, the logo graphics are edited in Photoshop to remove unwanted backgrounds. This gives a cleaner look to the page than having them sit in white rectangles on the tint.

Data preparation

For the 2011 directory the Excel spreadsheet contained 957 rows (individual entries) of 35 columns (data). Most of the columns were information that would appear in the directory, but some were administrative. After making a working copy, I deleted the admin columns and added four of my own to contain the filenames of any logos that were required.

This became the master database. My first task was to try to identify any duplicate entries that might have been submitted by different agents, or entered twice in different regions, say. To facilitate this process I used an Excel macro that displays two copies of the spreadsheet and highlights any rows where the data in two given fields matches up. I then made a list of possible duplicates to submit to the EG data owners for them to decide whether or not to remove one.

Obviously, at this point it was important that the spreadsheet rows remained in their original order so that cross-referencing could take place.

With the data still in handy columns I ran a series of smaller Excel macros to check and format elements such as telephone numbers, email addresses and postal codes to make sure they conformed.

Another macro was run to check the list of agents who had requested (and paid for) their logo to be shown and insert the filename into my additional columns in whichever row their name occurred.

When this period of review and amendment was over the spreadsheet was sorted and exported, region by region, as tab-separated text files. Held in distinct regional files, each entry now consisted of a paragraph of text with its data fields separated by tab characters.

I ran a Word macro at this stage inspecting each paragraph (entry) to make sure it had the correct number of tabs (fields).

The prototype

I chose to make a separate InData prototype for each region with its individual colour scheme. Each region?s prototype was the same except for the colour. This slightly simplified the coding and, more importantly, it divided a big job into more manageable chunks. Page numbering could be applied later using InDesign?s special ?Next Page Number? character.

Each entry (three to a page) had a master text frame with a background tint, plus two borderless text frames overlaid on the right-hand side: one two-column frame to contain the ?notes? text in one column and three in-line picture frames (or boxes) in the second column; and another on top of that to contain four in-line picture frames for the exceptional cases where four agents? logos were required.

The prototypes were InDesign documents rather than templates, and each one consisted of an opening spread based on a master spread which was already populated with chained text frames as described above. On the pasteboard to the left of the opening spread was the code, contained in a matching series of linked frames to accommodate column breaks and frame breaks.

Production

Using InDesign with InData and InFlow, I simply opened each regional prototype, placed the cursor in the first text frame of the document, pointed InData to the source text file and let it rip.

Once the flat plan for the directory had been finalised?there were adverts and introductory text pages in addition to the park entries?I could move each section?s start page to be a right-hander, if necessary. Page numbers were then applied by simply numbering the first page?InDesign?s ?Next Page Number? character took care of the rest.

A final look-through enabled me to tidy up any ugly text breaks before each section was output to a colour laser printer for proof-reading. As the text and pictures were all part of the same flow it was an easy matter to delete or reorder whole entries if required by the proof-readers.

The final documents were exported as PDFs one page at a time using the magazine?s preset, and sent to be printed.