“Categories and Attributes” in Open Text Content server is a nice way to add customized metadata to your content stored in the system.
If you want to have “cascading” attributes, for instance “Country >> City”, where you will only display the list of cities within the selected country, you need to use some look-up tables and a bit of SQL.
Here is an example of how you can implement this, with one pitfall and how you can avoid it.
We will use the following sample data:
Countries:
Country_Code | Country_EN | Country_FR |
CN | China | Chine |
DE | Germany | Allemagne |
BE | Belgium | Belgique |
Cities:
Country_code | City_Code | City_EN | City_FR |
CN | BJ | Beijing | Pékin |
CN | GZ | Guangzhou | Canton |
CN | SH | Shanghai | Shanghaï |
DE | FR | Frankfurt | Francfort |
DE | HA | Hamburg | Hambourg |
DE | BE | Berlin | Berlin |
BE | BX | Brussel | Bruxelles |
BE | NA | Namur | Namur |
BE | GN | Ghent | Gent |
Step 1: setting up the look-up tables with the data from above (SQL Server code / Transact-SQL):
- Create the countries table:
CREATE TABLE CAT_COUNTRY(
[Country_code] [nchar](10) NULL,
[Country_EN] [nchar](255) NULL,
[Country_FR] [nchar](255) NULL
) ON [PRIMARY]
- Populate the countries table:
INSERT INTO CAT_COUNTRY(
[Country_code]
,[Country_EN]
,[Country_FR])
VALUES
('CN','China','Chine'),
('DE','Germany','Allemagne'),
('BE','Belgium','Belgique')
GO
CREATE TABLE CAT_CITY(
[Country_code] [nchar](10) NULL,
[City_code] [nchar](10) NULL,
[City_EN] [nchar](255) NULL,
[City_FR] [nchar](255) NULL
) ON [PRIMARY]
- Populate the cities table:
INSERT INTO CAT_CITY(
[Country_code]
,[City_code]
,[City_EN]
,[City_FR])
VALUES
('CN','BJ','Beijing','Pékin'),
('CN','GZ','Guangzhou','Canton'),
('CN','SH','Shanghai','Shanghaï'),
('DE','FR','Frankfurt','Francfort'),
('DE','HA','Hamburg','Hambourg'),
('DE','BE','Berlin','Berlin'),
('BE','BX','Brussel','Bruxelles'),
('BE','NA','Namur','Namur'),
('BE','GN','Ghent','Gent')
GO
Step 2: fixing a “Table Key Lookup” attribute type shortcoming:
The SQL code provided in the “Table Key Lookup” can’t be more than 255 characters, and must be very simple.
As we would like to combine and display in our look-up values the English and French description, and in order to keep the select statements simple in the attribute definition, we will then create 2 views from our previous tables as follow:
Create Country_View:
SELECT DISTINCT
RTRIM(country_EN) + ' - ' + RTRIM(country_FR) AS Country_desc
FROM CAT_COUNTRY
ORDER BY Country_desc
Create City_View:
SELECT DISTINCT
RTRIM(dbo.CAT_Country.Country_EN) + ' - ' + RTRIM(dbo.CAT_Country.Country_FR) AS Country_Desc,
RTRIM(dbo.CAT_City.City_EN) + ' - ' + RTRIM(dbo.CAT_City.City_FR) AS City_Desc,
FROM CAT_CITY
INNER JOIN CAT_COUNTRY
ON dbo.CAT_CITY.Country_Code = dbo.CAT_Country.Country_Code
ORDER BY Country_Desc, City_Desc
Step 3: Creating the Country and City category:
In the Content Server Categories volume, create a new Category named “Country and City” from the “Add Item” menu:
Click on the newly created category name to edit it.
In the “Add Attribute” drop down menu, select “Text: Table Key Lookup” to create the “Country” attribute:
Name it “Country” and use the following settings:
The “Custom SQL statement” box should be checked in order to edit the “Values” box where we will add the SQL statement to query the list of available countries in our look-up table:
Use the following simple select statement to retrieve the list of available countries from the look-up table:
Select Country_desc from Country_view
Create the “City” attribute in the same way (except that this one may not be required, and display it after “Country”), and with the following SQL select statement to retrieve the list of cities based on the selected country:
SELECT City_Desc FROM City_view where Country_Desc = <Country>
Then, test your newly created category by selecting first a country, and then display the available cities you can select: