You can query the collection at any point where you can enter a Web SQL expression. The collection must be visible to you.
The following collections are visible to you in modules and components.
- Self-defined collections
- Virtual collections
- Collections in session modules
To define a Web SQL query
FROM <Collection> SELECT [CURRENT] <Column>
- OR -
SELECT [CURRENT] <Column> FROM <Collection>
The query listed first is advantageous in terms of auto completion because both the collection name and the columns of the collection can be auto-completed.
IMPORTANT: If you query several columns, you must enclose the column names in curved brackets.
Example for a multi-column query
Query with Web SQL:
FROM Persons SELECT FirstName
FROM Persons SELECT (FirstName, LastName)
The Web SQL query relates to the collection. Persons is the name of the collection. LastName and FirstName are properties of a collection in Web SQL.
To show several rows (the names of all loaded employees for example) from a collection there are several Web Designer node types that are iterated through a collection. If you want to reference the current data set within one of these iterations, use the Web SQL keyword CURRENT.
Example for a multi-row query
There are also aggregate functions, which group rows of a collection together into a single value.
Example of querying a single value of a collection
This query returns the number of rows of the collection Persons
This query returns the minimum, maximum, and average number of days
FROM AttestationPolicies
SELECT (Min(SolutionDays), Max(SolutionDays), Avg(SolutionDays))
You can limit a query to the relevant rows by using a filter condition when querying a collection with Web SQL. Use Web SQL to enter the keyword WHERE and a condition in the SELECT statement. The condition is a comparison of one or more columns with a preset value.
Example of a simple filter
FROM AttestationPolicies SELECT Count(*) WHERE SolutionDays > 4
Example of a filter with nested Web SQL expressions
FROM AttestationPolicies SELECT Count(*)
WHERE SolutionDays >
(FROM AttestationPolicies SELECT Avg(SolutionDays))
Example of a filter to use within an iteration
You can also combine a filter with the keyword CURRENT if you are in an iteration.
FROM AttestationCase SELECT Display()
WHERE UID_AttestationPolicy >
(FROM AttestationPolicy SELECT CURRENT UID_AttestationPolicy)
Example of a filter with a condition
There are several places where you can only enter a filter condition with Web SQL.
If all employees are loaded in the collection "Persons" and you want to display men and women separately, proceed as follows:
- Define two grids that iterate on "Persons" and only differ in their filter conditions:
- Gender = 1
- Gender = 2
Collections play an important role when you are using the Web Designer. The data contained in a collection is required for working the components. Data in a collection is stored in rows and columns as in database tables.
A collection is referenced by name within a component or module. You can query collections with Web SQL. For more information, see Defining with Web SQL.
A component's data model defined with collections server as the basis for displays and further processing of data. The contents of collection can be loaded from the database but other data sources are also possible.
Detailed information about this topic
In most use cases, the database is the best data source for collections. All table defined in One Identity Manager can be used as data source for collections. Not all database table entries must be loaded in this case. Normally, you need to limit which data sets are loaded from the database with a WHERE clause on performance grounds.
Tip: An object from a collection based on a database behaves no differently than other objects. All the database object's templates, formatting rules and access permissions are apply.
To load a simple database based collection
- Open the module or component to which to add the collection.
- Select the Collections node with a right click.
- Select the Database object option.
- Click the new node.
- In the Node editor view, under Object type, select the database table.
- Enter the name of the new collection under Identifier.
To load the contents of a database table
- Select the component's Initializer node by right-clicking on it.
- In the Data actions submenu, select the Load collection option.
- Click on the node.
- In the Node editor view, select the relevant collection under Collection.
- Using the Filter condition property, enter an filter condition expression if you wish.
