Skip to content

SQL-filtered reference field (searchReferenceSql)

The searchReferenceSql field is a form field type for hand-built forms that selects a reference to a data object – like the classic reference field, but its option list is pre-filtered by a freely defined SQL query.

This makes it possible to narrow the selection down to the relevant records – for example only projects with status "open" – instead of offering a complete, unfiltered list.

What is this field for?

The automatic reference field of a data schema can only search by the document name (document_name) and cannot filter by business attributes. This is exactly the gap that searchReferenceSql closes: a custom SQL query defines which records are available for selection and which text is searched against.

The field is intended for hand-built custom forms, i.e. forms whose structure is defined directly as form JSON.

Use case

A typical scenario is retroactive time tracking: a dashboard button starts a process with a hand-built form in which time is booked against a reference – for example a project. With searchReferenceSql, the project list can be restricted in advance to open projects, so that already completed projects cannot be selected.

Column contract of the SQL query

The SQL query must return exactly the following four columns. If a column is missing or its name differs, the field will not work correctly.

Column Description
id Unique identifier of the data object. Used to reload the full object (hydration).
data_schema_id Identifier of the data schema. Carried along for preview and consistency.
document_name Display text of the selection option.
search_string The column that is filtered and searched against. Its content is defined freely in the SQL (e.g. document name plus customer name, lowercased).

The four contract columns are mandatory

The query must return exactly id, data_schema_id, document_name and search_string. Clever pre-filtering in the WHERE clause keeps the list small and the search fast.

Configuration via props

Configuration is done inline in the form JSON via the field props:

Property Default Description
sql – (required) Base query with business filter; returns the column contract.
initialLimit 1000 Number of rows loaded initially – also the threshold between "fully" and "partially" loaded.
searchLimit 50 Upper limit per server-side load-more search.
loadOn 'render' 'render' = load when the field loads, 'focus' = load only on focus (saves queries).
hydrateFullObject true On selection, loads the full data object – like the classic reference field.
dataSchemaId – (optional) If set, a "New" button appears to create a new data object of this schema. Without it, the "New" button stays hidden.

Placeholders in the SQL

The following placeholders are resolved automatically in the SQL query and enable user-specific filters:

  • @currentUserSub – the unique identifier of the signed-in user.
  • @currentUserEmail – the email address of the signed-in user.

Complete example

The following form field offers a selection of open projects and shows a "New" button for the projekte schema:

{
  "key": "projekt",
  "type": "searchReferenceSql",
  "props": {
    "label": "Project",
    "sql": "SELECT id, 'projekte' AS data_schema_id, document_name, lower(document_name) AS search_string FROM projekte WHERE status = 'offen'",
    "initialLimit": 1000,
    "searchLimit": 50,
    "loadOn": "render",
    "hydrateFullObject": true,
    "dataSchemaId": "projekte"
  }
}

Search and loading behaviour

The field works in two stages and stays fast even with tens of thousands of records:

  • Fully loaded (result count lower than initialLimit): Typing filters the list instantly on the client. No reloading is needed.
  • Partially loaded (result count equal to initialLimit): Typing also filters the already loaded buffer first. In addition, a "Load more" button appears that continues searching on the server with the current search term (LIKE search up to the searchLimit limit) and merges new results into the list.

Wildcards (% and _) in the search term are allowed.

Field buttons

  • New: Creates a new data object of the schema directly. Only visible when dataSchemaId is set.
  • Open in Data Lake: Opens the selected reference in a new tab.
  • Clear selection: Resets the field.

Tips

  • Pre-filter in the WHERE clause: The tighter the base query, the smaller the list and the faster the search.
  • Build the search column deliberately: Several values can be combined in search_string (e.g. document name and customer name) so that searching by them is possible.
  • loadOn: 'focus' with many fields: If a form contains several such fields, deferred loading on focus saves unnecessary queries.

Related topics: Define attributes and Link processes and forms.