Why does FileMaker ExecuteSQL return a question mark?

1. Establish a result you can recognize.

Create three practice records or adapt the example to an existing test table. Keep the IDs simple so the expected result is unambiguous. A query against a field that legitimately contains a question mark is a poor diagnostic: its valid value can resemble an error.

T01 | Prepare quote | Open
T02 | Send invoice  | Closed
T03 | Call supplier | Open

Before evaluating SQL, inspect these records on a layout based on Tasks. Confirm their stored values, not only what a calculation or conditional formatting displays. A spelling difference such as “Opened” changes the test.

2. Prove the names before adding a filter.

ExecuteSQL (
    "SELECT \"TaskID\" FROM \"Tasks\" ORDER BY \"TaskID\"" ;
    "" ; "¶"
)

Expect three lines: T01, T02 and T03. Use the table occurrence name from the relationships graph. A layout name is not a substitute. Double quotes protect SQL identifiers; inside a FileMaker string they are escaped as \".

If this fails, compare the exact names in Manage Database. Check access under the account that will run the finished script. Do not change privileges just to make the example pass. Keep a copy of the original expression so each change remains reversible.

3. Add one bound value.

ExecuteSQL (
    "SELECT \"TaskID\" FROM \"Tasks\"
     WHERE \"Status\" = ? ORDER BY \"TaskID\"" ;
    "" ; "¶" ; "Open"
)

Expect T01 and T03 on separate lines. The question mark inside the query is a value placeholder; the question mark returned by a failed query is an error result. They serve different purposes. Supply one argument for each placeholder, in order. Bind values, not table names or field names.

Change the argument to “Waiting”. With this fixture, the result should be empty. That is a valid no-match result. Keep it separate from the error branch; do not replace every unexpected result with zero.

4. Ask for detail, then isolate the failing clause.

On FileMaker Pro 21.1 or later, evaluate the same expression using ExecuteSQLe. It can return an error message with a location. For example, deliberately changing TaskID to a nonexistent field should identify a column problem. Older clients cannot evaluate this newer function, so keep that diagnostic out of code intended for them.

• Works until WHERE is added: check the field name, operator and argument count.
• Works until JOIN is added: test both table occurrences separately, then inspect the join keys.
• Works until an aggregate is added: review the selected columns and grouping together.
• Works for you but not another account: reproduce with that account and inspect its permitted access.

The SQL formatter can help expose query structure. It does not know your schema, permissions or data, and a clean formatting result is not proof that FileMaker will execute the query.

5. Keep a tiny regression checklist.

Save the three-record fixture and record the expected outputs beside the query. Test Open, Closed and a status with no matches. Add a title containing an apostrophe, such as “Review O’Brien quote”, and bind it as a value in a separate test. Recheck after renaming any referenced field.

Finally, inspect how the caller uses the returned text. A query that succeeds can still be misread if a value contains your chosen separator. For this diagnostic we return only IDs. If the real query returns free text, choose a result-handling strategy that preserves field and record boundaries.

Continue reading

• Write your first FileMaker ExecuteSQL query
• Make FileMaker calculations clearer with Let