A special type of field that displays a list of records by custom user query to the database. You can build a query for any entity in the app.

Let's look how field work on a specific example. And so we have a Project entity and a sub entity Invoices. Our goal is to show a list of open Invoices for each project in the project list.

To do this, create a field "Open Invoices" in the Projects entity, select the type "list of records by MySql query" , and specify the data selection condition we need. In the "Heading Template" option, specify which fields will be displayed in the list.

New feature for version 2.8: On the "Display as" tab, you can configure the output of selected records. If there are a lot of records, you can display them as a counter. Clicking on it will open a pop-up window with a full list of selected records.

Setting up conditions

You can use custom and system fields in the condition. Consider the condition from our example:

e.parent_item_id=[id]

e.parent_item_id - this is the ID of the parent record in the Invoice entity.
[id] - this is the ID of the current entity (Projects).

Reserved fields

e.id - record ID
e.parent_item_id - ID of the parent record
e.date_added - date the entry was added (Unix Timestamp)
e.created_by - ID of the user who created the record

Select Invoices with a specific status:

[251] IN (99,20,30)

50 - this is the ID of the Status field. 99,20,30 - this is the status ID. Thus, to select all Invoices with a certain status, we need to create the following condition:

e.parent_item_id=[id] and [251] IN (10,20,30)

You can also apply nested queries in the condition, for example:

[255] in (select id from app_entity_45 where field_88 in (40,50,60))

255 - this is field type Entity.