Chapter 17. Exchanging Data

When it comes to exchanging data across platforms and enterprise-scale databases, FileMaker 6’s support for Extensible Markup Language (XML) represents a great leap forward. Because it uses non-proprietary, standards-based formats, XML has been widely embraced by Information Technology managers since its birth in 1998. In contrast to the often rigid requirements of Open Database Connectivity (ODBC), which is covered starting on page 280, XML is inherently flexible.

The really exciting thing about XML, however, is that you don’t have to be a tech guru to benefit from FileMaker’s XML support. In more and more corporate-based databases, the XML prep work has already been done behind the scenes. That means you can start using FileMaker’s XML features to access and share data previously available only through complicated, customized database applications. FileMaker also has a free online XML exchange (www.filemaker.com/xml/xslt_library.html) that offers an ever-growing collection of downloadable templates (Figure 17.1). The templates—from package trackers to FileMaker-to-Quark converters to iTunes exporters—are not only useful in making XML transformations but can help you understand XML’s potential (Figure 17.2).

FileMaker’s free online exchange offers an ever-growing collection of XML-based templates.

Figure 17.1. FileMaker’s free online exchange offers an ever-growing collection of XML-based templates.

Slick: One template quickly transforms a Mac’s iTunes library into a FileMaker database.

Figure 17.2. Slick: One template quickly transforms a Mac’s iTunes library into a FileMaker database.

If you understand the Web’s underlying language, HTML (HyperText Markup Language), XML will seem familiar (Figure 17.3). In a nutshell, XML provides a way to simultaneously describe and structure data. By separating the content and the instructions on how it should be processed, XML and its companion language, XSL (Extensible Style Language), can transform data into a variety of formats: HTML, PDFs (Portable Document Format), email, comma-separated text, plain text, and even other XML documents. Using HTTP (HyperText Transfer Protocol), XML can, for example, query Web-based sales databases and snatch just the bits of data needed to update a local order form. In short, XML can bridge virtually any data chasm, and that’s why it is transforming database communication.

If you understand the Web’s underlying language, HTML (HyperText Markup Language), XML will seem familiar.

Figure 17.3. If you understand the Web’s underlying language, HTML (HyperText Markup Language), XML will seem familiar.

If you want to learn more and take full advantage of XML capabilities now built into FileMaker, take a look at two great books on the topic: XML for the World Wide Web: Visual QuickStart Guide by Elizabeth Castro (Peachpit Press, 2000), and Learning XML by Erik Ray (O’Reilly, 2001). Castro’s book in particular does a nice job explaining how the various coding languages can work together.

Exchanging Data Using XML

With FileMaker 6 you can use XML in several ways. You can import XML-based data into an existing FileMaker file or you can convert XML data into a new FileMaker file. You also can export a FileMaker database in an XML format that other XML-aware applications can then read.

