The field type "MySQL query" makes it possible to build a query to an entity (table) according to a given condition. The query is built automatically, you only need to specify the parameters by which the data will be sampled.

Field settings

Select from entity
Specify the entity to which the MySQL query will be built.

Select field
Specify the field to be selected in the query. For example: [12], where 12 is the id of the numeric field. You can select any field types and use MySQL functions.
Note: the query must return only one value.

Examples of standard queries
Maximum field value: MAX ([12])
The sum of all values in the column: SUM ([12])
Total number of entries: COUNT (*)
A query with the condition: IF ([12]> 100, 'Yes', 'No')

Using the field IDs, specify the conditions for what should be selected in the query.

Examples of standard conditions

  • Comparison of one field: [50] = [12].
  • Comparison across several fields: [50] = [12] AND [51] = [13]
  • Example of a MySQL condition IN: [50] IN (10,20,30)
  • Using a Boolean field in a query: [1522]='true' or [1522]='false'
  • Compare the value in the Dropdown multiselect field: find_in_set([396],[377])>0
  • Condition by field type "Entity": [423]
    [423] - field type Entity linked to Entity A - ID of item from Entity A

The following variables can also be applied in the condition:
[current_user_id] - id of the current user
[current_user_group_id] - current user's group
[TODAY] - current date timestamp

Internal fields

In the condition you can apply internal fields. If the field is used with a prefix msq. (for example: msq.parent_item_id), the value is taken from the entity specified in the "Select from entity" field. If you add the prefix e. (for example,, the value is taken from the current entity in the main query.
id - record identifier
parent_item_id - parent record id
date_added - the date the record was added (Unix Timestamp)
created_by - identifier of the user who created the record

msq.parent_item_id =