Introduction

Views are often used for data sampling. Views can sample data from localizable columns. Additional configurations of localizable views is required for localized data sampling.

To localize a view:

1. Create a view object schema. Add a multilingual checkbox to the localizable columns.  Please refer to “Adding a multilingual terminator to an object schema” for more details.
2. Add a new localization view in the database.

Case description

One of the columns in the ContactAddress object schema refers to the AddressType lookup (schema). The Name column of the AddressType schema is localizable.

Since almost every object schema in bpm'online corresponds to a database table, the table binding structure is (Fig. 1):

• ContactAddress – contact address table. Linked to the AddressType table through the AddressTypeId column.
• AddressType – address type table. The Name column contains values that match the default user language (“culture”). The values of other cultures are located in the SysAddressTypeLcz table.
• SysAddressTypeLcz – an auto-generated system table of localizable address type values. Linked to the AddressType table through the RecordId column, and to the SysCulture table through the SysCultureId column. The localizable address type values of a culture (specified in the SysCultureId column) are located in the Name column.
• SysCulture – a system table with a list of cultures.

Fig. 1. Structure and relationships of tables for [Contact address], [Address type] schemas and localization tables.

Create a view that samples the following fields:

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

Case implementation: 

1. Create a view object schema

Learn more about creating object schemas and adding columns to them in the “Creating the entity schema” article.

Create an object schema with the following parameters (Fig. 1):

• [Name] – “UsrVwContactAdress”. The Usr prefix corresponds to the value of the [Prefix for object name] system setting. The Vw prefix (short for View) indicates that the schema is a database view.
• [Title] – “Contact address view”.
• [Package] – the name of the package used for development (see: “Creating and installing a package for development”).
• [Parent object] – “Base object”.
• [Represents Structure of Database View] – required (Fig. 2).

Fig. 2. The view in the database checkbox

When the values are populated, we recommend saving the schema metadata or pre-publishing the object.

Add two text columns to the created schema.

The first column will contain unlocalizaed address values in the default culture. Set the following parameter values:

• [Name] – “UsrAdress”. The Usr prefix corresponds to the value of the [Prefix for object name] system setting.
• [Title] – “Address”.
• [Data type] – “Text (50 characters)”. The string may be set to a different maximum length. The amount of memory used in the database depends on the number of characters.

The second column will contain localized address type values. Set the following parameter values:

• [Name] – “UsrAddressType”. The Usr prefix corresponds to the value of the [Prefix for object name] system setting.
• [Title] – “Address type”.
• [Data type] – “Text (50 characters)”. 
• [Localizable text] – required (Fig. 3). Learn more about multilingual checkboxes in the “Adding a multilingual terminator to an object schema” article.

Fig. 3. A multilingual checkbox in a column

NOTE The [Data type] and [Localizable text] properties are displayed in the extended column properties display mode. (See: “ Workspace of the Object Designer”).

2. Creating a database view

Execute the following SQL script to create the UsrVwContactAddress view in the database.

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

Execute the following SQL script to create the UsrVwContactAddress view in the database.

-- The name of the view must match the name of the schema localization table.
CREATE VIEW dbo.SysUsrVwContactAddressLcz
AS
SELECT
    SysAddressTypeLcz.Id,
    ContactAddress.id AS RecordId,
    SysAddressTypeLcz.SysCultureId,
     -- View columns should correspond with 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 localized UsrVwContactAddress view must match the columns of the localization tables. Learn more about localization tables in the “Localization tables” article.

When sampling the data with EntitySchemaQuery from the UsrAddressType column of the UsrVwContactAddress view, the correct values for different languages will be displayed.

Testing case results

You can use one of the examples in the “Reading multilingual data with EntitySchemaQuery” article to check the results. You can use the examples in the “How to create custom configuration service” article to check the results of queries.

Implement a method in the created service class that returns a list of addresses and their types from the created non-localized UsrVwContactAddress view using the EntitySchemaQuery query.

[OperationContract]
[WebInvoke(Method = "GET", UriTemplate = "Ex01")]
public string Ex01()
{
    // User connection.
    var userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
    // Forming a query.
    var esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "UsrVwContactAddress");
    // Adding columns to a query.
    esqResult.AddColumn("UsrAddress");
    esqResult.AddColumn("UsrAddressType");
    // Executing a database query and retrieving the entire resulting objects collection.
    var entities = esqResult.GetEntityCollection(userConnection);
    // Displaying 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 this method is shown in Fig. 4.

Fig. 4. The default localization test results

To test the performance of a localized view in the created service class, you must implement the second method:

[OperationContract]
[WebInvoke(Method = "GET", UriTemplate = "Ex01")]
public string Ex01()
{
    var userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
    // Retrieving the Id of the necessary culture, e.g. Spanish.
    var sysCulture = new SysCulture(userConnection);
    if (!sysCulture.FetchPrimaryInfoFromDB("Name", "es"))
    {
        return "Culture not found";
    }
    Guid CultureId = sysCulture.Id;
    var esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "UsrVwContactAddress");
    esqResult.AddColumn("UsrAddress");
    esqResult.AddColumn("UsrAddressType");
    // Selecting the required 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 is shown in Fig. 4.

Fig. 5. The selected localization test results