To import XML data into an existing FileMaker database:

  1. Open the FileMaker database into which you want to import data (To import XML data into an existing FileMaker database: in Windows/To import XML data into an existing FileMaker database:To import XML data into an existing FileMaker database: on the Mac). If you’ll be replacing particular records, use FileMaker’s Find, Omit, and Sort commands to expand or narrow the Found Set to only the records you’ll be replacing.

  2. Choose File > Import Records > XML Source (Figure 17.4).

    To import XML data into FileMaker, choose File > Import Records > XML Source.

    Figure 17.4. To import XML data into FileMaker, choose File > Import Records > XML Source.

  3. When the Specify XML and XSL Options dialog box appears, you have the option of locating your data source and style sheet in a local File or on a Web server with an HTTP request. In this example, choose the top File radio button (Figure 17.5), which will automatically activate the Open dialog box.

    When the Specify XML and XSL Options dialog box appears, choose the top File radio button.

    Figure 17.5. When the Specify XML and XSL Options dialog box appears, choose the top File radio button.

  4. When the Open dialog box appears, navigate to the folder where the XML source file is stored, select it, and click Open. In this example, navigate to the file msdso_attrib.xml inside these folders: FileMakerFileMaker Pro 6ExamplesXML ExamplesImportATTRIB_MSDSO (on Macs the path is FileMaker Pro 6FileMaker ExamplesXML ExamplesImportATTRIB_MSDSO) (Figure 17.6).

    Once you navigate to the folder where the XML source file is stored, select it, and click Open.

    Figure 17.6. Once you navigate to the folder where the XML source file is stored, select it, and click Open.

  5. When the Specify XML and XSL Options dialog box reappears, the file chosen in step 4 will be listed next to the top Specify button. Now choose the Use XSL style sheet checkbox and choose the second File radio button (Figure 17.7), which will automatically activate the Open dialog box.

    When the Specify XML and XSL Options dialog box reappears, choose the Use XSL style sheet checkbox and choose the second File radio button.

    Figure 17.7. When the Specify XML and XSL Options dialog box reappears, choose the Use XSL style sheet checkbox and choose the second File radio button.

  6. Navigate to the folder where the XSL file is stored, select it, and click Open. In this example, navigate to the file msdso_attrib.xsl, which is stored in the same folder listed in step 4 (Figure 17.8).

    Navigate to the folder where the XSL file is stored, select it, and click Open.

    Figure 17.8. Navigate to the folder where the XSL file is stored, select it, and click Open.

  7. When the Specify XML and XSL Options dialog box reappears, both the XML and XSL files will be listed next to their respective Specify buttons. Click OK (Figure 17.9).

    Once you’ve selected the XML file and XSL style sheet, click OK.

    Figure 17.9. Once you’ve selected the XML file and XSL style sheet, click OK.

  8. When the Import Field Mapping dialog box appears, check to see if the data listed in the left-side window matches up with the field names on the right side (Figure 17.10). If not, click the View by pop-up menu and choose matching names or click the name of the mismatched field and drag it up or down to match up with the correct left-side data. Use the Scan Data << and >> buttons to double-check the data-field matchups. (Also see the first Tip.)

    Before clicking Import, make sure the left-side data in the Import Field Mapping dialog box matches up with the right-side field names.

    Figure 17.10. Before clicking Import, make sure the left-side data in the Import Field Mapping dialog box matches up with the right-side field names.

  9. To activate individual field imports, click the center column to turn the slashed zero into a single-headed arrow to import data or a double-headed arrow to match fields. For details, see Table 7.2 on page 87.

  10. Once you’ve tweaked the source-to-database mapping to your satisfaction, click Import. If you’re importing into an existing FileMaker database, the new records will appear in Browse mode (Figure 17.11).

    If you’re importing into an existing FileMaker database, the new records will appear in Browse mode.

    Figure 17.11. If you’re importing into an existing FileMaker database, the new records will appear in Browse mode.

If you’re importing into an existing FileMaker database, the new records will appear in Browse mode. Tips

  • In many cases, you will not necessarily know in advance about all the data contained in an XML source file. In such cases, there may be data listed in the left side of the Import Field Mapping dialog box for which you have no appropriate fields listed in the right-side window. No problem: just click the lower right Define Fields button, create the needed fields, and then match them up following step 8.

  • If you try to import an XML file that does not use FileMaker’s underlying XML grammar or choose the wrong XSL file to translate it, FileMaker will display a dialog box alerting you that it cannot properly translate or parse the file (Figure 17.12). Use the Specify buttons in the Specify XML and XSL Options dialog box to make sure you’ve selected the right XSL file to translate the specified XML file.

    FileMaker will display an alert if you try to import an XML or XSL file it cannot translate.

    Figure 17.12. FileMaker will display an alert if you try to import an XML or XSL file it cannot translate.

  • While this example uses the File option, you also can use the HTTP option to retrieve XML data from a remote database on the Web.

