User interface
Using the queries module it is possible to manage configured Queries within a Space.
Access levels
The queries are divided up into three access sections:
Space
To manage Queries under the Space section it is required that
you have the Manage Space Definition access modifier on one
of your roles.
All users within the Space will be able to read and execute these Queries.
Participant
The Participant section will display Queries owned by the Participant for your current Role.
All users with read access to this Participant will be able to see and execute these queries.
To add queries to the Participant section it is required that
you have the Create access modifier on your current role.
Queries will be added to the Participant for your current role.
To edit queries under the Participant section it is required
that you have the Update access modifier on your current role.
Personal
You will always be able to add, modify, and execute Queries under the Personal section.
Query categories
All configured Queries have a tag called "Query Category", this tag is set in the Configuration template.
The tag is later used when configuring the Modules with Query capabilities. A module will show all Queries with a "Query Category" matching the configuration of the Module.
The Modules can also be seen as headings in the Query Module.
Run a query
A query can be defined to take one or more parameters e.g. id and/or name
or run without any parameters. The queries that are parameter less will run
directly and display the result direct when selected. The queries that
are defined to take parameters will display search boxes for the values that
can be submitted. The user has to click the search button to run the
query with the submitted values. Omitted values in a query that is defined
to take parameters will be treated as wild-cards.
Column sort
In the query result it is possible to reorder the result list based on the value of a specific column. Click on a column header to sort on the values for that column. For every column header clicking the sort order for that column will toggle between ascending and descending. When the column header is clicked the whole query, with the parameter values, will be executed and then presented with the ordering set on that column.
Column filter
On headers, for terms that take parameters in the query, a striped triangle icon will be shown:
When that icon is clicked, a dialog will show. Using this dialog it is possible to set a filter value for that column:
By clicking the red cross the value will be discarded but when clicking the green ok tick the value will be selected into the query and the query will be re-executed.
Date and time
In ShareAspace all dates and date-times are stored as UTC date-times i.e. converted into a UTC
representation. A provided date will be stored as a date-time in ShareAspace and when the date
is converted to UTC the time part will change e.g. the date 2000-01-01 (e.g. provided
on a DateTimeAssignment) created in Sweden will be represented as the UTC date-time
1999-12-31 23:00:00 in ShareAspace. When a ShareAspace date-time is presented in the web UI the value
can be presented in three ways:
- As raw text displaying the UTC date-time.
- As a localized date-time based on the current time zone.
- As a localized date where only the date portion of the localized date-time is presented.
If a piece of information is created in ShareAspace let's say in a session where the time zone is set to Sweden/Stockholm at 18:30 (UTC+1). If that data is queried from a session where the time zone is set to Asia/Beijing, the result of that query will display the creation date of the information at 01:30 the next day (UTC+8).
Operators for date and date-time parameters
When querying on date-time values it is possible to do the querying only based on the actual date, or to be specific and include the time in the comparison.
A typical date only comparison would be used for queries like "All documents created on this day" or "All documents created in the last 5 days" etc. Date-time queries would typically be used for queries like "All documents created before 13:00 today" etc.
The supported operators differ between date only queries and date-time queries.
Supported operators
| Precision | > | >= | < | <= | = | != |
|---|---|---|---|---|---|---|
| Date | ⚫ | ⚫ | ⚫ | ⚫ | ⚫ | ⚫ |
| Date/Time | ⚫ | ⚫ |
Edit a query
To edit a Query - select it from the left hand side list of Queries.
Once the Query is selected click the Edit button in the ribbon menu.
Note
The Edit button will only show if you have access to edit the
selected Query.
General
| Attribute | Description |
|---|---|
| Name | The display name of the Query. |
| Category | The category tag. Note that for the query to show up in a module, the category must match the configured category of the module. |
| Scope | Can only be set during the Copy operation. |
| All Version | If checked, all the versions of an object will appear in the result. |
Query
When defining the Query you can add new Query clauses by clicking
the Add new clause button.
The first clause will not have an AND/OR operator however each following clause can be AND/OR operated with the previous clause.
You can remove a single clause by clicking the red x-button and you can inject a new clause above an existing one by pressing the green +-button.
| Description | |
|---|---|
| And/Or | AND/OR operator with the previous clause. |
| Field | The identifier of an indexed field. |
| Operator | Based on the value type of the indexed field. |
| Value | The Value used with the operator to compare with the indexed field. If the value is left blank, executing the Query will require user input for the value. |
| Options | Used to define a value set for the Value field. If the value is left empty, the required user input will be presented as a drop down list. |
Query operators
| Operator | Works on term type | Description |
|---|---|---|
| = | date, date-time, oid, string | Case insensitive equality search, tries to find the exact value. |
| LIKE | oid, string | Case insensitive equality wildcard search with * as wildcard, tries to find all matches where * can be anything. |
| STARTSWITH | oid, string | Case insensitive equality search of terms start part, tries to find matches based on matching the start of the value. |
| NOT | date, date-time, string | Case insensitive non-equality search, tries to find everything that does not match the value exact. |
| NOT LIKE | string | Case insensitive non-equality wildcard search with * as wildcard, tries to find everything that does not matches the wildcard expression. |
| NOT STARTSWITH | string | Case insensitive non-equality search for start portion of term, tries to find everything that does not start with provided value. |
| < | date, date-time | Less than search, tries to find values that are less than or before provided value. |
| > | date, date-time | Greater than search, tries to find values that are bigger than or after provided value. |
| <= | date, date-time | Less than or equal to search, tries to find values that are equal to or less/before than provided value. |
| >= | date, date-time | Greater than or equal to search, tries to find values that are equal to or greater/after than provided value. |
Reserved words for value
Some key words have a specific meaning if they are passed on as a value for a parameter. The diagram below lists all reserved words for query values and describes them.
| Word | Description |
|---|---|
| @me | The email address of the user executing the query. |
| @ignore | Ignores the query clause, used as an "empty" value. Most commonly used for search forms. |
| @empty | Used to represent a empty or missing value. |
| @today | Represents the date of today, can be combined with +/- and a number. |
Example:
@me can be used to find data that is created by the current user logged on. Using the example
below will return all instances that are created by current user.
@empty can be used to find data that is missing e.g. instances without name. Using the example
below will return all instances that do not have any name.
@today can be used to find data that is created at a certain time in relation to todays date.
The @today keyword can be combined with a + or a - sign together with a number e.g. @today-1
will represent yesterday and @today+1 will represent tomorrow.
Using the example below will return all instances that was created during the last week i.e.
from seven days ago up until today.
The keyword @ignore can be used when creating a query but it will be the same as removing
that query clause that has the @ignore as a value. The @ignore is mostly used when running a
query and the user wants to ignore a specific field that needs to be set prior to running the
query.
Grouping clauses
It is possible to group clauses together. The groups are interpreted as parenthesizes in the logical expression.
To group two or more clauses, select them using the check boxes and finally press the blue group button.
Field options
Field options allow you to define how user input should work for a specified term. The types could be:
- A dropdown list with a set of defined values and presentations.
- A dropdown list using a custom ShareAspace extension to fetch a name-value list.
- A query to be executed. e.g. "search for Parts classified as" where the classification would be a term. The query would take the user input to search for a classification and allow the user to pick the classification object.
Display columns
The Display columns section controls what the result of the Query will look like.
You can display all the fields that are indexed. To add a field, select it from the left hand side list and click the middle right-arrow-button.
When adding a new field to the result you can configure the header title for the column, the width of the column, enable sorting, as well as selecting a formatter for the value.
| Formatter | Description |
|---|---|
| Display the value as raw text. | |
| generic | Display a text value or an icon based on the true/false result of the cell value matching a regular expression. |
| booleanText | Display using a custom text for a Boolean value. |
| booleanIcon | Display using a custom icon for a Boolean value. |
| date | Display the date portion of a date time. |
| date-time | Display the date time adjusted for current time zone. |
| softType-icon | Display the icon if a SoftType instead of name. |
| statusIcon | Only applicable to DataExchange queries. |
| owner | Display Participant default presentation for an indexed owner reference. |
The "enable sorting" checkbox will allow the user to click on the column header in the query result screen in order to sort ascending or descending. If the "enable sorting" is not checked then nothing will happen when they click on the column header.
You can also rearrange the order of the columns of the result table. The first item in the right hand side list will be the first column.
Note
The display configuration is based on the language settings of your Space. If you switch the presentation language you can configure the Query for that language as well.
Sort columns
There is also the possibility to set what columns the result should be sorted on. It is also possible to set the sorting mode (ascending or descending).
If more than one column is added to sort on the result will sort on first column in list first, second column in list second etc.
Copy a query
To copy a Query - select it from the left hand side list of Queries.
Once the Query is selected click the Copy button in the ribbon
menu.
Set a name on the Query to be created as well as a Scope.
Depending on your access the available options for the Scope will
differ, see Access Levels.
Example:
By copying the Query to your personal queries you will be able to
see that the newly copied Query will show up under the Personal queries
heading on the left hand side list of Queries.
Delete a query
To delete a Query - select it from the left hand side list of Queries.
Once the Query is selected click the Delete button in the ribbon menu.
Note
The Delete button will only show if you have access to edit the
selected Query. You will never be able to delete a Space Query.
Paging
When running a query if the result ends in more than 100 records, the query module result user interface will start paging the results and will indicate the exact number of records in the database.
The default size page is set to display a maximum of 100 records.
The following picture shows an example of a query returning more than 100 records ([2] indicates 148 actual hits) and the number pages [1]:
If the result contains more than 1000 records, the number of hits indicator [2] stops indicating the actual number of hits. It then displays "1000+ hit" indicating there are more than 1000 records in the database.
The following query example returns 1509 records:
The first number of pages is displayed under the table [1] and limited to the first 1001 records. It is updated as needed as the navigation button to the next page is clicked.