Database Reference
In-Depth Information
UP TO SPEED: SQL QUERIES
To make use of FileMaker's query builder, you need to know just a little bit about SQL. A SQL
query is made up of
clauses
, each of which influences the results you receive from the database
server. Although SQL understands several clauses, the query builder window supports just three of
them: SELECT, WHERE, and ORDER BY. The window has a tab for each of these.
The SELECT clause is where you tell the database what
fields
you want to import. You can include
fields from more than one table if necessary. A SELECT clause alone imports every record in the
table. If you include fields from more than one table, you get a result that may surprise you: Every
record from each file is mixed in every possible combination. In other words, you import lots of re-
cords (multiply the record counts from each table to figure out how many).
The WHERE clause's job is to control which
records
get imported and how the tables are related.
To specify certain records, you build find criteria into the WHERE clause. First, select a table and
column from the pop-up menus. Then select something from the Operator pop-up menu. You can
match this field with a value you type yourself (turn on the Value radio button) or with another field
(turn on the Column radio button). Either way, enter or select the correct value. Finally, select either
the And radio button or the Or radio button and then turn on Not if you want to
omit
the matching
records. When you're finished defining the criteria, click “Insert into SQL Query.”
Relationships in SQL are probably the most confusing. SQL databases don't have a Relationships
graph like FileMaker, so the database doesn't know how things relate to one another at all. Each
time you build a query, it's your job to tell it how to relate records from one table to those in another.
You do this job by matching field values in each table in the WHERE clause.
For instance, you might pick the Customer ID field from the Customers table, the “=” operator, and
the Customer ID field from the Orders table. When you add criteria like this to your WHERE
clause, you've told the database how Order and Customer records relate. You can add as many cri-
teria in this way as you need.
The last tab is called ORDER BY. This clause lets you specify a sort order for your data. It works
just like FileMaker's normal Sort dialog box: Just add the fields to the Order By list, selecting As-
cending or Descending as appropriate. When you've given it the order you want, click “Insert into
SQL Query” again.
As you do these things, FileMaker builds the actual query in the SQL Query box at the bottom of
the window so you can see how it comes together. If you feel adventurous, you can manually change
this query at any time.
Importing and Exporting in a Script
Like almost everything else in FileMaker, you can completely control the import and export
process from a script. You use the Import Records and Export Records scripts, which you