To import XML data into a new FileMaker database:

  1. Choose File > Open (To import XML data into a new FileMaker database: in Windows/To import XML data into a new FileMaker database:To import XML data into a new FileMaker database: on the Mac) and when the Open File dialog box appears, use the Show pop-up menu to select XML Source.

  2. When the Specify XML and XSL Options dialog box appears, choose the top File radio button (Figure 17.5), which will immediately take you to the Open dialog box.

  3. Navigate to the folder where the XML source file is stored, select it, and click Open (Figure 17.13). In this example, navigate to the file msdso_elem.xml inside these folders: FileMakerFileMaker Pro 6ExamplesXML ExamplesImportMSDSO_ELEM (on Macs the path is FileMaker Pro 6FileMaker ExamplesXML ExamplesImportMSDSO_ELEM).

    Navigate to where the XML source file is stored, select it, and click Open.

    Figure 17.13. Navigate to where the XML source file is stored, select it, and click Open.

  4. When the Specify XML and XSL Options dialog box reappears, the file chosen in step 3 will be listed next to the top Specify button. Now choose the Use XSL style sheet checkbox and choose the second File radio button.

  5. Navigate to the folder where the XSL file is stored, select it, and click Open. In this example, navigate to the file msdso_elem.xslt, which is stored in the same folder listed in step 3.

  6. When the Specify XML and XSL Options dialog box reappears, both the XML and XSL files will be listed next to their respective Specify buttons. Click OK (Figure 17.14).

    Once you’ve selected the XML file and XSL style sheet, click OK.

    Figure 17.14. Once you’ve selected the XML file and XSL style sheet, click OK.

  7. When the Name converted file dialog box appears, FileMaker offers to rename the converted file or let you enter another name. Enter a name, navigate to where you want the file stored, and click Save (Figure 17.15). The imported XML data will appear in a new FileMaker database (Figure 17.16).

    Enter a name for the converted file, navigate to where you want it stored, and click Save.

    Figure 17.15. Enter a name for the converted file, navigate to where you want it stored, and click Save.

    The imported XML data will appear in a new FileMaker database.

    Figure 17.16. The imported XML data will appear in a new FileMaker database.

To export FileMaker data as XML:

  1. Before you export the FileMaker database, use FileMaker’s Find and Omit commands to expand or narrow the Found Set to just the records you’ll want. Use FileMaker’s Sort command to then put the records in the order you want them to appear within the receiving document.

  2. In Browse mode (To export FileMaker data as XML: in Windows/To export FileMaker data as XML:To export FileMaker data as XML: on the Mac), choose File > Export Records (Figure 17.17).

    To export FileMaker data as XML, choose File > Export Records.

    Figure 17.17. To export FileMaker data as XML, choose File > Export Records.

  3. When the Export Records to File dialog box appears, type in a name for the file (including the .xml suffix) and navigate to the folder where you want to store it. Use the Type pop-up menu to select XML as the file format and click Save (Figure 17.18).

    Use the Type pop-up menu to select XML as the file format, include .xml in the file name, and navigate to where you want to save the file.

    Figure 17.18. Use the Type pop-up menu to select XML as the file format, include .xml in the file name, and navigate to where you want to save the file.

  4. When the Specify XML and XSL Options dialog box appears, use the drop-down menu to choose the appropriate grammar: FMPXMLRESULT (the default) or FMPDSORESULT (Figure 17.19). (See the first Tip on choosing a grammar.)

    When the Specify XML and XSL Options dialog box appears, use the drop-down menu to choose the appropriate grammar.

    Figure 17.19. When the Specify XML and XSL Options dialog box appears, use the drop-down menu to choose the appropriate grammar.

  5. Check the Use XSL style sheet box, and choose the File radio button, which will trigger the display of the Open dialog box. Navigate to the folder containing the appropriate XSL file for translating the file you’re exporting, select it, and click Open. After the Specify XML and XSL Options dialog box reappears with the XSL file listed next to the Specify button, click OK (Figure 17.20).

    Once you’ve picked the Grammar and specified the XSL style sheet, click OK.

    Figure 17.20. Once you’ve picked the Grammar and specified the XSL style sheet, click OK.

  6. The Specify Field Order for Export dialog box will appear (Figure 17.21). Select fields in the left-side list you want to export, and click the center Move button to place them in the right-side list of fields to be exported. To export all the fields, click Move All. If you change your mind and want to remove a field from the right-side list, select it and click Clear.

    Use the Specify Field Order for Export dialog box to pick which fields you want to export.

    Figure 17.21. Use the Specify Field Order for Export dialog box to pick which fields you want to export.

  7. Once you’ve moved all the fields you want to export into the right-side list, click and drag the double-arrows next to each field to rearrange the list’s export order.

  8. Choose one of two format options listed in the dialog box’s lower-left corner. Use the Format output using current layout button to export, for example, commas and dollar signs along with the numbers in any fields you’ve formatted that way. Use Don’t format output if you just want to export the raw, unformatted data.

  9. Click Export and an Export dialog box with a horizontal status bar will appear briefly (Figure 17.22). When it disappears, FileMaker will have exported the data into an XML-based file that can be opened by other XML-aware applications.

    When the export begins, the Export dialog box with a horizontal status bar will appear briefly.

    Figure 17.22. When the export begins, the Export dialog box with a horizontal status bar will appear briefly.

