When you were learning about exporting data above, you may have noticed the “Group by” list in the Specify Field Order for Export dialog box. Under normal circumstances, you see “(Unsorted)” in this list. But if you sort the records in the found set before choosing the Export Records command, you see instead a list of the fields in your sort order, each with a checkbox by its name. You can see this in action in Figure 17-2.

If you opt to group the data, you get just one record in the export file for each unique value in the “group by” field. For example, if you export 300 people records, grouped by state, you get one record for each state. Why would you ever want to do this? Because you can include summary fields in your export list and FileMaker properly summarizes all the records represented by each group. You could see, for example, how many people you have in each state.

You’re free to select as many fields as you want in the “group by” list. If you select more than one, you get a hierarchical list of records, similar to a sub-summary report (Chapter 6). For example, if you sort first by state, then by city, and turn on the checkbox next to both fields in the “group by” list, you get a list of states, and below each state, you see one record for each city in that state. Again, summary fields included in your export show proper totals for both the state as a whole, and each city.

Figure 17-2. When your data is sorted, FileMaker shows the sort fields in the “Group by” list. By turning on one or more of these checkboxes, you tell FileMaker you want to group the data in the export file

After wading through the technical details about FileMaker’s amazing flexibility in exporting to practically any format you might need, the following two export types should give you some breathing room. Forget about arcane formatting rules and settle into more familiar territory. Gone too, is the need to specify a field order for export. The File → Save/Send command lets you choose between an Excel spreadsheet and a PDF (Portable Document Format) document. Both commands grab whatever fields are on the current layout and whip them and their data into the shape you choose.

Excel

If people need to work with the data you send them, but they aren’t lucky enough to have FileMaker, you can create an Excel file for them, as shown in Figure 17-3. Once you’ve sent the file, they’re free to do anything to the data that Excel allows– make a table, create a chart, or run totals and summaries.

The Save option is a pop-up menu that lets you choose between the “Records being browsed” or the “Current Record” so you don’t even have to do a find if all you want to do is send the current record. Like the other formats under the Export menu, you have the option to “Automatically open file” and “Create email with file as attachment,” so it’s easy to check your work and create a quick email with the data your boss just asked to email her.

Figure 17-3. The Save Records As Excel window gives you the option of automatically opening the file you’re creating, so you don’t have to go rummaging around your hard drive looking for it. Just check “Automatically open file” and soon, you’ll be looking at your FileMaker data as an Excel spreadsheet.

If you click Options, you can set up some basic details for your new Excel file. For example, you can choose whether you want your FileMaker field names put in the first row of the spreadsheet. You can also type in a file name and a title, subject, and author (which appear in the Properties dialog box).

When you import data into a file that already has some records in it, the Import Action section of the Import Field Mapping dialog box (Section 17.3) gives you three ways to specify how you want to deal with that existing data. Normally, it starts out with the “Add new records” setting turned on, meaning that FileMaker simply adds imported records to your database. Sometimes, though, you want to update existing records instead. For example, suppose your database holds shipping rates for every state you ship to. When your freight company updates its rates, it sends you a new file with one record for each state, and the new rates in a Rate field. If you add these records to your database, you end up with two records for each state, which is probably not what you want. So FileMaker gives you two other choices that let you update records as you import.

Update matching records in found set

The “Update matching records in found set” works much the same way. When you import with this action, FileMaker updates data in existing records by copying it from the import file. This time, though, you get to tell FileMaker how to figure out which records in the import file match each record in the found set. You tell FileMaker this by specifying one or more matching fields, as described in Figure 17-8.

Figure 17-8. When you turn on “Update matching records in found set,” FileMaker lets you use a new symbol between source and target fields. In this picture, the Email Address field has an “=” symbol beside it. This symbol tells FileMaker to match records based on email address. In other words, when it imports the first record, it finds a record in the found set with the same email address. FileMaker then updates that record’s data based on the import file. If FileMaker doesn’t find a match, it skips the import record. It’s safest to use a key field for import matching.

To get the new symbol in the field mapping list, just click the spot between fields. FileMaker now toggles between the three possible symbols: Import, Don’t import, and Match. (If you forget what each symbol means, the “Field Mapping” area in the window reminds you.) Again, if you’d rather have FileMaker import every record in the import file, adding new records when no match is found, turn on “Add remaining data as new records.”

