Localizing views

Introduction

Views are often used to select data. The views, in their turn, can select data from localizable columns. Thus, to perform data selection via views, you need to set up localizable views.

To localize a view:

1. Create a view object schema. Enable multi-language for localizable columns.
2. Add a new localization view in the database.

Case description

The [Contact address] object schema (ContactAddress) is already implemented in Creatio. One of its columns refers to the [Address type] (AddressType) lookup (schema). The [Name] (Name) column of the [Address type] schema is localizable.

Since almost every object schema has a corresponding table in Creatio, the table connection structure will look as follows (fig. 1):

• ContactAddress – the table of contact addresses. It is bound to the AddressType table by the AddressTypeId column.
• AddressType – the table of address types. The Name column contains values that correspond to the user culture set by default. The values that correspond to other cultures are contained in the SysAddressTypeLcz table.
• SysAddressTypeLcz – automatically generated system table of localizble values of address types. It is bound to the AddressType table by the RecordId column, and to the SysCulture table – by the SysCultureId column. The Name column contains the localizable values of address types for the culture specified in the SysCultureId column.
• SysCulture – system table with the list of cultures.

Fig. 1. The structure and table connections for the [Contact address], [Address type] schemas and localization tables.

Create a view that selects the following fields:

• ContactAddress.Address – contact address
• AddressType.Name – localizable name of the address type

Source code

You can download the package with case implementation using the following link.

Case implementation

1. Creating a view object schema

Create an object schema with the following parameter values:

• [Name] – “UsrVwContactAdress”. The Usr prefix is set via the [Prefix for object name] system setting. The Vw (contracted from View) indicates that the schema is a view in the database.
• [Title] – "Contact address view”.
• [Parent object] – “Base object”.
• [Represent Structure of Database View] – this checkbox must be selected (fig. 2).

Creating an object schema and adding custom columns to it is described in the “Creating the entity schema” article.

Fig. 2. The “Represent Structure of Database View” checkbox

Add two string columns to the created schema

The first column will contain non-localizable address values of the culture by default. Set the following parameter values for it:

• [Name] – “UsrAddress”
• [Title] - “Address”
• [Data type] – "Text (50 characters)”

The second column will contain the localizable values of the address type. Set the following parameter values for it:

• [Name] – “UsrAddressType”
• [Title] – “Address type”
• [Data type] – "Text (50 characters)”
• [Localizable text] – the checkbox must be selected (fig. 3). Read more about multi-language functionality in the “Enabling multi-language in an object schema” article.

Fig. 3. Multi-language checkbox in the column

Save and publish the schema.

2. Creating the view in the database

To create the UsrVwContactAddress view in the database, execute the following SQL script:

-- The view name should correspond to the name of the schema table.
CREATE VIEW dbo.UsrVwContactAddress
AS
SELECT
    ContactAddress.Id,
    -- The view columns should correspond to the schema columns.
    ContactAddress.Address AS UsrAddress,
    AddressType.Name AS UsrAddressType
FROM ContactAddress
INNER JOIN AddressType ON ContactAddress.AddressTypeId = AddressType.Id;

To create the UsrVwContactAddress localizable view in the database, execute the following SQL script:

-- The view name should correspond to the schema localization table.
CREATE VIEW dbo.SysUsrVwContactAddressLcz
AS
SELECT
    SysAddressTypeLcz.Id,
    ContactAddress.id AS RecordId,
    SysAddressTypeLcz.SysCultureId,
     -- The view columns should correspond to the schema columns.
    SysAddressTypeLcz.Name AS UsrAddressType    
FROM ContactAddress
INNER JOIN AddressType ON ContactAddress.AddressTypeId = AddressType.Id
INNER JOIN SysAddressTypeLcz ON AddressType.Id = SysAddressTypeLcz.RecordId;

The columns of the UsrVwContactAddress localizable view must correspond to the localization table columns. Learn more about the localization tables in the “Localization tables” article.

As a result of case implementation, when the data is read via EntitySchemaQuery, the correct values for different languages are displayed in the UsrAddressType column of the UsrVwContactAddress view.

Case result demonstration

To verify the result, use one of the examples from the “Reading multilingual data with EntitySchemaQuery” article. To verify the query results, you can create a custom configuration service (see “Creating a user configuration service”).

In the created service class, implement the method that will return the list of addresses and their types from the created UsrVwContactAddress non-localizable view via the EntitySchemaQuery query.

[OperationContract]
[WebInvoke(Method = "GET", UriTemplate = "Ex01")]
public string Ex01()
{
    // User connection.
    var userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
    // Query generation.
    var esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "UsrVwContactAddress");
    // Adding columns to query.
    esqResult.AddColumn("UsrAddress");
    esqResult.AddColumn("UsrAddressType");
    // Executing  the database query and receiving the whole resulting collection of objects. 
    var entities = esqResult.GetEntityCollection(userConnection);
    // Display of results.
    var s = "";
    foreach (var item in entities)
    {
        s += item.GetTypedColumnValue<string>("UsrAddress") + Environment.NewLine;
        s += item.GetTypedColumnValue<string>("UsrAddressType") + Environment.NewLine;
    }
    return s;
}

The result of method operation is represented in fig. 4

Fig. 4. The result of verification in localization by default

To verify the operation of a localized view in the created class, implement the second method:

[OperationContract]
[WebInvoke(Method = "GET", UriTemplate = "Ex01")]
public string Ex01()
{
    var userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
    // Receiving the Id of the necessary culture, for example, English.
    var sysCulture = new SysCulture(userConnection);
    if (!sysCulture.FetchPrimaryInfoFromDB("Name", "en-US"))
    {
        return "Culture didn't find";
    }
    Guid CultureId = sysCulture.Id;
    var esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "UsrVwContactAddress");
    esqResult.AddColumn("UsrAddress");
    esqResult.AddColumn("UsrAddressType");
    // Setting up the needed localization.
    esqResult.SetLocalizationCultureId(CultureId);
    var entities = esqResult.GetEntityCollection(userConnection);
    var s = "";
    foreach (var item in entities)
    {
        s += item.GetTypedColumnValue<string>("UsrAddress") + Environment.NewLine;
        s += item.GetTypedColumnValue<string>("UsrAddressType") + Environment.NewLine;
    }
    return s;
}

The result of this method operation is represented in fig. 5

Fig. 5. The result of verification in selected localization