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.
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.
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.
FROM Persons SELECT CURRENT FirstName
There are also aggregate functions, which group rows of a collection together into a single value.
This query returns the number of rows of the collection Persons
FROM Persons SELECT Count(*)
This query returns the minimum, maximum and average number of days
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. Enter the keyword WHERE and a condition in the Web SQL SELECT statement. The condition is a comparison of one or more columns with a preset value.
FROM AttestationPolicies SELECT Count(*) WHERE SolutionDays > 4
FROM AttestationPolicies SELECT Count(*)
WHERE SolutionDays >
(FROM AttestationPolicies SELECT Avg(SolutionDays))
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)
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:
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.
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
To load the contents of a database table