Generating dynamic options

For text select, text multi select, and number select, you can generate options in the following ways.

Fixed list

Use this when choosing from fixed values.

• Setting: CSV text.
• Each item is separated by a newline.
• To add a display name, write it on the same line, separated by a comma (,).

value1,Display name1
value2,Display name2

Table

Use this to generate options from a specific table's column.

Setting	Content
Table	The table to fetch values from.
Column for options	The column used as the option's value.
Column for display name	The column used as the display label (optional; the value is used as the display name if omitted).

SQL against the table is run frequently, to narrow results as the user types. Specify a table with few rows.

Query result

Use this to generate options from the run result of a specified SQL query.

Setting	Content
Connection	The connection to run the SQL on.
SQL	The SQL that returns the options. If it returns multiple columns, the first column is the value, and the second (if any) is the display label.

Because the options SQL runs frequently, avoid SQL that's slow or consumes a lot of resources.

You can also use parameter references within the SQL. For example, you can generate sub-category options based on a major-category parameter. Enabling Reset if a referenced parameter changes resets the selected value to default when a dependent parameter changes.

SQL that generates options for a notebook-wide parameter can't reference page-specific parameters, since that would break cross-page references. SQL that generates options for a page-specific parameter can reference both notebook-wide and page-specific parameters.

When the SQL runs

When options come from a table or SQL, fetching happens at the following times.

1. On page load: only parameters currently in use on that page are initialized, and their corresponding SQL runs. SQL for unused parameters doesn't run.
2. On first opening the dropdown: fetched when the parameter's input form is focused and the dropdown menu opens.
3. While typing a search: fetched when narrowing option candidates by typing text in the parameter's input form (if all options have already been fetched, narrowing happens within the fetched options).

Cache

Options from a table or SQL are cached as follows.

• Cached on the backend for up to 30 minutes.
• Identical SQL (including parameter values) uses the cache.
• The frontend also keeps an in-memory cache for the duration of the session.

Repeatedly opening the dropdown avoids re-running the same SQL within the cache period.