VIEW MORE SCREENCASTS   |   MISSINGMANUALS.COM HOME PAGE

	Click here to preview and order the book.

TRANSCRIPT

Hi, I'm Geoff Coffey, co-author of FileMaker Pro 9: The Missing Manual. In this screen cast, we're gonna talk about one of FileMaker 9's most exciting new features, the external SQL sources. Using this feature, you can connect your FileMaker Pro database to an Oracle Microsoft SQL service or a MySQL database and have a direct live connection to that data.

In particular, we're gonna talk about setting up external data sources in FileMaker Pro, adding external tables to your database, and working with shadow tables.

Let's pretend you've built a new Web site, a blogging Web site; and on that Web site, you allow your readers to leave comments. Now, what you wanna do is you wanna take the People database that you've been working on; and you wanna show the comments from your readers right in this database. So if one of your friends or associates who's in your database leaves a comment on the site, you want those comments to show up right here in FileMaker.

Now, your blogging system uses the MySQL database, which is a high-end, sort of backend database system. And with FileMaker's ESS feature, we can integrate directly with that MySQL database.

Now, before we dive into this, I should let you know, to be perfectly honest, that we're gonna gloss over the first part of setting up ESS, and that is defining an ODBC data source; and the reason is because there's a lot of variability to that and a lot of different possibilities; and it would take forever to go through it all.

If that's something that you need help with, the book covers creating ODBC data sources on Windows XP, Windows Vista, and MAC OS 10, so it's a great resource for that.

In this case, I've already defined my data sources; and they're ready to go. We're gonna focus on just the FileMaker side of things.

So I have my People database right here. I'm gonna go to the File menu, to Manage, and then External Data Sources. This is where we tell this database how to interact with other databases.

In the Manage External Data Sources window, I'll click the New button to add a new data source; and the first thing I'll do is choose ODBC instead of FileMaker. I'll name this data source My Blog; and then I need to pick the data source that I've defined in my operating system. So I click Specify, and here's a list of all of my ODBC data sources. I'll choose My Blog. That's the one I just created, and push Okay.

I also wanna hardcode the user name and password with this data source definition, so that I don't have to type it in every time I open my database. So I'll turn on Specify User Name and Password, and I'll put in my ODBC database user name. This is the user name from my MySQL database, and the password.

Now, these things down here, I'm just gonna leave blank. They allow me to tell FileMaker not to show me everything on that database server, but to reduce the results to just what's important to me. But in this case, it's a small database; so I don't have to worry about that. I push Okay, and now I've defined a data source. Push Okay again to close that window.

Now, I can go to the File menu, Manage, Database to make changes to my database structure. I'm gonna switch to the Relationships tab, and here I see my one table occurrence of my People table. I'm gonna push the Add Table button, and FileMaker lets me pick a table.

From this popup menu, I see a choice of all of the data sources I have defined. I'll choose the My Blog data source that I just created, and then I see all of the tables that are on that database.

Now, we're gonna take a diversion here to show you something. We're gonna dip behind the scenes and look at the actual MySQL database to see what it looks like behind the scenes.

I have that MySQL database running on Linux, so I'll switch over to the Linux machine. Now, this is the MySQL database. This is pretty ugly. This is why we love FileMaker, because we don't have to deal with stuff like this. But, anyway, it's a command line interface to the database; and I'm looking at my Web site database here.

So let's look at the tables that are defined in this database. I'll type Show, Tables. Now, don't worry. You don't have to do any of this stuff to use ESS. I'm just showing you what the backend database looks like, so we can see how FileMaker mimics that look in it.

And these are the tables that are defined in my database: Categories, Comments, Images, and Posts. And let's look more closely at the Comments table, because that's the one we're interested in. So I'll say Describe Comments; and, here, MySQL tells me about that Comments table. I see I have these fields: ID, Name, E-Mail, Stamp, and Comment, and the different types – and that's like in Number field; Varcares, geek word for the Text field; a Date, Time, or Timestamp field; and it tells me this is a primary key. It's an auto-increment, which is just like the FileMaker serial number, and things like that.

