Skip to main content

SQL: Subselect

Enquiry in own or foreign database

Properties

  • Type: Lookup
  • Groupable: No
  • Show in lists: Yes
  • Searchable: Yes
  • Requires configuration: Yes

User interface

Field in show mode

[INSERT IMAGE]

Due to its nature this field has no special editing mode.

Configuration

image.png

Required:

  • SELECT query that returns a single record with a single value in the recordset

Options:

  • Restrictions (when to display the value):
    • Always: Single, list and subitem mode
    • Items: Single mode only (hidden in list mode)
    • Lists: List mode only (hidden in single mode)
    • Subitem: Lists displayed in single items (child records)

Notes for usage:

  • Values are not cached or stored
  • Consider activating INDEX for fields used in WHERE or JOIN clauses

Configuration example 1

Solution setup

Solution "Child items" (system name: "testchild")
* Relation field "owner" (system name: "PARENT").

Solution: "Parent items" (system name: "testparent")
* Date field "starting date" (system name: "DATO")

Business requirement

Display the starting date (system name: "DATO") from the related parent table, linked by the local field owner (system name: "PARENT").

Subselect SQL statement

SELECT DATO 
FROM data_testparent 
WHERE :PARENT = data_testparent.DataID

this is the shorthand version for

...
WHERE data_testchild.PARENT = data_testparent.DataID

Configuration example 2

Solution setup

Solution "Example" (system name: "example")
* Status log activated

Business requirement

Display the number of state changes for a data item

Subselect SQL statement

SELECT CONCAT( COUNT(*), ' transitions') 
FROM data_example_statuslog 
WHERE data_example_statuslog.DataID = :DataID

this is the shorthand version for

...
WHERE data_example_statuslog.DataID = data_example.DataID

Developer info

  • FeltTypeID: 510
  • SQL datatype: N/A
  • Class name: FieldSubselect
Back to top