FieldSubselect

From TempusServa wiki
Revision as of 14:47, 20 February 2014 by old>Admin (→‎Configuration example 2)
Jump to navigation Jump to search

SQL: Subselect

Enquiry in own or foreign database

Properties

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


User interface

Field in show mode

File:FieldSubselect show.png

Field in edit mode

File:FieldSubselect edit.png

User interface as displayed in picture


Configuration

This field requires configuration before deployment.

FieldSubselect BE.png

Required:

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

Options:

  • As shown above


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 :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: Special
  • Class name: FieldSubselect
  • Field is allowed in inline tables