Functions Reference

Logical functions
F
ILEMAKER FUNCTIONS REFERENCE 233
Notes
• To apply the correct formatting to dates in an SQL query, use the DATE statement. If you do
not use the DATE statement, ExecuteSQL treats dates as literal strings.
• FileMaker Pro returns date, time, and number data in Unicode/SQL format, not in the locale
of the operating system or the file.
• ExecuteSQL accepts only the SQL-92 syntax ISO date and time formats with no braces.
ExecuteSQL does not accept the ODBC/JDBC format date, time, and timestamp constants
in braces.
• FileMaker SQL uses the Unicode binary sort order, which is different from the FileMaker Pro
sort order used with language sorting or with the default language-neutral sort order.
• For more details about SELECT statement syntax, supported SQL statements, expressions,
and Catalog functions, see the F
ILEMAKER ODBC AND JDBC GUIDE and the FILEMAKER SQL
R
EFERENCE.
Example 1
Suppose a database contains two tables, Employees and Salaries, which are related through the
EmpID field.
Note The Empoyees::EmpID, Salaries::EmpID, and Salaries::Salary fields are number fields.
You can use the ExecuteSQL function to return a field value from a specific record without changing
the current record or modifying the found set.
ExecuteSQL ( "SELECT Department FROM Employees WHERE EmpID = 1" ) returns
Development regardless of the current record, found set, or layout.
Example 2
Suppose you want to add a field to the Employees table to display the percentage of an employee’s
salary relative to the total salaries in a department. Though you could use a calculation in
FileMaker
Pro to generate this value, you can use the ExecuteSQL function to specify this query
using dynamic parameters.
Define a calculation field in the Employees table, then use the ExecuteSQL function to specify the
following query statement:
Round ( 100 * Salaries::Salary / ExecuteSQL ( “select sum ( S.salary )
from Employees E join Salaries S on E.EmpID = S.EmpID where E.Department =
?”; “”; “”; Employees::Department ) ; 2 )
On each employee record, the calculation field displays the percentage of the employee's salary
relative to the sum of the salaries for the employee's department. For example, the record with a
Last Name of "Smith" returns 52.97, and the record with a Last Name of "Mehmet" returns 100.
EmpID
Last Name
Department
1
2
3
4
5
6
Smith
Ogawa
Durand
Garcia
Mehmet
Ferrini
Development
Development
Quality Assurance
Quality Assurance
Documentation
Marketing
EmpID
Salary
1
2
3
4
5
6
98000
87000
86000
90000
89000
121000
Employees Salaries