-
-
-
- Configure the HTML editor
- Building blocks for user interface functionality
- Layouts
- Sections
- Defining the columns of a table
- Forms
- Form filters
- Searching for data
- Queries: Introduction
- Queries: Advanced search
- Queries: Columns in table
- Cross tables
- Queries: Saving and opening reports
- Dashboards
- Drop down menu
- Custom (user friendly) URLs
- How to change standard messages and labels
- Tree layout
- Velocity forms
-
Queries: Columns in table
The tab Columns enables the user to specify the fields that should be used as columns in the resulting table.
To add a field as a column, click on the ‘Add column' button. A pop-up window appears, with a listing of available classes. This listing consists of fields from the class being searched on and related classes. Mark one or more fields and click ‘Select', the fields will be added to the list of columns for the selection, and the pop-up window closes.
The order of the columns can be changed by dragging them up or down. Each of the columns can be deleted with the 'delete' button on the right side of each row.
Sort fields
Under the tab Sort, fields can be added to sort the results table on. Click add Sort field, select a field and choose a sort direction. If a field is only required to sort the results table, without it being a column in the table, then uncheck the property 'Show' under the Columns tab.
Advanced columns
Going to the Columns tab and toggling on advanced mode will add options for grouping, functions, aggregation and creating cross tables.
To start, it is now possible to group search results on a field. The search results will be grouped around the distinct values of this field, creating a row for every value.
When search results are grouped, it is not possible to show every field value, as not every record will have it's own row anymore. Using the function option allows an operation to be performed on all the fields of records per group, returning a single value.
The Aggregate by option can be used when a field with datatype 'date' is used as a group field. The group rows will be aggregated by the unit of time selected for this property.
When results are grouped or aggregated by a column, clicking on a row will open up the table of records this group consists of.
To create a cross table, set fields to be used as columns, as rows and as cell content under "cross table use".
More functions
- Average Use on a numeric field to return the average value of all records for this field. Tick the group box at another field to get averages per grouping. Click on a result to see all members of a group.
- Average_rounded Works the same as the average function, but results are rounded.
- Concat Concatenates all values from all records in the selected field. Group by another field to get concatenated values per grouping.
- Concat unique Works the same as the concat function, but will not add the same value more than once to the concatenation.
- Count Counts the number of values in all records for this field (empty fields are not counted). Can be combined with group by on another field.
- Count distinct Same as count but doesn't count repetitions of the same value
- First Is used to limit the number of result rows to one when a connected field is added to your query, only the first connected record will be shown.
- Last Same as first, but for the last connected record.
- List Returns the results for the chosen field as a list. Can be used with grouping
- Maximum Returns the maximum value in a field or group
- Minimum Returns the minimum value in a field or group
- Sum Use on a numeric field to return the summed value of all records for this field. Tick the group box at another field to get the sum per grouping. Click on a result to see all members of a group
- Velocity Apply a Velocity script to the field or grouping