All the importing you’ve learned about so far assumes that your tables and fields are already defined. In other words, your target table is already in place when you choose your source file. But when you’re doing a big conversion job (say, moving a 35-file database system from FileMaker 6 format to FileMaker 7), it’ll take quite some time to create all those tables and fields. And even though you have access to the Define Database dialog box while you’re importing, this process isn’t quick or easy when you’ve got hundreds of fields coming in from dozens of files. FileMaker’s already thought of that, and offers to handle the tedious work of table and field creation for you.

Choose Import → File and select a data source. In the Import Field Mapping window, there’s a handy pop-up menu called Target. Click it, and you see the current table (remember that’s based on the layout that was active when you chose the Import command), all your other tables (grayed out, so you can’t choose them), and a very useful command, New Table (Data Source). The stuff in parentheses is the name of your data source and the name FileMaker gives the new table it creates for you. If you already have a table with the same name as your data source, FileMaker appends a number to the end of the new table’s name.

Choose New Table, then click Import. FileMaker creates a table and an appropriate set of fields for you, and then populates the new fields with data. You also get a simple form layout for your new table and a table occurrence on your relationships graph. You can treat this table just like one you created yourself. Start hooking it up to other table occurrences and you have a full-fledged related table.

When you let FileMaker create fields for you, it does its best to create the field types you want. For instance, if the data source is a FileMaker file, your new fields match the old file’s field names and field types. But if the source is a plain text file with no formatting information to go by, FileMaker doesn’t have any names to go by, so the new fields become text fields and get the prosaic names of f1, f2, f3, and so on. In either case, you should check the fields in your new table to make sure you get the names and field types you want. You have to manually define any calculations you need.

Finally, it pays off to think about the order in which you import your files. FileMaker can handle data from related files (lookup fields, and calculations based on relationships) for you if you import the file that contains the supporting records first, and then the file containing the dependent records. If you get the order mixed up, or if your relationships are so complicated that you can’t import files in a perfectly clean order, then plan to spend some time cleaning up broken field references after your import.

If using the import button is just too much trouble, or you don’t need much control over how it’s handled, FileMaker has an even simpler way of creating a FileMaker file from another data source, like an Excel file or a text file. Just choose File → Open and select the file you want to convert. (On the Mac, you can convert files by dragging them onto FileMaker’s application icon.)

If FileMaker finds data with a first row that looks like field names, it asks you if you want to use those when FileMaker defines fields. If it can’t find anything that looks like field names, you get those old standbys, f1, f2, f3. In addition to a single table and the appropriate number of fields (complete with data, of course), you get two very plain layouts. One is a generic form layout, showing one record at the time, and the other is a simple columnar list.

