Perform SQL Query by Natural Language

Sends a natural language prompt and database schema to a model, which sends back an SQL query to get a result from the database to use in its response.

Options 

• Account Name is a text expression of the AI account for this script step to use. In the current file, use the Configure AI Account script step to set up the account and assign it this name any time before this script step runs.

• Model is the name of the text generation model to use. Specify the model name as a text expression. For supported models, see FileMaker technical specifications.

• Prompt is a text expression for the natural language request or question to send to the model.

• Options specified determines how the other options for this script step are configured.

  • From list: Configure options individually using the settings below.

  • By JSON data: Configure the following options by providing a JSON object. This option is useful for dynamically setting values. The JSON object must contain key-value pairs corresponding to the script step options and their values. Keys for the supported options are:

    • action: String value corresponding to the desired Action: "query", "query for data only", "get sql", "get first sql only", "get ddl".

    • data tables: A JSON array of strings, where each string is the name of a table whose schema should be included. For example: ["Contacts", "Addresses"].

    • stream: String value for the Stream option ("on" or "off").

    For example:

    Copy

    {
        "action" : "get sql",
        "data tables" : [ "Products", "Invoices" ],
        "stream" : "off"
    }

• Action specifies the type of response to retrieve from the model.

  • Query: Returns the model's final response to the user's prompt in natural language.

  • Query for Data Only: Returns only the data retrieved by the model's last SQL query executed by this script step.

  • Get SQL: Returns all SQL statements generated by the model during the entire interaction.

  • Get First SQL Only: Returns only the first SQL statement generated by the model.

  • Get DDL: Returns the database schema (in Data Definition Language) that this script step generates and sends to the model.

• Data Tables indicates how you want to specify database schema for tables to the model. Only the schema for the specified tables is sent.

  • From list: Select specific tables from a list.

  • By name: Provide a list of table names as a text expression. Separate multiple table names with carriage returns.

  • By DDL: Provide a custom DDL text expression. This allows you to control exactly which schema information is sent to the model.

• Tables is where you select tables when Data Tables is From list.

• Stream determines how the response is delivered when the Action is Query.

  • On: The response is retrieved and delivered incrementally (word by word or in chunks). This can make the FileMaker client feel more responsive.

  • Off: The entire response is retrieved before it is made available. This may cause the FileMaker client to appear unresponsive while waiting for the complete response.

• Parameters is a text expression for a JSON object that consists of key-value pairs for additional parameters that are supported by the model provider. For example: {"temperature": 0.7, "seed": 42}. Refer to the model provider's documentation for key names of supported parameters and their valid ranges.

• Prompt Template Name specifies the name of a custom prompt template configured using the Configure Prompt Template script step. Use this option to design an additional prompt to help achieve an optimal response from an AI model.

• Response Target specifies the field or variable where the result of the script step is to be stored.

• Perform JavaScript in Web Viewer specifies JavaScript code to perform in a target Web Viewer after the script step completes. This option is similar to running the Perform JavaScript in Web Viewer script step as the next script step, except that the response can be passed in to the JavaScript function and streamed to the web viewer when the Stream option is On.

  • Web Viewer specifies the object name of a web viewer on the current layout.

  • Function Name is the name of the JavaScript function to call in the web viewer.

Originated in version 

22.0

Description 

This script step allows you to query your database using natural language instead of writing SQL statements directly. It uses an AI model to interpret a natural language prompt, analyze the database schema, generate appropriate SQL queries, then after this script step executes the SQL queries, the model can respond in natural language with the results. None of your FileMaker data needs to be sent to the model.

The interaction between this script step and the model typically follows these steps:

1. This script step sends the Prompt and the relevant database schema (DDL specified by Data Tables) to the configured AI Model. (This includes prompts provided in an optional prompt template.)

2. The model analyzes the prompt and schema and generates one or more SQL queries designed to retrieve the requested information from the database.

3. This script step receives the generated SQL queries from the model and executes them.

4. This script step sends the results of the SQL query execution back to the model.

5. If the model determines that the results require further processing or refinement to fully address the prompt, it may generate additional SQL queries (repeating steps 2-4). This allows for complex queries or multistep data retrieval.

6. After the model has sufficient information, it generates a final response based on the prompt and SQL query results it received.

7. This script step receives the final response and stores it in the Response Target.

The Action option determines what is returned to the Response Target at different stages of this process:

The debugging actions are primarily useful for understanding how the script step interacts with the model and your database schema while testing your script.

Notes 

• If you specify a Prompt Template Name, this script step uses the template configured via the Configure Prompt Template script step. These templates can use constants like :schema:, :question:, :sql_query:, and :sql_results: which are replaced dynamically during execution. The available constants depend on the template type and the current Action.

• Field comments you enter in the Manage Database dialog are included with the database schema sent to the model. To improve the model's ability to generate useful SQL statements, you can use the comment to explain the purpose of the field (for example, when a field is a foreign key that identifies a record in related table). See Best practices for database schema in DDL and SQL query generation.

• This script step can't determine which round of SQL generation and execution is the last until the final natural language response is received from the model.

• During the execution of the Query or Query for Data Only actions, this script step receives and stores all SQL statements generated by the model and the result of the last executed SQL query. These are the same results returned by the Get SQL and Query for Data Only actions, respectively.

