Scripting for Lawyers > Database

In many professions, knowledge workers use forms with special fields to be filled in—templates, in other words—to speed along their daily tasks. Legal professionals are no exception. We use templates to efficiently create documents for our clients. Bankruptcy petitions, trademark applications, copyright applications, and real estate closing documents are just a few examples of templates used by legal professionals. AppleScript can help improve the efficiency in creating and tracking these documents.

A typical template includes pre-defined fields where data from a database or spreadsheet gets inserted. For example, we might have the following greeting in a template:

Dear FIRST_NAME LAST_NAME:

With this template, we would replace the FIRST_NAME field in the template with data from our data source. We would then do the same for the LAST_NAME field. At a basic level, this resembles a merge that you might find as a feature in Word or WordPerfect. But we can use AppleScript to control all aspects of this “merge” including calculating values and making decisions depending on the values in your data source. In addition, by using AppleScript, you are not necessarily tied to one word processing vendor.

There are two ways to accomplish this task. With the first method, we can recreate the entire document from scratch every time we run our script. Our script will hold the template, use variables to replace the fields in the template, then write out the entire document to a file. The second method uses search and replace to find the template variables and change them to the proper variables.

The advantage of the first method is that the final script need not be dependent on your word processor. You can simply copy the resulting template to the clipboard and the paste it into your preferred word processor. The advantage of the second method is that you can use existing templates to the extent that they exist. In addition, if you want to change the template, you don’t have to fiddle with your script.

In the next two posts, we’ll look at each method of using templates to create documents.

In our last post, we discussed grabbing data from a database using AppleScript. Although several excellent databases exist, we’re going to use FileMaker Pro because many Mac lawyers use FileMaker as a case management tool. FileMaker is an Apple subsidiary that creates several versions of FileMaker Pro, a database application. A database stores and organizes your data to help you manage that data. FileMaker Pro differs from most databases because FileMaker Pro includes tools to create a graphical user interface “on top of” the underlying database. Click here for an introduction to databases and FileMaker Pro.

Before we begin scripting FileMaker, we need to create a new database or use an existing database. Fortunately, FileMaker ships with several existing templates including a database to manage contacts. Because we’ll be using the data from FileMaker Pro to write a letter to a contact, we’ll use this existing database as our database.

Open the Contact Management database in FileMaker and enter a new record. (You may have to open and save the database first). For this example, we need only enter the first name and last name of a contact. In my example, I entered “Larry” for the first name and “Staton” for the last name. We’re done with FileMaker for now, so we can hide it. (We’ll be back to FileMaker later, so don’t quit it yet.)

When we want to script an application, I find it helpful to review the application’s dictionary in Script Editor, so let’s do that now. Open up Script Editor and select “Open Dictionary” from the File menu. Select FileMaker Pro from the dialog box. You should now see FileMaker’s scripting dictionary:

Let’s focus on what makes a FileMaker database. FileMaker has a strange dictionary. The main elements fall under the “Subset of the Core, Table, and Database suites” category. Select this category and scroll down to the database container. From the dictionary, we can see that database is the top-level container. Each database is made of tables. Tables organize related information in the database. Each table is made of records. A record is a single entry in the database. When we entered a new contact into the database, we entered a new record—a single entry into the database. Tables also contain fields.  Each field stores a single piece of information. We want to access these individual pieces of information. In our example, we want to get the value of the first name field and the value of the last name field of our contacts database. After we get this information, we can push it to any other application, such as Microsoft Word or iCal or Mail. So let’s write a script to get these values.

Open up Script Editor and type the following:

tell application "FileMaker Pro"
    tell database "Contact Management"
        tell table 1
            tell  record 1
                set firstName to field "First Name"
                set lastName to field "Last Name"
            end tell
        end tell
    end tell
end tell
set fullName to firstName & " " & lastName

Now “Run” the script. In the “Result” window at the bottom of the Script Editor window, you should see the name that you entered into FileMaker:

Study this script and let it sink in. In my next post, we’ll break down the script in some detail.

AppleScript shines at bossing around your applications. Not only can you use AppleScript boss around individual applications, you can also use it as a conduit to transfer data from one application to another. A common example involves transferring data from a database application such as FileMaker Pro to a word processor such as Microsoft Word. You might also transfer data from a database to a spreadsheet application and then to your word processor. Or read data from a text file into a spreadsheet to produce a report or into Timeline to produce a timeline. As you can imagine, the possibilities are endless. (Update: Pete Summerill, the Mac Litigator, has produced a script that combines Adobe Acrobat Pro and Journaler. If you use these apps, take a look.)

In the next few posts, we’re going to build a database in FileMaker Pro and learn how to script it using FileMaker Pro’s AppleScript dictionary. We’ll create a simple database with some fields and use AppleScript to access those fields. Once we have the data in AppleScript, we can send it to another application. We’ll send our data to Microsoft Word 2004 to create a letter.

Administrative: I’d like to ramp up the frequency of the posts, but I don’t want to move too quickly so as to leave new scripters behind. I’d like to hear your feedback regarding the frequency of the posts. Would you like to see more posts more often or do you like the current one post per week rate? Please leave your thoughts in the comments.

(more…)

Before we get to our first script, I thought that I’d post a quick screencast of a script in action. This screencast shows some of the power of scripting. In the screencast, I grab some data from a text file, parse that data, then send the data to Timeline to create a beautiful timeline.

