Page Comparison

Anchor
top top

It is possible to connect XperienCentral to one or more external databases . The word ‘external’ is used to indicate that it is different from which lie outside the internal database : The database that maintains XperienCentral’s internal content & administrationandadministration. It is possible that , however, to place the tables with external data are located in the same database as XperienCentral’s internal tables. Because XperienCentral can be connected to other databases, it is, for example, possible to request data from the

...

backoffice and show these on the website.

In This Topic

Table of Contents
maxLevel 2 minLevel 2

 

...

Use of Queries

Views on Database Content

By means of queries you can configure views on database content.

 

Image Removed

 

 

Overview Versus Detail View

...

• The overview level shows a list of records from the database.
• The detail view level shows one specific record when it is selected.

...

• more details of a selected record.

The upper part shows an overview and the lower part shows a detail view. Note that you You can also place the detail view on a different (special) page.

Sorting by Column

For At the overview level, you can sort by column by clicking on a column heading which toggles the sorting between ascending and descending order. For each column in the overview, you can enable or disable column sorting separately.

...

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 XperienCentral, you manage your query definitions at a central point. Each query definition has a name, code, the name of the database, the presentation format of the retrieved data, which fields of the retrieved data are to be shown in which columns and query parameters. To show the retrieved data on a web page, you insert a Query content element on a page. In the content element you select the desired query definition.

Query Parameters

To be make queries more flexible with queries, you can use parameters in the query code. For instance, you may use a parameter to filter the data to be retrieved on certain characteristics. Then, to change the filter characteristics, After that you only have to assign another value to the parameter without changing the query code. The parameter is put between @ characters in the WHERE clause. For example:

...

You can assign values to a parameter at two levels:

• With Using the query content element
• With the query definition

...

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. 

 

 

Back to Top

 

...

Connecting an External Database

...

1. Make sure a filled external database is available and that you know the connection-related attributes.
2. Navigate to Configuration -> Database Maintenance.
3. Select the [ Database connections ] tab.
4. Select [ New database connection ] from the pulldrop-down menu (top left). A blank dialogue pane with connection related attributes is shown.
5. Enter the connection related attributes.
6. Press [Click Apply]. If XperienCentral can make the connection, the product and driver information is shown as well as a table content list.
   Image Removed
   
   

See Database Maintenance for complete information on connecting external databases.

 

Back to Top

 

...

Using Queries

In this section, we setup set up a basic view on the whPerson table by showing the following fields: lastname, prefix, firstname and email. This section will not elaborate on all the fields in the XperienCentral screens; go to section *** for a detailed description on each field of the different panels. For this section we start with this query:

...

We start with the following query:

...

• To create a query, select ‘New query’ "New query" from the drop-down menu (see A)list.
• To copy a query, select that query it and click [ Copy] (see B).
• To delete a query, select that query it and click [ Delete] (see C).
• To add a column to the overview for any field in the SELECT clause, click [ All fields ] (see D). Then and then enter the column names (headings).
• To enable sorting by column, check the ‘Sort’ box] (see E).
  Image Removed
  
  

Defining a Query Content Element

...

1. Navigate to the desired page.
2. Add the Query content element to the page.
3. Select the desired overview query from the drop-down list.
   Image Removed
   

 

Showing the Basic Overview

The result may look like this. Sorting by column has been enabled for ‘Last name’ and ‘First name’. By clicking on it, you can toggle sorting between ascending and descending order. For this type of content element with this presentation format, the title in the content element is not shown.

 

Image Removed

 

 

1. query from the drop-down list.
   
   
   Image Added
   
   

Back to Top

 

...

Overview with Parameter

Introduction

...

1. Select the desired overview query (Configuration > Queries).
2. Enter the query with a parameter.
3. Press [Click Apply]. A "Parameters" section is automatically added at the bottom of the screen.
4. Define the parameter options:

• • Set the type to "Integer".
  • Select "Edit in element" to be able to assign values to the parameter on the query content element.
  • Select "List" to be able to assign more than one value to the parameter on the query content element.

 

Image Removed

 

Connecting an Options Table to a Parameter

The goal is to let the editor choose from a list of options for the "place" parameter at in the Query content element. The options in the list can be obtained from another table from the database.

1. Click "Connect to table" (or "Edit table" if a table already exists).
2. Enter the name of the table and the names of the ID and name fields. Click [ Apply].
3. Press [Click Close]. You can now select a default value for the parameter. This list is also available with the query content element now.

 

...

 

Defining a Query Content Element with Parameter

...

1. Navigate to the desired page.
2. Add a Query content element.
3. Select an overview query that includes a parameter. A parameter pulldrop-down box is added to the content element.
4. Select one or more parameter values on which you want to filter your view.
   Image Removed

 

If you selected ‘Use "Use search pop-up’ (see 3.2.3Connecting an options table to a parameter), the following screen up", a panel pops up when setting the locations on the query element:

 

Image Removed

 

Showing the Overview with Parameters

The result only shows records in which the location is Amsterdam or Nijmegen:

 

Image Removed

 

 

.

Back to Top

 

...

Overview with Link to Detail View

...

1. Navigate to Configuration > External Databases.
2. Enter the queries and define other settings. For example:

 

 

 

 

 

 

Linking an Overview to a Detail View

In our basic overview example, we add a link from the ‘Last name’ "Last name" field to a person’s detail view. However, to be able to link to a detail view, an integer field with the unique record ID must be present in the SELECT clause. Any other type of ID than an integer type of field will not work.

...

This record ID field must be selected in the ‘Columns settings’ "Columns settings" as well. More than likely, it is not the intention to show this integer field at the website. So, we set it to ‘Do "Do not show’show".

1. Select the desired overview query (Configuration > Queries Queries tab).
2. Under "Column settings" add a column for the record ID field and check "NT" for "Do not show".
3. In order for the field to be linked, select "New link" under "Column settings" or "Modify link" if a link already exists.
4. From the "Field" drop-down list, select the ID field(in this example "personid").
5. From the "To" drop-down list, select the desired detail view. Click [Save].
6. Click [Close]. The overview query looks like this:

 

 

 

 

 

Defining a Query Content Element with Detail Link

...

1. Navigate to the desired page.
2. Add a Query content element.Select an overview query that includes a link to a detail view from the upper drop-down list:
   Image Removed
   

Showing the Overview with Link to Detail View

...

1. .
2. Select an overview query that includes a link to a detail view from the upper drop-down list.

Using a Special Page for the Detail View

...

The detail view will hereafter be shown on this special page.

 

Back to Top

 

More Complex Example

...

...

More Complex Example

• A parameter that filters on the locations Amsterdam and Nijmegen.
• Link A link to detail a detailed view , which is shown underneath the overview.
• Three columns (Department, Job and Location) which whose fields are not retrieved directly from the whPerson table but indirectly through six other tables. This requires a more complex WHERE clause.

...

Detail Query – Get Information

...

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

 

Back to Top 

...

Reference Information

Database Connection Panel

Configuration > Database Maintenance > Database connections 

...

Configuration > Queries > Query 

 

 

The following fields are only visible if the query has @ parameters:

 

...

Configuration > External Databases 

 

 

Back to Top