• When this script step executes without an error but no relevant information is found, a message is returned in the Response Target, and the Get(LastError) function returns error 0 ("No error").

• In scripts run by FileMaker Server, FileMaker Cloud, FileMaker Data API, OData API, and Custom Web Publishing, the Perform JavaScript in Web Viewer option isn't supported.

Example 1 

Configures an AI account, then uses the Perform SQL Query by Natural Language script step to ask a question about data in the Contacts table, which has data in Name and Company fields. The script step sends the schema for the Contacts table and the prompt "Which company does Alea work for?" to the model. The model generates the necessary SQL query and this script step executes it, then the model provides a natural language response stored in the Response field.

Configure AI Account [ Account Name: "my-account" ; Model Provider: OpenAI ; API key: "sk-..." ]

Perform SQL Query by Natural Language [ Account Name: "my-account" ; Model: "gpt-4o" ; Prompt: "Which company does Alea work for?" ; Options specified: From list ; Action: Query ; Data Tables: From list ; Tables... ; Stream ; Response Target: Contacts::Response ]

Possible response stored in the Contacts::Response field:

Alea works for Acme Anvils, Inc.

Example 2 

Asks the user for a question in a custom dialog—where the default button is "Query," button 2 is "Data Only," and button 3 is "Cancel"—and the question is stored in the $$UserPrompt variable. Then sets the $options variable to reflect the user's choice for the action to perform: query or query for data only. If the user entered a question and didn't click Cancel, runs the Perform SQL Query by Natural Language script step, passes in $$UserPrompt and $options, then returns the results from the model in $$QueryResultData, which is displayed in another custom dialog. Queries are limited to the Contacts and Employees tables.

Configure AI Account [ Account Name: "my-account" ; Model Provider: OpenAI ; API key: "sk-..." ]

Show Custom Dialog [ "Ask the database:" ; $$UserPrompt ]

Set Variable [ $options ; Value: 
    Let ( [
        $choice = Get ( LastMessageChoice ) ;
        action_choice = Case ( 
            $choice = 1 ; "query" ;
            $choice = 2 ; "query for data only"
            ) 
        ] ; 
    
        JSONSetElement (
            ""; 
            [ "action" ; action_choice ; JSONString ] ;
            [ "data tables" ; JSONMakeArray ( 
                    "Contacts¶Employees" ;
                    "¶" ; 
                    JSONString 
                ) ;
                JSONArray ] ; 
            [ "stream" ; "off" ; JSONString ]
        )
    )
]

If [ not IsEmpty ( $$UserPrompt ) and $choice ≠ 3 ]

    Perform SQL Query by Natural Language [ Account Name: "my-account" ; Model: "gpt-4o" ; Prompt: $$UserPrompt ; Options specified: By JSON data ; Response Target: $$QueryResultData ]
    
    Show Custom Dialog [ "Data Result:" ; $$QueryResultData ]
    
End If

Possible response stored in $$QueryResultData, assuming the prompt was "Which company does Alea work for?" and the user clicked the Data Only button:

Acme Anvils, Inc.

Example 3 

Demonstrates using the Get SQL and Get DDL actions for debugging. The script first retrieves the DDL generated for the Products and Orders tables, then retrieves all SQL statements generated by the model for a specific prompt, storing each in separate variables for review.

Perform SQL Query by Natural Language [ Account Name: "my-account" ; Model: "gpt-4o" ; Prompt: " " ; Options specified: From list ; Action: Get DDL ; Data Tables: By name ; "Products¶Orders" ; Response Target: $$ProductsOrdersDDL ]

Perform SQL Query by Natural Language [ Account Name: "my-account" ; Model: "gpt-4o" ; Prompt: "Find the total value of orders for Product ID 101" ; Options specified: From list ; Action: Get SQL ; Data Tables: By name ; "Products¶Orders" ; Response Target: $$GeneratedSQL ]

Show Custom Dialog [ "Debugging Information" ; "Generated DDL:" & ¶ & $$ProductsOrdersDDL & ¶ & ¶ & "Generated SQL:" & ¶ & $$GeneratedSQL ]

Possible DDL stored in $$ProductsOrdersDDL:

CREATE TABLE "Products" (
"ProductID" int, /*Primary key that uniquely identifies a product*/
"ProductName" varchar(255), /*Descriptive name of the product*/
"Price" int, /*Price of the product in USD*/
PRIMARY KEY (ProductID)
);

CREATE TABLE "Orders" (
"OrderID" int, /*Primary key that uniquely identifies an order*/
"ProductID" int, /*Foreign key for a many-to-one relationship with Products table*/
"Quantity" int, /*Quantity of product in the order*/
PRIMARY KEY (OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Possible SQL statement stored in $$GeneratedSQL:

SELECT SUM("Quantity" * "Price") AS "TotalValue" FROM "Orders" JOIN "Products" ON "Orders"."ProductID" = "Products"."ProductID" WHERE "Orders"."ProductID" = 101

Related topics 

• Configure AI Account script step

• Configure Prompt Template script step

• Perform JavaScript in Web Viewer script step

• Best practices for database schema in DDL and SQL query generation

• GetTableDDL function

• Script steps reference