Dropdown menu from query

A dropdown of options based on a SQL query. The query must return a Label column (display text) and one or more value columns. Each value column’s alias either will match a column name in the form’s table or cause a new value to be brought into scope for the rest of them. When a user selects a dropdown option, all matched columns are set to the corresponding values from the selected row.

Ingredient Family: formIngredients

Outline Display: A dropdown widget that draws from {{query}}.

Details

Supports a multi-column pattern: if the query returns columns beyond Label, each non-Label column’s alias is matched to a column of the parent table or creates a new form field value. When the user selects a dropdown option, all matched columns are auto-populated with the corresponding values from the selected row. This enables a single dropdown selection to fill multiple form fields at once.

Requirements

The query SQL query must return at least 2 columns, one of which must be aliased as Label (the display text in the dropdown). The remaining columns are value columns whose aliases are matched to table columns or brought into scope as new form fields. Value columns should not return NULL values. Use DISTINCT or GROUP BY to avoid duplicates. CORRECT PATTERNS: For foreign key dropdown: SELECT “RefTable”.“KeyColumn” AS “Foreign Ref”, “RefTable”.“DisplayName” AS “Label” FROM “RefTable” ORDER BY “RefTable”.“DisplayName”. For string dropdown: SELECT “Table”.“Code” AS “Code”, “Table”.“Name” AS “Label” FROM “Table” WHERE “Table”.“Active” = TRUE ORDER BY “Table”.“Name”. MULTI-COLUMN PATTERN (set several fields from one selection): SELECT “Product”.“ID” AS “Product”, “Product”.“Price” AS “Unit Price”, “Product”.“Category” AS “Category”, “Product”.“Name” AS “Label” FROM “Product” ORDER BY “Product”.“Name”. Selecting a product auto-populates Product, Unit Price, and Category in the form.

Local scope

The parent slot binds these (look up the slot in the parent to see how):

• $parentTable – The table (if any) that this form is based on
• $formFieldsTable – A table of form fields that is built up through the sequence of form ingredients

Names introduced within this component’s knobs:

• queryTab – result columns of query (referenceable as a virtual table)
• subsetTable – empty table populated by ingredients

Settings

Required

• query : SQL query
  A SQL query returning the values for the dropdown.
  Must produce: result named Label matching showable type, then any number of results matching any type
  Scope: current username as [Username] (only where the handler authenticates: a logged-in page, or the calling user of an MCP tool, including inside its pipeline), all columns of $formFieldsTable (reference as [ColumnName], e.g., [Name], [Price]).

Optional

• customLabel : formula (returns text; optional)
  A custom label for the dropdown (leaving this empty uses the column name as the label)
  Scope: none – this formula does not accept column references or handler params.

• cssWidth : constant value (type: integer; required, non-nullable)
  Width between 1 and 12

• compact : constant value (type: bool; required, non-nullable)
  Should this widget be vertically compact? Choosing compact will put the label to the left of the widget, while choosing non-compact will put it above