So this is the database in MySQL.

I'm gonna go ahead and switch back to Windows now; and here, when I've chosen my data source, I see those same tables listed here. I'm gonna click the Comments table and push Okay, and now FileMaker adds a new table occurrence to my relationship graph for that Comments table.

You'll notice that the word Comments right here is in italics. That's FileMaker's way of telling me that this table occurrence refers to a table that's not in this database. In fact, it comes from a different database, the MySQL database. And here we can see all the same fields that were showing up in MySQL: ID, Name, E-Mail, Stamp, and Comment.

What I'm gonna do is I'm gonna take this E-Mail field, and I'm gonna match it up with the E-Mail Address field in my People database. Now, having made that connection, I've told FileMaker how my table in the People database relates to the table in the MySQL database. Now I'll just push Okay.

Now, you'll notice that those MySQL tables work a lot like regular FileMaker tables; and that similarity extends all the way through the product. I'm gonna switch to Layout Mode. I'll make my body a little bit larger, and I'll add a portal to this layout. I'm gonna choose that Comments table for my portal. I wanna show a vertical scrollbar, and let's show three rows. Push Okay.

Now I will add Name, Stamp, and Comment to my portal. Push Okay again. Now this is pretty ugly here, so let's go ahead and fill this guy with white. Change his border to solid, and the same with these fields. We'll fill them with white, as well.

Now I'm gonna make these rows a little bit larger, and I'll bring the Comment down below Name and Stamp. Stamp is like the timestamp like when that comment was left. Of course, that name comes from the MySQL database; so it could be just about anything.

I'll drag this comment now wider; and, of course, you can decorate this portal however you see fit. Now, I'm gonna save my layout and switch back to Browse Mode; and here we can see I have my same Person record from before; but right here it shows me the Name and the Comment coming straight from the MySQL database.

In fact, I can even come in here and make changes if I want to; and those changes are pushed back out to the MySQL database.

Now, let's look a little deeper at what FileMaker did when we added that table occurrence. I'm gonna go back to the Manage Database window. As soon as I added this table occurrence to my relationship graph, FileMaker added that table to my Tables tab, as well.

Now, this isn't a real table. It's what's called a shadow table. It's like a shadow of the table from MySQL, and you can tell it's a shadow table, because all of its text is in italics here in this table list. But FileMaker puts it here so that I can make certain settings and configurations about that table.

So I'm gonna switch to the Fields tab now; and with my Comments table selected, I can see all of the fields in that database. And now this lets me do things like, for instance, I can set FileMaker validations and auto-entry options for MySQL table fields.

So I'll click, for instance, the Comments field and click Options; and you see that I can come in here and say, "I want to limit that to a specific size," or "I wanna make sure it's not empty," or something like that. Those settings don't apply in MySQL; but FileMaker will apply them; so that when you make changes to the data in FileMaker, those restrictions will be applied.

The other thing I can do is I can actually delete a field. I could say, for instance, "This Stamp field doesn't interest me." So I'll push delete. Now that field is gone. It didn't actually delete the field from the MySQL table, though. It's still there. We've just told FileMaker, "You can ignore that field completely, ‘cause I'm not gonna use it within FileMaker."

If, later on, you decide you want that field back, you can just push the Sync button right here. FileMaker will go out, look at the MySQL table, and bring back all of the field definitions again. That Sync button also comes in handy if you or someone else adds new columns to the MySQL database. You can push the Sync button to get them to show up in FileMaker Pro.

Aside from those shadow table idiosyncrasies, these external SQL source tables, like this Comments table, work just like any other kind of table. You can do finds. You can sort. You can use them in portals and relationships, as we've seen here. So it's an incredibly powerful feature that allows almost seamless integration with backend databases.