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

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