| Anchor | ||||
|---|---|---|---|---|
|
...
- The overview level shows a list of records from the database.
- The detail view level shows more details of a selected record.
In the screenshot above, the The upper part shows an overview and the lower part shows a detail view. You can also place the detail view on a different (special) page.
...
Queries are defined using SQL (Structured Query Language). The information returned from the database can then be shown on the website. The following is an example of an SQL query:
...
In this topic, the example database "Who is Who" is used to illustrate concepts. In this database, the whPerson table is the central table containing most of the personal data. The other tables are extensions used to store additional information about the person.
...
Connecting an External Database
...
- Make sure a filled external database is available and that you know the connection-related attributes.
- Navigate to Configuration -> Database Maintenance.
- Select the Database connections tab.
- Select New database connection from the drop-down menu (top left). A blank dialogue pane with connection related attributes is shown.
- Enter the connection related attributes.
- Click Apply. If XperienCentral can make the connection, the product and driver information is shown as well as a table content list.
See Database Maintenance for complete information on connecting external databases.
...
Using Queries
In this section, we set up a basic view on the whPerson table by showing the following fields: lastname, prefix, firstname and email. We start with the following query:
...
- Navigate to the desired page.
- Add the Query content element to the page.
- Select the desired overview query from the drop-down list.
...
Overview with Parameter
Introduction
...
If you selected "Use search pop-up", a panel pops up when setting the locations on the query element.
Showing the Overview with Parameters
The result only shows records in which the location is Amsterdam or Nijmegen.
...
Overview with Link to Detail View
...
The detail view will hereafter be shown on this special page.
...
More Complex Example
- A parameter that filters on the locations Amsterdam and Nijmegen.
- A link to a detailed view which is shown underneath the overview.
- Three columns (Department, Job and Location) whose fields are not retrieved directly from the
whPersontable but indirectly through six other tables. This requires a more complex WHERE clause.
...
SELECT firstname + ' ' + prefix + ' ' + lastname AS fullname, title, firstname, prefix, lastname, whDepartment.name AS depname, whJob.name AS jobname, whLocation.name AS locname, email, telephone, devicenumber, fax, mobile, convert(char(10), birthdate, 105) AS birthdate FROM whPerson, whPersonDepartment, whDepartment, whPersonJob, whJob, whPersonLocation, whLocation WHERE whPerson.id = @dbid@ AND whPerson.id=whPersonDepartment.person AND whPersonDepartment.department=whDepartment.id AND whPerson.id=whPersonJob.person AND whPersonJob.job=whJob.id AND whPerson.id=whPersonLocation.person AND whPersonLocation.location=whLocation.id
...
Reference Information
Database Connection Panel
...
Field | Description | |
Select | List of database page models (detail views). | |
[Delete] button | Delete this database connection. | |
Description | Name of the database page that should be displayed in the selection lists. | |
Database | List of database connections. | |
Presentation | Select ‘WM Details’. This is the default value in XperienCentral. Other values (such as ‘WM Agenda’, ‘WM Person’, and ‘WM Product’) are added to the list if certain functionalities are imported in XperienCentral. | |
Value | A unique value used by the search engine for indexing. | |
Get information query | This is the query to be run if the detail view has to be displayed. This query is connected with @dbid@ to the record that was clicked through. The variable For example:
| |
Show settings - | A number that determines the sequence of the columns to be shown. | |
Show settings – Query field | Name of the column in the SELECT clause. This can be a column name or alias (behind the keyword ‘AS’). | |
Show settings – Column name | Text that should be shown above the column on the page (only if the ‘Show column heading’ is set to ‘Yes’). If this field is left blank, the content of the ‘Query field’ will be used. | |
Show settings – Don’t show | Indicates whether this column should be shown or not. | |
Show settings – Always index | A column that is not shown is not indexed by default. To index this column, a check mark can be placed here. This setting is, however, ignored if ‘Index for search’ is set to ‘No’. | |
Show settings - Delete | The column to be shown can be deleted here. | |
Show settings - [New column] button + number | Add a number of columns. | |
Get query | Query to retrieve the title of the database object. This title is shown on the page in a larger font. The query has to contain an ID and a name in the SELECT clause. | |
Search query | The search query is used in two places:
The query has to return an ID and a name and can use the search parameter to find the proper records in the database. For example:
| |
Link query | Using this query, the links related to this table can be retrieved from a specially created link table. The Link query is only used for the product catalog and for meetings that have been created in the Meetings and Papers component. | |
Index for search | Indicates whether this type of page should be indexed and, therefore, be shown in the search element. | |