When the export begins, the Export dialog box with a horizontal status bar will appear briefly. Tip

  • In step 4, in most cases you should stick with the default grammar of FMPXMLRESULT since it will pass on metadata about the source file’s records, field types, and field formats. The grammar also is compatible with Microsoft Internet Explorer’s own XML format. Choose FMPDSORESULT if you plan on using CSS (Cascading Style Sheets) with the resulting XML file.

Exchanging Data Using ODBC

Open Database Connectivity (ODBC) allows FileMaker to exchange data with giant enterprise-level databases such as those created by Oracle and Microsoft SQL Server. On Macs running OS 8.1–9.1 and Windows machines, the ODBC process is roughly the same: You turn on FileMaker’s Data Access Companions, configure the ODBC Control Panel for the particular data source, connect to the data file, and then construct a query (search) of that data. There are a lot of steps involved, but you’ll find the process pretty straightforward once you walk through it. If you’re not familiar with Structured Query Language (SQL)—the programming language that controls corporate-level database management systems—find someone within your organization to lend you a hand building those initial queries.

As with version 5.5, FileMaker 6 does not fully support ODBC on Macs running OS X. But with version 6’s new XML support this isn’t the roadblock it might seem. In fact, using FileMaker’s XML import-export features often is actually easier than working with ODBC. For more information, see Exchanging Data Using XML, on page 272.

To turn on the Data Access Companions:

  1. Choose Edit > Preferences > Application (Figure 17.23).

    To turn on the Data Access Companions, choose Edit > Preferences > Application.

    Figure 17.23. To turn on the Data Access Companions, choose Edit > Preferences > Application.

  2. When the Application Preferences dialog box appears, click the Plug-Ins tab (Figure 17.24).

    Click the Plug-Ins tab and check Local... or Remote... depending on where the ODBC-enabled database resides.

    Figure 17.24. Click the Plug-Ins tab and check Local... or Remote... depending on where the ODBC-enabled database resides.

  3. Check Local Data Access Companion if you want to share FileMaker data with an ODBC-enabled database on the same computer. If you want to share FileMaker data across a TCP/IP network, check the third choice, Remote Data Access Companion.

  4. Click OK and the Application Preferences dialog box will close.

  5. Now open the FileMaker file you want to share and choose File > Sharing (Figure 17.25).

    Open the FileMaker file you want to share, then choose File > Sharing.

    Figure 17.25. Open the FileMaker file you want to share, then choose File > Sharing.

  6. When the File Sharing dialog box appears (Figure 17.26), first choose Multi-User in the FileMaker Network Sharing panel. Within the Companion Sharing panel, make the same choice you made in step 3: Local Data Access Companion to share the file with an ODBC-enabled database on the same computer; Remote Data Access Companion to share the file across a TCP/IP network.

    Choose Multi-User to share a file, then check Local... or Remote... in the bottom panel to match your choice in Figure 17.24.

    Figure 17.26. Choose Multi-User to share a file, then check Local... or Remote... in the bottom panel to match your choice in Figure 17.24.

  7. Click OK and the File Sharing dialog box will close. The FileMaker file can now be reached by an ODBC-enabled database. For more information, see To use FileMaker data from an ODBC application on the next page.

