A Query fetches information from app data and can perform some simple yet powerful operations.


Click > +New Query a new page opens

New Query

Query Type

Query can be selected on two types:

  1. Standard
  2. Advanced (ASQL)


Standard queries are built using the Appivo Query Builder. Queries can optionally use Parameters, which can be defined via user input.

Select records of

The model is essentially the starting point from which we can select attributes either directly from the model or by traversing relationships and selecting attributes of related records.

Selected attributes

When selecting attributes, no matter where in the query builder, this will by default select all attributes present in the Model and if we click on All Attribute Selected (Expand), we can see the entire list of all selected attributes. Here we can directly choose from its attributes but we can also traverse its relationships (listed in bold) and pick attributes of its related models.

Clicking on the plus icon lets you add another attribute in the selected attribute, where you can use functions like plus, minus, sum etc to get results from those multiple attributes.


Standard functions can be applied to selected attributes. Applying functions in a query is an efficient way to quickly manipulate data, as opposed to doing it in front end code.

The sum of multiple values.
The average of multiple values.
The minimum value in a set of values.
The maximum value in a set of values.
The number of values in a set.
The number of unique values in a set.
The day of the week of a given date.
The day of the month of a given date.
The day of the year of a given date.
The hour of a given time.
The minute of a given time.
The second of a given time.
The month of a given date.
The year of a given date.
The date of a given date.
The month name of a given date.
The day name of a given date.
The week of a given date.
The weekday of a given date.
The week of the year, of a given date.
The quarter of a given date.

Functions can also be combined using simple operators of +, -, /, and *. In addition to using attributes, functions can also use static values and parameters. This table contains a list of common function parameters.

Description of fields:

User ID
The email address of the current user.
Tenant ID
The tenant ID string of the current user’s current tenant.
Current User
The current user.
Current Date & Time
The current date and time.
Current Date
The current date, without time.
Current Year
The current year.
Current Month
The current month.
Current Day of Month
The current day of the month (integer).
Current Day of Week
The current day of the week (string).
Current Week of Year
The current week of the year (integer).
Current Hour
The current time’s hour, without minutes or seconds.
Current Minute
The current time’s minute, without hours or seconds.
Current Second
The current time’s second, without hours or minutes.


Pivots provides the ability to apply functions to the query model and/or related models.  In addition to standard function abilities, pivots also support joins. Multiple pivot attributes can be added and query results will be grouped by the pivot attributes selected.

Description of fields:

Left Join
Includes all records that at least have a match in the left model.
Right Join
Includes all records that at least have a match in the right model.
Natural Join
Includes all records where there is a match in both models.

Example: In an ecommerce app we may want to find the total amount invoiced to each customer. If the invoice has a relation to a Customer model it will have an attribute called “Customer_id” (the customer that owns the invoice). We can pivot our result on the Customer_id attribute to get a result that includes one resulting record each for every customer with the total invoiced amount for each customer.

Filter Criteria

Filters can be used to further narrow query results. When applying filters, attributes of the selected and related models can be used, as well as values and parameters.

Multiple filter criteria can be added and the result will work based on AND or OR conditions for the filter. AND will only fetch results which satisfies all the filtered criteria and OR will give us results if either of the filter criteria is met and not all.

If the model has a relationship to the User model, it will have a User_id attribute. We can filter on that saying that we want to get only records that have a User_id equalling a certain value. Now this value can be derived in a number of ways:

  1. By providing a hardcoded value or by entering the query builder e.g. “1234567899”.
  2. Referring to one of the predefined variables (such as “Current user” – which holds the id of the logged on user).
  3. A custom defined variable is passed as an input to the query when executed. These parameters are defined on the parameters tab. Each parameter has a name, a data type and possibly a default value (the value that is assigned if no input for it is provided at the time of query execution).

When an attribute is selected we can choose to apply a function to its value. Functions which work on aggregates – i.e. a series of values of a selected attribute. 

Some aggregate functions are sum, maximum, minimum, average or the count of values (SUM, MAX, MIN, AVG, COUNT) etc.

Example: So if the stored date is 2022-04-5 we can select 2022 by declaring a selected YEAR function applied to our date attribute. Let’s say that our model is an invoice and it has an attribute for storing the total invoice amount. We could then apply the SUM function to that attribute to get the total of several invoices. Using an aggregation.

Example: assume we are selecting from a model that holds cars that are associated with a manufacturer which in turn is related to a country that has a name attribute). We can traverse the many relationships like this: Car -> Manufacturer -> Country -> Name. So here, for each Car we would get its country of origin.

Order By

Order by applies an order to the query results, based on one or more selected attributes, and by specifying ascending or descending order.  When using attributes from different models, a join type must also be selected.

We can have multiple Order By attributes and the result will be sorted using all those attributes. Here, the result of the query will be first sorted by id in descending order and then that sorted order will be sorted based on Name in ascending order.


When Advanced (ASQL) is selected a new field appears, where a valid ASQL statement can be entered.


Parameters are variables that can be used within queries. Each parameter has a name, a data type and possibly a default value (the value that is assigned if no input for it is provided at the time of query execution).

Description of fields:

Enter the name.
Select a type:
-Date and Time
-Big Integer
Select a variable:
-Current User
-Current Date and Time
-Current Date
-Current Year
-Current Month
-Current Day of month
-Current Day of Week
-Current Week of year
-Current Hour
-Current Minute
-Current Second
Value is only enabled with the Variable selected is Value. This will be used as default, for the query if no other value is assigned at the time of query execution.

Up Next

No Topics.