Although the SQL Query Builder is extremely useful for importing, it doesn't include menus for other SQL tasks. When the need arises, you'll find the Execute SQL script step invaluable. Like other script steps, you can use Execute SQL more than once in a single script, allowing you to make several changes to an ODBC data source at once.In this example, we use SQL to give raises to groups of employees in the ODBC data source, then transfer the payroll records into our FileMaker database. We also assume that this database can only be accessed by a user with full access privileges, because we automate the process by saving the user name and password to the SQL database with the script.
To run an SQL query from a script
1 .Open a file you use to work with an ODBC data source. Choose Records > Sort and sort the records by the criteria you want.2 .Choose Scripts > ScriptMaker (Control+Shift+S/Command+Shift+S).3 .When the Define Scripts dialog box appears, click New. When the Edit Script dialog box appears, name the script "Salary Increase."4 .Scroll down to the Miscellaneous section in the step list on the left and double-click Execute SQL (Figure 13.35). In the Script Step Options section, check "Perform without dialog," then click Specify (Figure 13.36).
Figure 13.35. The Execute SQL script step gives you a way to update the SQL database from within FileMaker.
Figure 13.36. To automate your SQL query, choose "Perform without dialog."
5 .When the Specify SQL dialog box appears, click Specify in the ODBC Data Source section (Figure 13.37).
Figure 13.37. You must specify the ODBC Data Source every time you execute a different SQL script step.
6 .When the ODBC Data Source dialog box appears, double-click to select your data source from the list.7 .Enter your user name and password in the Enter Password dialog box, then click the "Save user name and password" check box. Click OK to return to the Specify SQL dialog box.8 .In the "Specify SQL statement using" section, select the "SQL text" radio button.9 .Enter the appropriate SQL command in the query builder box. In our example, our ODBC table is called "employees," so we type update employees in the querybuilder box (Figure 13.38).
Figure 13.38. You must specify the table you want to update every time you write a different SQL query.
The SQL command "update" changes one or more rows in the ODBC table. In this case, it will update the table called "employees" based on the criteria you specify next.10 .Enter the criteria you want to use to update the ODBC table. To change a value in a specific way, you use the SQL "set" command.We want to increase the salary for some employees by 5 percent, so we type set salary=salary*1.05 (Figure 13.39). This command line specifies that whatever figure is currently in the salary column of the table should now be set to that figure multiplied by 105 percent.
Figure 13.39. Although you don't have to type each command on a separate line, doing so makes it easier to read the query.
11 .If you don't want every salary to be set to the new number, you have to refine the criteria with a WHERE statement. Not all employees in our example will get the same increase. To specify which salaries change, we type the criterion where salary <40000.00.This portion of the SQL command modifies the "set" command so that only employees whose salary is currently less than $40,000 a year will receive the 5 percent increase.12 .Add a semicolon (;) to signify the end of the SQL query (Figure 13.40). Click OK to return to the Edit Script dialog box.
Figure 13.40. Every SQL statement you write by hand must end with a semicolon.
13 .Click Duplicate to make a copy of the Execute SQL step (Figure 13.41), then double-click the copy to edit it.
Figure 13.41. It's easier to avoid mistakes if you duplicate and edit an existing Execute SQL step than if you write each one from scratch.
14 .When the Specify SQL dialog box appears, change the amount in the second line to 1.08.15 .Change the last line of the query to where salary >40000.00 and salary <1000000.00, then click OK (Figure 13.42).
Figure 13.42. Change the last line of the original SQL text to create a slightly different statement.
This statement increases by 8 percent the salary of all employees currently making more than $40,000, but less than $100,000.16 .Now we want to import the salary records into our FileMaker database. In the step list in the Edit Script dialog box, scroll to the Records section and double-click Import Records.17 .In the Script Step Options section, select "Perform without dialog." Click the "Specify data source" drop-down list on the right. Select ODBC data (Figure 13.43).
Figure 13.43. You won't see ODBC files by default. You need to specify them as an option.
18 .When the Select ODBC Data Source dialog box appears, double-click to select your ODBC database.19 .Enter your user name and password in the Enter Password dialog box, then click the "Save username and password" check box. Click OK.20 .When the Specify ODBC SQL Query Builder dialog box appears, the last import definition you used will appear in the window. Click Query Builder.21 .When the SQL Query Builder appears, delete the WHERE statement if there is one (Figure 13.44), since we'll want to import all of the employees and their new salary data.
Figure 13.44. Delete any portions of SQL queries that will prevent you from importing all the data you need.
22 .Click the ORDER BY tab. In the Columns list, double-click "Employees:Salary" to move it to the Order list. Click the Insert into SQL Query button (Figure 13.45).
Figure 13.45. You can presort your SQL data by choosing a SQL column in the ORDER BY tab.
ORDER BY is the SQL version of FileMaker's Sort command.23 .Click OK twice to return to the Edit Script dialog box. In Script Step Options, click the "Specify import order" check box (Figure 13.46).
Figure 13.46. "Specify import order" allows you to map your SQL columns to your FileMaker fields.
24 .Enter your user name and password in the Enter Password dialog box, then click the "Save user name and password" check box. Click OK.25 .The Import Field Mapping dialog box will appear. Match up the SQL source fields with the target fields, then click OK.26 .Select Go to Layout in the Navigation section of the step list. Under Script Step Options, select the layout you want from the drop-down menu (Figure 13.47). Click OK twice to finish.
Figure 13.47. Choose a layout in which to display the imported and updated records.
When this script runs, it will do two separate salary increases in the ODBC database, and will then use your SQL import criteria to bring records over to FileMaker and display them with your other records.
لطفا منتظر باشید ...