Choose Multi-User to share a file, then check Local... or Remote... in the bottom panel to match your choice in Figure 17.24. Tip

  • To control access to the FileMaker data across the network, it’s best to assign a password to the shared file. For more information, see Defining Passwords on page 262.

Using FileMaker Data within an ODBC Application

Once you’ve turned on the Data Access Companions (explained on the previous page), your FileMaker data can be used by any ODBC-compliant application. The exact steps for tapping the FileMaker file will vary depending on which ODBC application you’re using. The FileMaker ODBC driver must be installed on the computer that contains the ODBC client application. (See your network administrator for help on this.) Once the ODBC control panel has been installed, you’ll need to configure the control panel by naming and locating the FileMaker file you’ll be using from within the ODBC application. From there, it’s just a matter of building queries for the FileMaker data using Structured Query Language (SQL) within your ODBC application.

To use FileMaker data from an ODBC application:

  1. You’ll need to configure the ODBC:

    To use FileMaker data from an ODBC application: Choose Start > Settings > Control Panel. In pre-Windows 2000 machines, double-click ODBC Data Sources (32bit) when the Control Panel window opens. On machines running Windows 2000 and Windows XP, you’ll need to go one level deeper by also opening the Administrative Tools folder and choosing Data Sources (ODBC).

    To use FileMaker data from an ODBC application: Choose System Folder > Control Panels > ODBC Setup PPC if you’re using a pre-OS X Mac. (If you’re using OS X, use FileMaker’s XML features to access the data. For more information, see Exchanging Data Using XML, on page 272.)

  2. When the ODBC Data Source Administrator dialog box appears, make sure the User DSN tab is active, and click Add (Figure 17.27).

    To create a new ODBC data source, make sure the User DSN tab is active, and click Add.

    Figure 17.27. To create a new ODBC data source, make sure the User DSN tab is active, and click Add.

  3. When the Create New Data Source dialog box appears, select the FileMaker Pro driver (Windows) (Figure 17.28) or the ODBC 3.11 FileMaker Pro PPC driver (Mac) (Figure 17.29), and click Finish.

    When the Create New Data Source dialog box appears, select the FileMaker Pro driver, and click Finish.

    Figure 17.28. When the Create New Data Source dialog box appears, select the FileMaker Pro driver, and click Finish. When the Create New Data Source dialog box appears, select the FileMaker Pro driver, and click Finish.

    When the Create New Data Source dialog box appears, select the ODBC 3.11 FileMaker Pro PPC driver, and click Finish.

    Figure 17.29. When the Create New Data Source dialog box appears, select the ODBC 3.11 FileMaker Pro PPC driver, and click Finish. When the Create New Data Source dialog box appears, select the ODBC 3.11 FileMaker Pro PPC driver, and click Finish.

  4. Make sure the General tab is activated when the driver setup dialog box appears (Figure 17.30), type a distinctive name into the Data Source Name text box and a Description if it will help users recognize the file.

    Make sure the General tab is activated when the driver setup dialog box appears. If the database is not on your local network, check Use Remote Connection and enter an IP number in Server Address.

    Figure 17.30. Make sure the General tab is activated when the driver setup dialog box appears. If the database is not on your local network, check Use Remote Connection and enter an IP number in Server Address.

  5. If you’ll be using a local file, click OK and you’re done. If you’ll be using a remote file not on the local server, check Use Remote Connection, enter the IP number in the Server Address text box, and then click OK.

  6. When the ODBC Data Source Administrator dialog box reappears, the newly named FileMaker data source will appear in the User Data Sources list (Figure 17.31). Click OK to close the dialog box. You’re now ready to build your SQL query within your ODBC application and execute the query of the FileMaker file.

    Once added, the newly named FileMaker data source appears in the User Data Sources list.

    Figure 17.31. Once added, the newly named FileMaker data source appears in the User Data Sources list.

