What is SQL and what is SQL used for?
SQL (Structured Query Language) is a database language with which databases can be created and edited and existing data sets within databases can be queried. The database language has a relatively simple structure and is based on English colloquial language. If an SQL query is sent to a database server, all commands in the query are processed in sequence.
Examples of SQL commands for data selection
SELECT “Name” FROM “Customers” WHERE “Customer_ID”=’K00001′ – This command returns the name of the customer with the customer number “K00001” from the “Customers” table.
SELECT “Name”, “EMail” FROM “Customers” – This command returns the name and email address of all customer records from the Customers table. Each record is separated by a line feed.
Data selected by SQL always contains all records whose criteria match. Thus, in the first example, if there are two customers with the customer ID “K00001”, the SQL command also returns two records separated by a row feed. See FileMaker: For related records, FileMaker only displays the first record in the related table. Several records must be displayed via a portal. In SQL unique records can be selected with the addition “DISTINCT”:
SELECT DISTINCT “EMail” FROM “Addresses” WHERE “Customer_ID”=’K00001′ – Returns the email address of the first related record whose customer ID is “K00001”, even if there are multiple customer IDs with this value in the related table “Addresses”.
Wildcards can also be used in SQL:
SELECT * FROM “Customers” WHERE “Customer_ID”=’K00001′ – Returns the values of all fields from the Customers table if the Customer ID contains the value “K00001”.
FileMaker and SQL
Since FileMaker 12, data can be selected using the SQL Execute () command. This command has been continuously expanded and improved in the following FileMaker versions. For the creation of SQL-queries in FileMaker some special features have to be considered. First of all, each field description in the SQL query should be enclosed in quotation marks (inch characters “). Field values whose target field is a number field may not contain surrounding characters, field values whose target field is a text field must be surrounded by quotation marks. In addition, FileMaker-specific rules must be followed when creating an SQL query in the formula editor. If a quotation mark is to be created in the FileMaker formula editor, a backslash ( \ ) must always precede the text string so that the quotation mark is not recognized as the end of the text string.
Example for an SQL query in the FileMaker Formula Editor
SQLExecute ("SELECT \"Name\") FROM \"Customers\" WHERE \"Customer_ID\"='" & $customer_id & "'"; ""; "")
This example shows the complete SQL Execute command with a simple SQL query. All field names are enclosed in quotation marks, preceded by a backslash within the text string. Since the customer ID in this example represents a text string, the corresponding value from the variable $kunden_id is enclosed in quotation marks.
In FileMaker, the SQL Execute command can be executed in any function that provides a formula editor for specifying values. This allows the command to be used in calculation fields or fields with calculated values, or in the script editor with the Set Variable or Set Field Value commands.