Functions Reference

Logical functions
F
ILEMAKER PRO FUNCTIONS REFERENCE 224
• 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.
• File
Maker 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.
• F
or more details about SELECT statement syntax, supported SQL statements, expressions,
and Catalog functions, see
FileMaker ODBC and JDBC Guide and FileMaker SQL
Reference.
Example 1
Suppose a database contains two tables, Employees and Salaries, which are related through the
EmpID field.
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
Note The Employees::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"; ""; "" )
re
turns 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. The example below uses table aliases for the Employees table (E) and
th
e Salaries table (S) when specifying fields (S.Salary, E.EmpID, and S.EmpID).
Define a calculation field in the Employees table, then use the Ex
ecuteSQL 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 perce
ntage 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.