Once added, the newly named FileMaker data source appears in the User Data Sources list. Tips

  • If your ODBC application requires it, you can set such items as a column’s maximum text length, the number of rows requested, and multi-threading by clicking the Advanced tab in step 4 (Figure 17.30).

  • To see which drivers you already have installed, in step 2 click the Drivers (Windows) or ODBC Drivers (Mac) tab in the ODBC Data Source Administrator dialog box (Figure 17.32).

    To see which drivers you already have installed, just click the Drivers (Windows) or ODBC Drivers (Mac) tab in the ODBC Data Source Administrator dialog box.

    Figure 17.32. To see which drivers you already have installed, just click the Drivers (Windows) or ODBC Drivers (Mac) tab in the ODBC Data Source Administrator dialog box.

Importing ODBC Data into FileMaker

FileMaker’s ODBC capabilities create a two-way street: ODBC-based applications can read FileMaker data, but you also can use FileMaker’s simpler interface to tap corporate databases created by such programs as Oracle.

Importing the data involves three distinct procedures: (1) using the ODBC control panel to specify the name and location of the ODBC source data, (2) connecting to the source data from within FileMaker, and (3) building a database query within FileMaker.

To import ODBC data into FileMaker:

  1. Open the ODBC control panel:

    To import ODBC data into FileMaker: Choose Start > Settings > Control Panel. In pre-Windows 2000 machines, double-click ODBC Data Sources (32bit) when the Control Panel window opens. On machines running Windows 2000 and Windows XP, you’ll need to go one level deeper by also opening the Administrative Tools folder and choosing Data Sources (ODBC).

    To import ODBC data into FileMaker: Choose System Folder > Control Panels > ODBC Setup PPC if you’re using a pre-OS X Mac. (If you’re using OS X, use FileMaker’s XML features to access the data. For more information, see Exchanging Data Using XML, on page 272.)

  2. When the ODBC Data Source Administrator dialog box appears (Figure 17.27), make sure the User DSN tab is active, and click Add if you’re setting up a new data source.

    If you’re going to use an existing data source and need to change its settings, select it in the list, click Configure, and make the changes in the dialog box that appears.

  3. When the Create New Data Source dialog box appears, select the appropriate ODBC driver for the data source you’ll be importing, and click Finish (Figure 17.33).

    Select the appropriate ODBC driver for your data source in the Create New Data Source dialog box, and click Finish.

    Figure 17.33. Select the appropriate ODBC driver for your data source in the Create New Data Source dialog box, and click Finish.

  4. The setup dialog box that appears (Figure 17.34) will vary, depending on your choice in step 3. Type in a Data Source Name, a Description if you want, and the Server Name where the data source resides. When you’re done, click OK.

    Type in a Data Source Name, a Description, and the Server Name where the data source resides. The dialog box will vary depending on your data source.

    Figure 17.34. Type in a Data Source Name, a Description, and the Server Name where the data source resides. The dialog box will vary depending on your data source.

    • Type in a Data Source Name, a Description, and the Server Name where the data source resides. The dialog box will vary depending on your data source. Click Select Directory to navigate your way to the Database Directory where the file that you want to use resides (Figure 17.35).

    Click Select Directory to navigate your way to the desired Database Directory.

    Figure 17.35. Click Select Directory to navigate your way to the desired Database Directory. Click Select Directory to navigate your way to the desired Database Directory.

  5. When the ODBC Data Source Administrator dialog box reappears, the newly named data source will appear in the User Data Sources list (Figure 17.36). Click OK to close the dialog box. You’re now ready to import the data source into FileMaker. For more information, see To connect to and query an ODBC source data on the next page.

    When the ODBC Data Source Administrator dialog box reappears, the newly named data source is listed under User Data Sources.

    Figure 17.36. When the ODBC Data Source Administrator dialog box reappears, the newly named data source is listed under User Data Sources.

When the ODBC Data Source Administrator dialog box reappears, the newly named data source is listed under User Data Sources. Tip

  • If your ODBC application requires it, you can set such items as a column’s maximum text length, the number of rows requested, and multi-threading by clicking the Advanced tab in step 4 (Figure 17.34).

