VISUAL QUICKPRO GUIDE FileMaker Pro 7 Advanced FOR WINDOWS AND MACINTOSH [Electronic resources] نسخه متنی

Storing SQL Commands in Scripts

SQL is not as friendly and intuitive as FileMakeryou have to become familiar with its syntax and commands to use it effectively. But it isn't particularly difficult to learn, and it's worth the effort if you need to get files from an ODBC-compliant database.

You can type an SQL query in any field, but it's not a good idea to just type the query the way you would any other piece of data. If you make even a small syntax mistake in an SQL statement, your query won't be executed. Fortunately, FileMaker has provided SQL Query Buildera little helper tool for creating an SQL query with perfect syntax.

In this example, we'll begin by creating an import script to store an SQL query. The query will extract a group of fields from the ODBC-compliant database: the first names, last names, social security numbers, and salaries of a list of employees. Once we have this script in place, we'll extract a subset of this information based on the employee salaries.

To store an SQL query in an import script

1 .

Choose Scripts > ScriptMaker (Control+Shift+S/Command+Shift+S).

2 .

In the Define Scripts dialog box, click New. When the Edit Script dialog box appears, type a name for your script.

3 .In the step list on the left, scroll to the Records section and double-click Import Records.

4 .

In the Script Step Options section, check "Perform without dialog" (Figure 13.18).

Figure 13.18. To avoid scrolling through numerous dialog boxes every time you run the import script, choose "Perform without dialog."

5 .

In the drop-down list on the right of the Script Step Options, select ODBC data (Figure 13.19). When the Select ODBC Data Source dialog box appears, double-click to select the SQL database from the list of Data Sources (Figure 13.20).

Figure 13.19. To see ODBC file choices, you must specify ODBC Data as your data source.

Figure 13.20. Your ODBC source will only appear in this window if you set it up first in your computer's ODBC control panel.

6 .When the Enter Password dialog box appears, enter the name and password in the Enter Password dialog box. If you don't need a password, just click OK.

7 .When the Specify ODBC SQL Query dialog box appears, click Query Builder (Figure 13.21).

Figure 13.21. Although you can type SQL queries directly into the text box, you can avoid typing and syntax mistakes by selecting Query Builder.

8 .The SQL Query Builder appears with the SELECT tab active. Click to select the database table you want from the Tables listing on the left (Figure 13.22).

Figure 13.22. Even if there's only one entry in the Tables section, you must select it to see the list of Columns.

Tables in SQL are equivalent to tables in FileMaker. Columns are equivalent to FileMaker fields.

9 .

When the Columns list appears on the right, double-click the first field whose contents you want to import to add it to the SQL Query section. In this example, we'vechosen the SocialSecurityNumber field (Figure 13.23).

Figure 13.23. Columns are listed alphabetically in the SQL Query Builder.

10 .Double-click the second field whose contents you want to import (Figure 13.24).

Figure 13.24. As you select additional columns, they're added to the SQL query below. The order you select your columns is the order they will be created as FileMaker fields.

FileMaker adds the second field name to the SELECT section of the query. In our example, it's the FirstName field.

11 .One at a time, select as many additional fields as you want to add to the query. When you've finished selecting, click OK twice to return to the Edit Script dialog box.

12 .In Script Step Options, check the "Specify import order" check box. When the Enter Password dialog box appears, input the user name and password again, then click OK.

13 .When the Import Field Mapping dialog box appears, match up the data from the SQL table with the existing field names on the right (Figure 13.25). When you're finished, click OK.

Figure 13.25. When you match up the fields, make sure that there's an arrow in the Map column for every field you want to import.

14 .

Click OK twice to finish.

When you run this script, the SQL data will automatically be found and the Enter Password dialog box will appear. Input an account and password, and the data will be imported into your database.

Once we have a simple import in place, we can use the Query Builder to narrow down our query to select exactly the information from the SQL database that we really need.

To refine a stored SQL query

1 .Choose Scripts > ScriptMaker (Control+Shift+S/Command+Shift+S).

2 .

In the Define Scripts dialog box, highlight the Import Employees script and click Duplicate (Figure 13.26), then Edit.

Figure 13.26. By duplicating the script, you leave the original settings intact for future use while building on your previous work.

3 .When the Edit Script dialog box appears, rename the script Import Employees by Salary.

4 .Double-click the Import Records step. When the Select ODBC Data Source dialog box appears, double-click the selected database, or click Continue.

5 .When the Enter Password dialog box appears, enter your user name and password, or just click OK if you don't need one.

6 .

The Specify ODBC SQL Query dialog box will appear, with the list of SQL columns in the SQL text box. Click Query Builder (Figure 13.27).

Figure 13.27. To add criteria to an existing query, do your work in the Query Builder.

7 .

When the SQL Query Builder dialog box appears, click the WHERE tab (Figure 13.28).

Figure 13.28. Select the WHERE tab to choose exactly the group of records you need.

In SQL you use WHERE to narrow the selection according to the options you choose.

8 .Click the "Show only selected columns" check box to prevent choosing a criterion that isn't part of the columns you've already selected (Figure 13.29).

Figure 13.29. When you check "Show only selected columns," only those columns you chose to include in your query will be available in the column drop-down menu.

9 .

From the Column drop-down menu on the right, select the ODBC data column you want to use to define your criterion.

For this example, we choose Salary.

10 .In the Operator drop-down menu, select the operator you need to define your criterion.

In our example, we choose the less than or equal to operator (<=) because we're searching for people with salaries equal to or below a certain amount (Figure 13.30).

Figure 13.30. Choose an operator that matches the criterion you've selected.

11 .Choose the Column or Value radio button to finish your query. If you want to compare two different data columns, select Column. If you want to define a specific number, select Value. The dialog box will update depending on which choice you make.

In our example, we select Value (Figure 13.31).

Figure 13.31. When we choose Value, the dialog box updates to show one input box instead of two drop-down menus because we won't be comparing two columns as part of our query.

12 .Type the value you want in the input box to the right of Value, or click Values to open the Column Values dialog box, where you choose a value from those in the ODBC data (Figure 13.32). Click OK to close the Column Values dialog box.

Figure 13.32. The Column Values dialog box displays only values that appear in the database.

13 .Click Insert into SQL Query to transfer your choices into the SQL Query formula box (Figure 13.33). Click OK four times to finish.

Figure 13.33. The WHERE statement isn't added automatically to your SQL Queryyou must click the Insert button.

You can return to this script at any time to update the criteria you selected and do a fresh import of the data.

Tips

You can eliminate the Enter Password dialog box by checking "Save user name and password" (Figure 13.34). Although this allows the script to run unattended, it is also a potential security risk if the information in the database is sensitiveas, for example, the example's list of employee salaries.

Figure 13.34. By selecting "Save user name and password," you save time, but allow others to run the import script.

In Chapter 10, "Script Troubleshooting," we showed you how to use the Get(LastError) function to determine whether something has gone wrong with a script. You can use the Get(LastODBCError) function in much the same way, except that it returns any ODBC errors that occur when you run a SQL query.