yousefulSOFTWARE

YOUSEFUL / FILEMAKER

Youseful Query: FAQ, examples and tutorial

Learning center · Sample database setup

Use YsflQuery.fmx64 for SQL, schema inspection and record helpers. The tutorial uses a small table in an open FileMaker database and only reads its records.

FAQ

Which names belong in SQL? Use table occurrence names and field names from your FileMaker file. Quote identifiers when needed. An empty first argument to ExecuteSQLEx selects the calling file.

How do I pass a value containing an apostrophe? Use ? placeholders in ExecuteSQLEx and supply values after its five fixed arguments. Keep empty separator arguments when using JSON. Do not concatenate user input into SQL.

Why does JSON contain Column1 instead of my field name? ExecuteSQLEx's JSON mode currently synthesizes Column1, Column2, and so on. Values are JSON strings, including values from numeric fields. SQL aliases do not rename these keys.

Where are LastError and LastErrorText? Query does not register those functions. Check the result for ? Error (N) or another ? error result. The YsflQr_GetFMErrorText function describes a FileMaker error number.

How is RecordData indexed? This implementation uses zero-based indexes: YsflQr_GetRecordData ( 0 ; 0 ) reads the first cell. A successful ExecuteSQLEx call in RecordData mode returns empty text and replaces the cached rows for that calling session.

Examples

Format a date for SQL:

filemaker
YsflQr_GetAsSQLDate ( Date ( 9 ; 20 ; 2026 ) )

Result: 2026-09-20.

Read products by category using the table created below:

filemaker
YsflQr_ExecuteSQLEx (
    "" ;
    "SELECT Name, Price FROM Products WHERE Category = ? ORDER BY Name" ;
    "JSON" ; "" ; "" ; "Tools"
)

The empty fourth and fifth arguments retain the separator positions before the first query parameter. They are not used in JSON mode.

Tutorial

Standalone sample scripts: Query products.

Goal: query two matching products and inspect the returned JSON.

  1. In a practice file, create a table occurrence named Products with Text fields Name and Category, and a Number field Price.
  2. Enter and commit these records:
Name Category Price
Hammer Tools 12
Notebook Office 5
Saw Tools 20
  1. Create a script named Youseful - Query products:
filemaker
Set Variable [ $category ; Value: "Tools" ]
Set Variable [ $json ; Value: YsflQr_ExecuteSQLEx (
    "" ;
    "SELECT Name, Price FROM Products WHERE Category = ? ORDER BY Name" ;
    "JSON" ; "" ; "" ; $category
) ]
If [ Left ( $json ; 1 ) = "?" ]
    Show Custom Dialog [ "Query failed" ; $json ]
    Exit Script [ Text Result: $json ]
End If
Show Custom Dialog [ "Products" ; JSONFormatElements ( $json ) ]
  1. Run it. Ignoring whitespace, the expected JSON is:
json
[{"Column1":"Hammer","Column2":"12"},{"Column1":"Saw","Column2":"20"}]
  1. Change $category to Office; only Notebook should be returned. Change it to an absent category; JSON mode returns an empty array.

Troubleshooting

Check table occurrence spelling, field names, committed records and account access. Do not substitute a layout name for the table occurrence. If a query returns no rows, test with a known category first. JSON formatting may differ without changing the data.