To connect to and query an ODBC data source:

  1. Open the FileMaker file into which you’ll be importing the source data and define fields for the data you expect to import. For more information on defining fields, see page 107.

  2. Make sure you’re in Browse mode (To connect to and query an ODBC data source: in Windows, To connect to and query an ODBC data source:To connect to and query an ODBC data source: on the Mac), then choose File > Import Records > ODBC Source (Figure 17.37).

    To tap an ODBC data source, begin by choosing File > Import Records > ODBC Source.

    Figure 17.37. To tap an ODBC data source, begin by choosing File > Import Records > ODBC Source.

  3. When the Open File dialog box appears, use the Files of type (Windows) or Show (Mac) drop-down menu to select ODBC Data Sources (Windows) or ODBC (Mac) (Figures 17.38 and 17.39).

    When the Open File dialog box appears, use the Files of type drop-down menu to select ODBC Data Sources.

    Figure 17.38. When the Open File dialog box appears, use the Files of type drop-down menu to select ODBC Data Sources. When the Open File dialog box appears, use the Files of type drop-down menu to select ODBC Data Sources.

    When the Open File dialog box appears, use the Show drop-down menu to select ODBC.

    Figure 17.39. When the Open File dialog box appears, use the Show drop-down menu to select ODBC. When the Open File dialog box appears, use the Show drop-down menu to select ODBC.

  4. When the Select ODBC Data Source dialog box appears (Figures 17.40 and 17.41), select a data source (such as the one you first configured in step 4 of To import ODBC data into FileMaker on page 284), and click OK.

    When the Select ODBC Data Source dialog box appears, select a data source and click OK.

    Figure 17.40. When the Select ODBC Data Source dialog box appears, select a data source and click OK. When the Select ODBC Data Source dialog box appears, select a data source and click OK.

    When the Select ODBC Data Source dialog box appears, select a data source and click OK.

    Figure 17.41. When the Select ODBC Data Source dialog box appears, select a data source and click OK. When the Select ODBC Data Source dialog box appears, select a data source and click OK.

  5. When the password dialog box appears, enter the required password or, if none was created, click OK (Figure 17.42).

    Enter a name and password for the ODBC database or, if none exists, just click OK.

    Figure 17.42. Enter a name and password for the ODBC database or, if none exists, just click OK.

  6. When the Specify ODBC SQL Query dialog box appears, the SELECT tab already will be activated (Figure 17.43). Within the Tables list, click the table you want to import, then click a choice in the Columns list. Click Insert into SQL Query and the request will appear in the SQL Query list.

    Use the SELECT tab to begin building your query (search) of the ODBC database.

    Figure 17.43. Use the SELECT tab to begin building your query (search) of the ODBC database.

  7. If you want to fine-tune which data will be imported, click the WHERE tab (Figure 17.44). Use the drop-down menus (similar to creating a Find request) to narrow which records and values you want to import. Again, click Insert into SQL Query to add the criteria to your query.

    In much the same as you would create a Find request, use the WHERE tab to fine-tune which data will be imported.

    Figure 17.44. In much the same as you would create a Find request, use the WHERE tab to fine-tune which data will be imported.

  8. If you want to sort the records before you import them, click the ORDER BY tab (Figure 17.45). As you select items in the Columns list, click Move to add them to the Order By list. Choose Ascending or Descending as needed for each item added to the Order By list. Click Insert into SQL Query to add the ordering criteria to your query.

    To sort how the records will be imported, use the ORDER BY tab.

    Figure 17.45. To sort how the records will be imported, use the ORDER BY tab.

  9. Click Execute (Windows) or OK (Mac) to start the query.

  10. When the Import Field Mapping dialog box appears (Figure 17.46), use the Scan Data arrows to make sure the data and fields match up properly. If necessary, reorder the fields in your FileMaker database. For more information on import mapping, see page 86.

    Once you begin the query, the Import Field Mapping dialog box will appear. Make sure the data and fields match up before you click Import.

    Figure 17.46. Once you begin the query, the Import Field Mapping dialog box will appear. Make sure the data and fields match up before you click Import.

  11. Click Import and the ODBC data source will be imported into the FileMaker database.

Once you begin the query, the Import Field Mapping dialog box will appear. Make sure the data and fields match up before you click Import. Tip

  • If you are familiar with building SQL queries, you can type SQL statements directly into the dialog box’s SQL Query window (Figure 17.43).

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
52.14.136.168