Introducing ExecuteSQL

The structured query language (SQL) is a standardized programming language used in relational databases to perform operational functions. Created in the 1970s, it became the standard language for relational databases. The American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) adopted an official SQL standard in 1986 and 1987, respectively. Since then, updates have been released, and numerous companies now have proprietary and open source SQL-compliant systems. While FileMaker is not built on the standard, it has some support for internal and external SQL queries. In version 9 (2007), FileMaker introduced the ability to create live connections to external ODBC data sources (Chapter 34 ). In version 12 (2012), the ExecuteSQL function was added to perform queries against FileMaker tables from any calculation formula. A SQL Query is a text-based instruction for an action. The most frequently used type of query and the only one supported by the ExecuteSQL function in FileMaker is the SELECT query, which retrieves a desired result set. Experienced SQL programmers will appreciate the direct back-end access but may find the limitation constraining. Others may find the divergence from FileMaker’s low code, context-centric access confusing. But the ability to perform a search, sort, and summarization of data directly within a calculation formula from any context is enormously useful. This chapter explores this function, covering the following topics: