Chat now with support
Chat with Support

Identity Manager 8.0 - Web Designer Reference Guide

Web Designer Editor Web Designer Structures and Functions The Web Designer Object Model Working with the Web Designer Web Project Configuration Options Customizing the Web Portal Basics of Web Designer Programming Compiling and Debugging Monitoring Frequent Tasks in the Web Portal

Querying Data from a Collection

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

    FROM Persons SELECT CURRENT FirstName

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

    FROM Persons SELECT Count(*)

This query returns the minimum, maximum and average number of days

    FROM AttestationPolicies
    SELECT (Min(SolutionDays), Max(SolutionDays), Avg(SolutionDays))

Filtering Data from a Collection

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.

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:
    1. Gender = 1
    2. Gender = 2

Collections

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

Database Objects as Collection

"Database Objects" as a collection

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

  1. Open the module or component to which to add the collection.
  2. Select the Collections node with a right click.
  3. Select the Database object option.
  4. Click the new node.
  5. Select the database table in the Node editor window, under Object type.
  6. Enter the name of the new collection under Identifier.

To load the contents of a database table

  1. Select the Initializer node of the component with a right click.
  2. Select the option Load collection in the submenu Data actions.
  3. Click the new node.
  4. Select the relevant collection under Collection in the Node editor view.
  5. Using the Filter condition property, enter an filter condition expression if you wish.
Related Documents