Click here to view the screencast

As you can see, I’ve used this script to create a timeline of events in a litigation context. In this case, I used a simple text file as my data source, but you could also use a database like FileMaker Pro or a spreadsheet like Microsoft Excel to hold your data. (Not that there’s anything wrong with plain text, mind you.) More importantly, however, I saved valuable time by not having to enter the data twice–once into my data source and again into Timeline. And I can use the script over and over again, saving more time every time I use the script. I timed the script execution to around 2 seconds. I then manually entered the events to create the timeline and finished in 240 seconds. Every time I use this script, then, I save almost four minutes of my time. This is the power of scripting.

On to our first script!

Open Script Editor and type the following text into the editor pane:

say "Hello from AppleScript!"

Now click the “Run” button or press Command+R. You should have seen the text change color and then you should have heard your computer speak the words, “Hello from AppleScript”. Cool!

So what happened? When you clicked the “Run” button, Script Editor secretly compiled the program first. When Script Editor compiles a program, it checks for errors in your code, then converts your code to machine code for the computer to read. Here, the translated code sent a command—the “say” command—to the computer along with an argument for the command—the text string “Hello from AppleScript—and the computer obeyed your command and said the words, “Hello from AppleScript”. The key lesson to remember is that, in AppleScript, you “tell” your applications to do things using “commands”. Who’s the boss, now?

How do you know what commands you can send to an application? For that you need to know about AppleScript dictionaries, the subject of the next post.

Scripting for Lawyers is a weblog that will explore all facets of scripting. From learning about the various scripting languages to writing our own scripts to digging into the guts of the operating system, together we will attempt to understand the ways that our computers can make you more efficient, more productive, and more profitable.

As the name of this weblog suggests, I’ll focus on how lawyers, especially those lawyers who use Macs, can use scripts to boss around their applications and data. Although scripts often operate on their own, many scripts can be used in connection with databases, text editors, word processors, calendars, and e-mail applications. As such, we’ll also look at setting up a database and using scripts to push that data into the other applications. We’ll also explore how to access Web services to grab data to put into your application.

#### Why focus on lawyers?

Well, I’m a lawyer and I want to help other lawyers who are interested in scripting. Many lawyers are interested in scripting, but they either don’t have the time to learn scripting or they are intimidated by the geekspeak that they find when exploring this technology. Other lawyers simply don’t understand the power of learning a scripting language and want some examples of how they can incorporate scripting into their workflow.

#### What is scripting, exactly?

Scripting is the process of writing short computer programs that tell your computer to complete a task. In short, you write scripts to boss around your apps. In AppleScript, for example, you “tell” an application process text or save a file or schedule an event. You use scripts to create and enhance your workflows with your current applications. You do not need to code an entirely new application to meet your needs.

#### What do you know about scripting anyway?

I began using Macs in 1998. My first Mac was a green Rev. B iMac. One of my graphic design friends was working on a large [Photoshop](http://www.adobe.com/products/photoshop/index.html “Adobe Photoshop”) file and, while I looked on, dropped the file on to a folder. Suddenly, an application opened, resized and transformed the file, and closed the file. Another application then opened and transmitted the image to a remote database. I stood in amazement. When I asked how they did that, the reply mentioned [AppleScript](http://www.apple.com/applescript/ “Apple’s scripting language”), Apple’s built-in scripting language. Well, I had to try that out. So I went home and started scripting. In 1999, I began working in an all-Mac law firm. The law firm used [FileMaker Pro](http://www.filemaker.com “Apple subsidiary of database software”) to keep track of everything. After learning the workflow and toying with the databases, I began writing AppleScripts to integrate Filemaker Pro with other applications. I have been scripting ever since, only now I’ve learned several more languages and how to use those languages with more databases and applications.

#### So which scripting languages might we see?

The majority of scripts that I plan to show will feature AppleScript. I also plan to introduce you to [Ruby](http://www.ruby-lang.org/en/ “Ruby”), [F-Script](http://www.f-script.org “Smalltalk-like scripting language”), and [JavaScript](http://www.ecma-international.org/publications/standards/Ecma-262.htm “Used on the Web and Adobe Acrobat”). We may also dabble in [Python](http://www.python.org “Python”), [Perl](http://www.perl.org/ “Perl”), and [Smalltalk](http://www.smalltalk.org “Smalltalk”).

#### Wow. That’s quite a bit. What about databases?

I’ve found that most Mac-using lawyers use FileMaker Pro to store information about their cases, which is great because FileMaker has an excellent AppleScript implementation. As such, most of the posts will cover FileMaker Pro. However, I also plan to feature databases such as [OpenBase](http://www.openbase.com) and [SQLite](http://www.sqlite.org).

#### And applications?

Deadlines are very important to lawyers, so learning to script a calendar application will be shown. In this case, we’ll look at iCal. Of course, lawyers write lots of letters, so we’ll look at text editors like TextEdit and word processors like Microsoft Word. We’ll also learn how to script Mail and Safari. Finally, we’ll look at how to script specialty applications like [Bee Docs' Timeline](http://www.beedocs.com “Software for creating timelines”).

#### What if I need help?

That’s what this weblog is for! I want to make this weblog into a conversation where we learn from each other. If you need help that is specific to your situation, [I'm available for consultation](http://scriptingforlawyers.com/?page_id=7 “Find out about my services”).

Now let’s get scripting!