Queries

Saved queries (DSQs) are reusable query definitions on an entity. Screens, reports, notifications and workflows all read data through them. Every entity gets Default, List and Detail queries when it is registered.

Filters

Filters have a field, an operator and a value. Combine them with FilterLogic, such as 1 AND (2 OR 3). There are 22 operators, including comparisons, lists, null checks, text matching, ranges and existence of related child records.

Dynamic values

Filters can use variables resolved when the query runs: the current user, their roles and teams, the current tenant, app and environment, and date ranges such as today, this week or last month.

Grouping

Queries can group and aggregate (count, sum, average, minimum, maximum) and filter the groups. See the Data API.

What a DSQ is

A DSQ acts as the API for an AppObject's data. Instead of writing SQL, you describe the read, and the platform compiles it into a database query, adds the tenant filter and the user's access scope, and runs it. For example, a DSQ named getWorkItemsByProject takes a projectId parameter and returns the work items of that project, sorted by due date.

Grids, lists, kanban boards, detail forms, reports, bulk import and bulk update, and workflow actions are all bound to DSQs, so one well-designed query serves many places.

Default queries

When you create or register an AppObject, the platform generates its starter DSQs:

Configure a DSQ

Go to App Setup > Entity List, open the AppObject, and select the DataSource tab. In TAF Studio, use TAF: New Query and TAF: Design Query. The configuration has these steps:

Summary

An overview of the configured fields, parameters, filters and sorting, with a Test DSQ button that opens a request tester so you can run the query with a body and parameters without handling authorisation yourself. In Studio, TAF: Run Query shows real rows.

Parameters

Values passed in when the DSQ runs, such as a project id. Mark a parameter as mandatory when the query must never run without it: an optional parameter that arrives empty drops its filter, and the query returns every row the user may see.

Filter and sort

Combine filters with AND and OR, or write filter logic such as 1 AND (2 OR 3) where the numbers are filter group numbers. Studio's TAF: Check for problems flags filter logic that names a group no filter is in.

Field selection

Choose the fields to return, and configure child records to include (see below).

Queries and analytics

The Queries step lists the custom views built on this DSQ. The Analytics step shows an execution trace (when, where and by whom it ran) and the lowest, highest and average execution time, so you can find slow queries.

Related and child records

• Lookup fields. Selecting a bare lookup field returns the linked record's id. Selecting a dotted path such as CustomerId.CompanyName joins the related entity and nests the value. Paths can go several hops, for example ProjectId.CustomerId.CompanyName, and filters accept the same paths.
• Child records. Include a child entity to get its records as a nested list. A child can use its own named DSQ, with its own fields, filters, sort and paging, and can include its own children in turn.
• Record info. Audit and lifecycle data is available as RecordInfo fields, for example RecordInfo.CreatedOn.

Nesting of lookups and children is limited to ten levels.

The definition

Behind the designer, a DSQ is stored as one JSON configuration. You rarely edit it by hand, but it helps to recognise it when you read a release diff or write a script. This one returns projects of active customers with their open tasks:

{
  "SelectedFields": ["Id", "Name", "CustomerId.CompanyName"],
  "Includes": ["#Task:OpenTasks"],
  "Parameters": [
    { "FieldName": "Status", "IsMandatory": false, "MappingFieldName": "status" }
  ],
  "Sort": [{ "FieldName": "Name", "Direction": 0, "Sequence": 0 }],
  "WhereClause": {
    "FilterLogic": "1",
    "Filters": [
      {
        "FieldName": "CustomerId.IsActive",
        "RelationalOperator": 3,
        "Value": true,
        "ValueType": 1,
        "ConjuctionClause": 1,
        "Sequence": 1,
        "GroupID": 1
      }
    ]
  }
}

Each result row nests the lookup as an object and the children as a list:

{
  "Id": "…",
  "Name": "Project Alpha",
  "CustomerId": { "CompanyName": "ACME Corp" },
  "Task": [
    { "Id": "…", "Title": "Design schema", "Status": "Open" },
    { "Id": "…", "Title": "Build API", "Status": "Open" }
  ]
}

In the configuration, a # in an include marks a child entity, and the name after the colon picks that child's DSQ. A value type of 1 is a literal and 2 a parameter; relational operator 3 means equal to.

Custom views

A custom view is a saved filter on top of a DSQ, such as “My open tasks” or “All issues assigned to me”. Create them under Administrator > Data > Queries with a name, the DSQ, a description and the filter criteria. Grid, List View, Kanban and Tree View components offer the views in a dropdown when Custom View Support is on in the list layout, and you can mark one as the default for a screen.

Good practice

• Return only the fields the screen or report needs.
• Filter precisely, for example by status or assignee, rather than filtering in the screen.
• Use consistent names such as get_users_by_name or list_completed_tasks.
• Test a DSQ before you bind a workflow or report to it, and check its analytics after release.
• For complex joins and aggregations, back the read with a database view registered as an AppObject. See Entities and fields.