Dynamic Select Options

Text select and Text multi select parameters support three types of select options:

1. Fixed List

Used when you want to select from fixed values.

• Settings:

  • CSV Text:

    • Define options in CSV text format, with each item on a new line. To specify a display name, include it on the same line with a comma (,).

For example:

value1,Display Name 1
value2,Display Name 2

2. Table Value

Used when you want to generate selectable values from a specific column in a table.

• Settings:

  • Source table: Select the table from which values are drawn.

  • Value column: Specify the column to be used as the option value.

  • Label column: Specify the column to be used as the display label.

3. Query Result

Used when you want to generate options based on the results of a specified SQL query.

• Settings:

  • Connection:

    • Choose the database connection to use for the query.

  • SQL:

    • Input the SQL query that returns the option values. If the query returns multiple columns, the first column will be used as the option value, and the second column (if available) as the display label.

SQL queries for options are executed frequently, so avoid using slow or resource-intensive queries for optimal performance.

Execution Timing of the SQL Queries

When using SQL queries for parameter options, it's important to understand when these queries are executed:

1. When a notebook page is loaded:

   • SQL queries for parameters used on the current page are executed during page initialization

   • Parameters not used on the current page are not initialized and their SQL queries are not executed

2. When a parameter dropdown is opened:

   • SQL query is executed when the dropdown menu is first opened

3. When typing in the search field:

   • After the initial query execution, subsequent filtering is performed on the client-side using the already fetched data

   • SQL is not re-executed for every keystroke

Caching Behavior

SQL queries for parameters use the same caching mechanism as regular notebook SQL blocks:

• Results are cached on the backend for up to 24 hours

• Identical queries (including parameter values) will use cached results

• The frontend also maintains a memory cache during the current session

This means even though parameter select dropdowns might be opened frequently, the actual query is only executed once within the cache period, significantly improving performance.