Functions Reference

Logical functions
F
ILEMAKER FUNCTIONS REFERENCE 222
• 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.
• 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.
Examples
Suppose a database contains two tables, Employees and Salaries, which are related through the
EmpID field.
Note The Salaries::Salary field is a number field.
You want to add a field to the Employees table that displays 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:
100 * Salaries::Salary / ExecuteSQL ( “select sum ( S.salary ) from
Employees E join Salaries S on E.EmpID = S.EmpID where E.Department = ?”;
“”; “”; Employees::Department )
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