SQL

What is SQL and what is SQL used for?

SQL (Structured Query Language) is a database language that can be used to create and edit databases and to query existing data sets within databases. 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 data records from the "Customers" table. Each data record is separated by a line feed.

Data selected via SQL always contains all data records with matching criteria. So if there are two customers with the customer ID "K00001" in the first example, the SQL command also returns two data records that are separated by a line feed. See FileMaker: For related records, FileMaker always displays only the first record from the related table. Multiple data records must be displayed via a section. In SQL, unique data records can be selected with the addition "DISTINCT":

SELECT DISTINCT "EMail" FROM "Addresses" WHERE "Customer_ID"='K00001′ - Returns the e-mail address of the first related data record whose customer ID is "K00001", even if there are several customer IDs with this value in the "Addresses" related table.

Placeholders 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 with the command "SQLAusführen ()". This command has been continuously expanded and improved in subsequent FileMaker versions. When creating SQL queries in FileMaker, there are a few special features to note. First of all, each field name in the SQL query should be enclosed in quotation marks (inch character "). Field values whose target field is a number field must not contain any surrounding characters; field values whose target field is a text field must be surrounded by quotation marks. In addition, the FileMaker-specific rules must be observed when creating an SQL query in the formula editor. If a quotation mark is to be created in the FileMaker formula editor, it must always be preceded by a backslash ( \ ) within the text string so that the quotation mark is not recognized as the end of the text string.

Example of an SQL query in the FileMaker formula editor

Execute SQL ("SELECT \"name\" FROM \"customers\" WHERE \"Customer_ID\"='" & $kunden_id & "'"; ""; "")

This example shows the complete "SQLAusführen" command with a simple SQL query. All field names are enclosed in quotation marks, each preceded by a backslash within the text string. As the customer ID in this example is a text string, the corresponding value from the variable $kunden_id is enclosed in quotation marks.

In FileMaker, the "Execute SQL" command can be executed in all functions that provide a formula editor for specifying values. This means that the command can be used in calculation fields or fields with calculated values or in the script editor with the "Set variable" or "Set field value" commands.

FileMaker has described which SQL commands are available in FileMaker in the FileMaker SQL Reference Guide (PDF) summarized.