SQL Functions

Introduction

These SQL custom functions simplify working with ExecuteSQL in FileMaker by providing consistent and reliable methods for constructing SQL statements and handling field and table names. They help overcome common challenges when working with SQL in FileMaker such as properly quoting table and field names, and building dynamic queries.

Related Functions

These SQL functions are used by other utility functions:

• lookupFieldValue - A simplified lookup function that uses SQL.GetColumn to retrieve a single value

Functions

SQL.GetColumn(columnField; whereField; whereValue)

Description: Retrieves a single column of data using ExecuteSQL based on a simple WHERE clause with one condition. This function handles all the SQL statement construction and properly escapes field and table names.

Parameters:

• columnField: The field to return values from
• whereField: The field to use in the WHERE clause condition
• whereValue: The value to match in the WHERE clause

Dependencies:

• SQL.GetFieldName - For proper field name formatting
• SQL.GetTableName - For extracting table name
• SQL.GetColumnStatement - For constructing the SQL statement

Return:

• The result of the SQL query (list of values from the specified column)

Example:

Let (
    [
        // Get all LastName values for customers in California
        $lastNames = SQL.GetColumn(Customers::LastName; Customers::State; "CA")
    ];
    $lastNames
)
// Returns: List of last names for California customers

SQL.GetColumn2Fields(columnField; whereField; whereValue; whereField2; whereValue2)

Description: Retrieves a single column of data using ExecuteSQL based on a WHERE clause with two conditions. This extends SQL.GetColumn by adding a second condition with AND logic.

Parameters:

• columnField: The field to return values from
• whereField: The first field to use in the WHERE clause
• whereValue: The value to match in the first condition
• whereField2: The second field to use in the WHERE clause
• whereValue2: The value to match in the second condition

Dependencies:

• SQL.GetFieldName - For proper field name formatting
• SQL.GetTableName - For extracting table name

Return:

• The result of the SQL query (list of values from the specified column)

Example:

Let (
    [
        // Get all LastName values for customers in California who are active
        $lastNames = SQL.GetColumn2Fields(
            Customers::LastName;
            Customers::State; "CA";
            Customers::Status; "Active"
        )
    ];
    $lastNames
)
// Returns: List of last names for active California customers

SQL.GetColumnStatement(theField; whereField; whereValue)

Description: Generates a SQL SELECT statement for retrieving a single column with a simple WHERE clause. This function is primarily used by the other SQL functions but can be useful when you need to customize the SQL statement further.

Parameters:

• theField: The field to return values from
• whereField: The field to use in the WHERE clause
• whereValue: The value or parameter placeholder for the WHERE clause

Dependencies:

• SQL.GetFieldName - For proper field name formatting
• SQL.GetTableName - For extracting table name

Return:

• A formatted SQL SELECT statement

Example:

Let (
    [
        // Generate a SQL statement with a parameter placeholder
        $sqlStatement = SQL.GetColumnStatement(Customers::Email; Customers::CustomerID; "?")
    ];
    $sqlStatement
)
// Returns:
// SELECT "Email"
// FROM "Customers"
// WHERE "CustomerID"=?

SQL.GetFieldName(field)

Description: Properly formats a FileMaker field reference for use in SQL statements by adding double quotes around field names. This is crucial for fields with spaces or special characters.

Parameters:

• field: The field reference to format

Return:

• A properly quoted field name for SQL statements

Example:

Let (
    [
        $formattedField = SQL.GetFieldName(Customers::Last Name)
    ];
    $formattedField
)
// Returns: "Last Name"

SQL.GetTableName(field)

Description: Extracts and properly formats the table name from a field reference for use in SQL statements. Handles the process of parsing the field name and adding appropriate quotes.

Parameters:

• field: The field reference to extract table name from

Return:

• A properly quoted table name for SQL statements

Example:

Let (
    [
        $tableName = SQL.GetTableName(Customers::LastName)
    ];
    $tableName
)
// Returns: "Customers"

Usage Tips

1. Parameter Placeholders: Use "?" as the whereValue when constructing statements that will be used with variable parameters to prevent SQL injection vulnerabilities.

2. Multiple Conditions: Use SQL.GetColumn2Fields when you need to filter by two criteria. For more complex queries, consider using SQL.GetColumnStatement as a starting point and adding additional conditions.

3. Field Naming: These functions properly handle quoted field and table names, allowing you to reference fields with spaces or special characters without errors.

4. Performance: When retrieving large datasets, consider adding additional query conditions to limit the result set size.