Use this same process (the Open file command), if you’re converting files from older versions of FileMaker to the .fp7 format. Converting old files is a huge topic and most people find that conversion requires a certain amount of retrofitting, either to make scripts and other things that break during conversion whole again or to take full advantage of the .fp7 multiple-table-per-file power. (FileMaker offers two excellent white papers on this subject for download at http://www.filemaker.com/support/whitepapers.html. Look under Migration Topics.)

The File → Import → File command shows an Open File dialog box. Select any file that matches one of the export formats explained earlier in this chapter. You see your old friend, the Import Field Mapping dialog box (Figure 17-5). Match your source to your target, and away you go.

You can also use a similar command when you have to move data from one table to another within the same file. Go to your target layout and choose File → Import Records → File. In the dialog box, choose the database you’re in, and you see the Import Field Mapping dialog box. Select the table that’s your data source from the Source pop-up menu and you’re ready to go.

Using this command, you can pick any folder, and FileMaker imports the contents of each appropriate file in that folder. It creates one record for each file it imports, and puts the file into the field you specify. In other words, if you have a folder full of letters you’ve written in a folder, you can import them into a Letters database using this command. The complete text of each letter would go in a field, with one field per letter. (Remember, though, that FileMaker only supports pictures, movies, and plain text files when importing. If your letters are in Microsoft Word format, for example, you’re out of luck.)

Choosing File → Import → Folder summons the Folder of Files Import Options dialog box shown in Figure 17-9.

Figure 17-9. This window is what you see when you choose the import folder command. In the top part of the window, you get to choose which folder to import (click Specify). In the bottom half, you decide what kind of files you’re interested in.

Normally, FileMaker finds only files directly inside the folder you pick—it ignores any other folders contained inside. You have to turn on “Include all enclosed folders” to make FileMaker look inside those folders, too. With this option turned on, it digs as deep as necessary to find every file.

Once you’ve picked a folder, you get to decide what kind of files to import. You have only two choices: “Picture and movie files” and “Text files.” In the first case, FileMaker ignores every file that isn’t a supported picture or movie type. You get to decide whether the files themselves are inserted in the container field, or just references to them. If you choose the “Text files” option instead, it seeks out only plain text files.

Importing a folder of pictures or movies

When you choose the “Pictures and movie files” option and click Continue, you might be in for a bit of a wait. Depending on the number of files FileMaker has to look through, you may see a progress dialog box for as long as several minutes. When the import is complete, you see the now-familiar Import Field Mapping dialog box. But the list of source fields looks entirely unfamiliar—in a good way. As outlined in Figure 17-10, FileMaker translates the file information into logical field types, perfect for database use.

Figure 17-10. When you import a folder full of pictures, FileMaker practically reads your mind. It not only lets you dump the picture into a container field, but it also offers you the chance to put the file name and full path into text fields. Best of all, FileMaker can shrink the picture down to a thumbnail and put that into a container field too, which saves space by reducing size and resolution. The thumbnails appear even when the original images aren’t available (if a network drive is unavailable, for instance). And the thumbnail’s reduced in size and resolution, so it doesn’t take up much space.

If you’re using Mac OS X, the File → Import Records menu has a Digital Camera option. Unfortunately, this option relies on a feature unique to Mac OS X, so you can’t import directly from a camera on Windows. You have to download the images to your computer first, and then import them. (There’s a workaround, if you can set up your camera to show up as a USB disk on Windows, In that case, use the Import → Folder command to import images directly from the camera.)

When you first plug your camera into your Mac, chances are iPhoto launches and prepares to import the images. To avoid any potential confusion, quit iPhoto first (or be careful not to click Import in iPhoto while FileMaker’s working with the camera).

With the camera connected, choose File → Import Records → Digital Camera. The verbosely titled FileMaker Pro Photo Import Options dialog box appears (Figure 17-11).

Figure 17-11. This dialog box appears when you import from a digital camera in Mac OS X. The device pop-up menu lets you pick which camera to import from (on the off chance you have several connected). You also get to decide which images to import, where to put them (more on these options in a moment), and whether or not you want to store just references to the pictures.

If you want to import everything on the camera, turn on “All images.” On the other hand, if you just want, for example, the last five shots you took, turn on the last radio button—“The last [blank] images”—and put the right number in its box. Finally, you can choose “Some images” and click Specify to see a list of image thumbnails to choose from.

Even if you want to import all the images, the “Some images” choice can come in handy. It’s the only place you can rotate the images before you import them. There’s no direct way to rotate an image once it’s in the container field. You have to export it, rotate it in another program, and then insert it again.

Even if you’re importing the images directly into FileMaker (rather than just a reference), it still puts them in the folder you specify in the “Download to” pop-up menu. If you want to, you can simply delete them when you’re done. (If you’re storing references, though, the folder you pick becomes very important. If you import the pictures and then move them to a different folder, the references are wrong, and the pictures don’t display in FileMaker.)

When you’re through making choices, click Continue. The Import Field Mapping window that appears next is loaded with source fields this time. FileMaker lets you import loads of image data along with the pictures, including dimensions, resolution, shot date and time, and all that photographer-speak stuff you see when you accidentally put your camera in advanced mode.

At this point you’re in familiar territory. Just match up the fields and click Import. Be prepared for a wait as it takes FileMaker longer to import full-color digital pictures than, say, zip codes.

Because there are so many programs and data formats out there, the World Wide Web Consortium (W3C) created the XML format to make data exchange more predictable. FileMaker uses a special subset of XML’s code, called FMPXMLRESULT, to facilitate import. If your data source was created by another FileMaker Pro database, it already uses FMPXMLRESULT, and you can import that data straight up, no chaser. But if the XML document doesn’t use FileMaker’s Document Type Definition (DTD), you’ll need an Extensible StyleSheet Language (XSLT) document to tell FileMaker how to make the XML file work with FileMaker.

As you can see in Figure 17-12, the Specify XML and XSL Options window is where you tell FileMaker whether or not your XML Data Source needs a style sheet, and if it needs one, where to find it.

When you’re finished choosing XML and XSL options, click Continue. FileMaker processes the XML and XSL (which may take a few minutes), and then shows you the Import Field Mapping dialog box. From there, you can proceed like any import, as described earlier in this chapter.

Figure 17-12. When you import from an XML Data Source, FileMaker first asks you where to get the data from, and how to process it. You can instruct FileMaker to look in any XML file on your computer, or to fetch the XML data from a Web server (see the box on Section 17.4.4.1).

XML and XSLT are complex languages and this book can’t cover it in full. If you need to master XSLT, you can get whole books devoted to the subject. You can also find dozens of premade style sheets on FileMaker’s Web site at www.filemaker.com/technologies/xslt_library.html.

The last import data source is called ODBC. This data source is a popular standard to allow programs to access information stored in database systems. For instance, if your company has an Oracle, Sybase, or Microsoft SQL Server database to manage orders, you can extract data directly from that database and import it into your FileMaker Pro database (perhaps you want to make your own reports with FileMaker).

ODBC is the most complex import data source to set up. It’s a two-step process:

Once you have a driver installed and a data source set up, you can use the File → Import Records → ODBC Data Source command. When you do, you see the Select ODBC Data Source window (Figure 17-13).

When you click Continue in this window, you probably need to enter a user name and password for the database you’re connecting to. You need to get this information from the database administrator, unless you created it in the ODBC software yourself.

Next, FileMaker shows the SQL Query Builder dialog box (Figure 17-14). When you extract data from most databases (FileMaker being the notable exception), you have to use a special programming language called Structured Query Language, or SQL (often pronounced sequel). Writing an SQL query (or program) is a complicated affair. Luckily, in most cases, FileMaker can do it for you—you just make the right choices. (For more detail, see the box on Section 17.5.)

Figure 17-13. When you tell FileMaker you want to import from an ODBC data source, it first asks you which source to use. This window shows every data source you’ve defined in your ODBC system. You just highlight the one you want and click Continue.

Figure 17-14. In the SQL Query Builder dialog box’s Select tab, the Tables list shows every table available from the database you’re connecting to. When you select a table, FileMaker lists all its fields (called columns in most database systems) in the Columns list. Select a field and click “Insert into SQL Query” to include that field in the data you’re importing. If you’re an SQL pro, you can simply type in the SQL Query box instead.

When you’re all finished building your query, click Execute. FileMaker performs the query, gathers the data from the data source, and shows the same Import Field Mapping dialog box you always see when importing data. If you find yourself doing a lot of ODBC imports, you might be well served by a good book on SQL.

Although the query builder supports only a little SQL, you can use any SQL commands supported by your database server if you type the query directly. And SQL can do a lot.

This script step has three options. First, you get to specify the data source to import from. Your choices match those in the File → Import Records menu: File, Folder, Digital Camera (Mac OS X only), XML Data, and ODBC Data. Whichever option you choose, FileMaker asks you for more information (which file to import, or which ODBC data source to use, for instance). When specifying a file, you get the standard path list dialog box. In other words, you can specify several paths if you want; FileMaker imports the first one it finds. (If you don’t specify a source, your users have to do it as they run the script, in a potentially confusing series of dialog boxes. Since you’re presumably providing a script to make things easier for people, it’s best to store source files in a safe place and have the script escort your users to them.)

Once you’ve specified the source, you can turn on “Specify import order” to record the import field mapping, and other import options. Finally, you can turn on “Perform without dialog” if you want FileMaker to import the data directly, with no input from your users. If you leave this option off, FileMaker displays the Import Field Mapping dialog box when the script runs, so folks can make changes to any field mapping you specified.

The Export Records script step offers similar options. You can specify the output file and export order, and you can choose “Perform without dialog” if you don’t want your users to see the export dialog box.

When you specify the output file, you may be surprised to see an Output File Path List. In other words, FileMaker lets you specify more than one file. This choice doesn’t mean FileMaker exports more than one file, though. Instead, it exports to the first file path that is valid. If the first path in the list includes a folder name that doesn’t exist, for example, FileMaker skips it and tries the next one.