KB179212012: WebCenter - How to connect a WebCenter List to a Database View

Description

In WebCenter, we have a functionality to create Lists based on database tables. The idea is that the content of these tables are managed via uploading Excel sheets to the List. There are use cases however which require that the List content is based on an existing database table (or combination of existing tables). An example of this is the List of Users associated with their Company/Location. 

This article describes how to connect a WebCenter List to a Database View. 

Procedure

1. Define the Database view that represents the data you want to show in the List. For the example of Users and Companies, your view will look like: 

   CREATE VIEW webcenter.Users_Companies 
   (user_ID, user_name, user_firstname, user_lastname, user_email ,
   company_name, location_name, location_shortname )
   as select 
   webcenter.users.Id, webcenter.users.UserName, webcenter.users.FirstName,webcenter.users.LastName,
   webcenter.users.Email,webcenter.companies.LegalName,webcenter.Locations.LocationName,
   webcenter.Locations.LocationShortName 
   from webcenter.users , webcenter.companies , webcenter.Locations
   where webcenter.users.Location_Id = webcenter.Locations.Id
   and webcenter.Locations.Company_ID = webcenter.companies.id

   You can use the existing WebCenter views as inspiration, but you cannot modify existing WebCenter views! 

2. Create an Excel sheet with exactly one row, the content of this row needs to match the names of the columns of the view. In this case, the Excel would be the following: User_company.xlsx. 

   It is very important that there is no other data than the first header row!

3. Now upload this Excel to the List Manager Project.
4. Create a new List, select this Excel sheet as input data and set the following options:
   1. Input Source Properties: Disable the option Flush existing List data before updating the List. The existing data will be replaced by the new data!. If you leave the toggle on, the List creation will fail.
      
   2. Database Info: Select Use an existing database table for storing the data. In the Database Table field, enter the name of the view you have created. You do not need to fill out Database User and Password. Since you re-use an already existing table/view, no update to the database schema is done. 
5. Create the List. 
   

Now the List is connected to the Database view you have created. When you add a User, or change the User assignee to a different Company/Location, it will be reflected immediately in the List content.

You can check the data by going into the list and clicking Show List Data.