Version: 8.1

Localize the view in the database

Level: advanced

To implement the example:

Implement an object schema. Read more >>>
Create a view in the database. Read more >>>
Implement a custom web service. Read more >>>
Change UI language. Read more >>>

Example

Create a view that includes contact address types and values. Implement the UsrViewLocalizationService custom web service that uses cookie-based authentication, receives data from created view and, based on the Spanish language chosen in the user profile, returns the following data:

Non-localizable values of contact address types in the primary language.
Localizable values of contact address types in an additional language (English).

Creatio includes the ContactAddress object whose column links to the AddressType lookup object that contains the Name localizable column. View the structure and relationships of database tables in the figure below.

Database table	Description
ContactAddress	The index of contact address values. Linked to the `AddressType` database table via the `AddressTypeId` column.
AddressType	The index of contact address types. The `Name` column contains the index of address type values in the primary language.
SysAddressTypeLcz	The index of contact address types in additional languages. Generated automatically. Linked to the `AddressType` database table via the `RecordId` column and to the `SysCulture` database table via the `SysCultureId` column. The `Name` column contains the index of localizable values of contact address types for the language culture that is specified in the `SysCultureId` column of the current table.
SysCulture	The index of language cultures.

1. Implement an object schema

Open the Customer 360 app in the No-Code Designer.
Open the Advanced settings tab in the No-Code Designer. To do this, click in the top right → "Application management" → "Application Hub" → Customer 360 app → "Advanced settings."
Create a user-made package to add the schema. To do this, click → Create new package → fill out the package properties → Save.

For this example, create the sdkLocalizeTheView user-made package.
Change the current package. Instructions: Change the current package.

For this example, change the current package to sdkLocalizeTheView user-made package.
Create the object schema. To do this, click Add → Object.

Fill out the schema properties.

For this example, use the following schema properties.

Property	Property value
General property block
Code	UsrContactAddressVw
Title	ContactAddressVw
Inheritance property block
Parent object	BaseEntity
Behavior property block
Represent Structure of Database View	Select the checkbox

Add columns.

For this example, add the following columns:

column that contains the index of contact address types
column that contains the index of contact address values

To do this:

Go to the Columns node’s context menu.

Click → Text → Text (50 characters) → fill out the column properties.

Column	Column type	Property	Property value
Column that contains the index of contact address types	Text (50 characters)	Code	UsrContactAddressType
		Title	ContactAddressType
		Localizable text	Select the checkbox
Column that contains the index of contact address values	Text (50 characters)	Code	UsrContactAddressValue
Column that contains the index of contact address values	Text (50 characters)	Title	ContactAddressValue

Publish the changes.

2. Create a view in the database

Ensure that Id column values of the view are unique. Otherwise, it can lead to errors in the business logic that displays data, especially in Freedom UI lists and expanded lists.

Execute SQL queries.

For this example, execute the following SQL queries:

SQL query that creates a UsrContactAddressVw view

SQL query
-- The view name must match the name of database table.
CREATE VIEW dbo.UsrContactAddressVw
AS
SELECT
    ContactAddress.Id,
    -- View columns must match names of the object schema columns.
    ContactAddress.Address AS UsrContactAddressValue,
    AddressType.Name AS UsrContactAddressType
FROM ContactAddress
INNER JOIN AddressType ON ContactAddress.AddressTypeId = AddressType.Id;

SQL query that creates a SysUsrContactAddressVwLcz localizable view

SQL query
-- The view name must match the name of localizable database table.
CREATE VIEW dbo.SysUsrContactAddressVwLcz
AS
SELECT
    SysAddressTypeLcz.Id,
    ContactAddress.id AS RecordId,
    SysAddressTypeLcz.SysCultureId,
    -- View columns must match names of the object schema columns.
    SysAddressTypeLcz.Name AS UsrContactAddressType
FROM ContactAddress
INNER JOIN AddressType ON ContactAddress.AddressTypeId = AddressType.Id
INNER JOIN SysAddressTypeLcz ON AddressType.Id = SysAddressTypeLcz.RecordId;

As a result, when Creatio retrieves data from the UsrContactAddressType column of the UsrContactAddressVw view using EntitySchemaQuery, the localizable values will be displayed.

3. Implement a custom web service

Open the Customer 360 app in the No-Code Designer.
Open the Advanced settings tab in the No-Code Designer. To do this, click in the top right → "Application management" → "Application Hub" → Customer 360 app → "Advanced settings."
Select a user-made package to add the schema.

For this example, select the sdkLocalizeTheView user-made package.
Add the package properties.
1. Open the package properties. To do this, click → Properties. This opens the Dependencies tab on the Package properties page.
2. Click Add in the Depends on Packages block. This opens the Select package window.
3. Select the checkbox for the CrtCoreBase package. The CrtCoreBase package includes the SysCulture object that stores the index of language cultures.
4. Click Select.
5. Apply the changes.
Create the source code schema. To do this, click Add → Source code.
Fill out the schema properties.

For this example, use the following schema properties.

Property
Property value
Code
UsrViewLocalizationService
Title
ViewLocalizationService
Apply the changes.
Create a service class.
1. Add the Terrasoft.Configuration namespace in the Schema Designer.
2. Add the namespaces the data types of which to utilize in the class using the using directive.
3. Add a class name that matches the schema name (the Code property).
4. Specify the System.Web.SessionState.IReadOnlySessionState class as a parent class.
5. Add the [ServiceContract] and [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)] attributes to the class.

Property	Property value
Code	UsrViewLocalizationService
Title	ViewLocalizationService

Implement class methods.

For this example, add the following methods:

Add the public string GetNonLocalizableView() method that implements the endpoint of the custom web service. The method executes database queries using EntitySchemaQuery. The response body will return the index of contact address types and values from the UsrContactAddressVw view in the primary language.
Add the public string GetLocalizableView() method that implements the endpoint of the custom web service. The method executes database queries using EntitySchemaQuery. The response body will return the index of contact address types and values from the UsrContactAddressVw view in an additional language.

UsrViewLocalizationService
namespace Terrasoft.Configuration {
    using System.ServiceModel;
    using System.ServiceModel.Web;
    using System.ServiceModel.Activation;
    using System.Web;
    using Terrasoft.Core;
    using Terrasoft.Core.Entities;
    using System;
    using System.Collections.Generic;

    [ServiceContract]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
    public class UsrViewLocalizationService: System.Web.SessionState.IReadOnlySessionState {

        /* The method that returns the index of contact address types and values from the "UsrContactAddressVw" view in the primary language. */
        [OperationContract]
        [WebInvoke(Method = "GET", UriTemplate = "GetNonLocalizableView")]
        public string GetNonLocalizableView() {
            var userConnection = (UserConnection) HttpContext.Current.Session["UserConnection"];
            
            /* The EntitySchemaQuery instance that accesses the "UsrContactAddressVw" database table. */
            var esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "UsrContactAddressVw");
            
            /* Add columns to the query. */
            esqResult.AddColumn("UsrContactAddressType");
            esqResult.AddColumn("UsrContactAddressValue");

            /* Retrieve the query results. */
            var entities = esqResult.GetEntityCollection(userConnection);
            var s = "";
            foreach(var item in entities) {
                s += item.GetTypedColumnValue<string>("UsrContactAddressType") + ": ";
                s += item.GetTypedColumnValue<string>("UsrContactAddressValue") + "; ";
            }
            return s;
        }

        /* The method that returns the index of contact address types and values from the "UsrContactAddressVw" view in an additional language. */
        [OperationContract]
        [WebInvoke(Method = "GET", UriTemplate = "GetLocalizableView")]
        public string GetLocalizableView() {
            var userConnection = (UserConnection) HttpContext.Current.Session["UserConnection"];
            
            /* Retrieve the ID of the primary language. */
            var sysCulture = new SysCulture(userConnection);
            
            /* Return an error message if the primary language is not found. */
            if (!sysCulture.FetchPrimaryInfoFromDB("Name", "en-us")) {
                return "No culture found";
            }
            Guid CultureId = sysCulture.Id;

            /* The EntitySchemaQuery instance that accesses the "UsrContactAddressVw" database table. */
            var esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "UsrContactAddressVw");
            
            /* Add columns to the query. */
            esqResult.AddColumn("UsrContactAddressType");
            esqResult.AddColumn("UsrContactAddressValue");
            esqResult.SetLocalizationCultureId(CultureId);

            /* Retrieve the query results. */
            var entities = esqResult.GetEntityCollection(userConnection);
            var s = "";
            foreach(var item in entities) {
                s += item.GetTypedColumnValue<string>("UsrContactAddressType") + ": ";
                s += item.GetTypedColumnValue<string>("UsrContactAddressValue") + "; ";
            }
            return s;
        }
    }
}

Publish the schema.

As a result, Creatio will add the custom UsrViewLocalizationService REST web service that has the GetNonLocalizableView and GetLocalizableView endpoints.

4. Change UI language

Enable UI language.

For this example, enable Spanish language. To do this:
1. Open the Languages section. To do this, click in the top right → System setup → Languages.
2. Open the Spanish (Spain) language.
3. Select the Active checkbox.
4. Save the changes.
Change UI language for the current user.
1. Open the user profile. To do this, click the profile picture in the top right → Your profile.
2. Go to the Basic settings tab → Language → Spanish (Spain) → Save.

As a result, UI language for the current user will be changed to Spanish.

View the result

To view the outcome of the example in the primary language, access the GetNonLocalizableView endpoint of the UsrViewLocalizationService web service from the browser address bar.

Request string

CreatioURL/0/rest/UsrViewLocalizationService/GetNonLocalizableView

As a result, the Creatio instance will return non-localizable values of contact address types in the primary language. View the result >>>

To view the outcome of the example in an additional language, access the GetLocalizableView endpoint of the UsrViewLocalizationService web service from the browser address bar.

Request string

CreatioURL/0/rest/UsrViewLocalizationService/GetLocalizableView

As a result, the Creatio instance will return localizable values of contact address types in an additional language. View the result >>>

Source code

UsrViewLocalizationService
namespace Terrasoft.Configuration {
    using System.ServiceModel;
    using System.ServiceModel.Web;
    using System.ServiceModel.Activation;
    using System.Web;
    using Terrasoft.Core;
    using Terrasoft.Core.Entities;
    using System;
    using System.Collections.Generic;

    [ServiceContract]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
    public class UsrViewLocalizationService: System.Web.SessionState.IReadOnlySessionState {

        /* The method that returns the index of contact address types and values from the "UsrContactAddressVw" view in the primary language. */
        [OperationContract]
        [WebInvoke(Method = "GET", UriTemplate = "GetNonLocalizableView")]
        public string GetNonLocalizableView() {
            var userConnection = (UserConnection) HttpContext.Current.Session["UserConnection"];
            
            /* The EntitySchemaQuery instance that accesses the "UsrContactAddressVw" database table. */
            var esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "UsrContactAddressVw");
            
            /* Add columns to the query. */
            esqResult.AddColumn("UsrContactAddressType");
            esqResult.AddColumn("UsrContactAddressValue");

            /* Retrieve the query results. */
            var entities = esqResult.GetEntityCollection(userConnection);
            var s = "";
            foreach(var item in entities) {
                s += item.GetTypedColumnValue<string>("UsrContactAddressType") + ": ";
                s += item.GetTypedColumnValue<string>("UsrContactAddressValue") + "; ";
            }
            return s;
        }

        /* The method that returns the index of contact address types and values from the "UsrContactAddressVw" view in an additional language. */
        [OperationContract]
        [WebInvoke(Method = "GET", UriTemplate = "GetLocalizableView")]
        public string GetLocalizableView() {
            var userConnection = (UserConnection) HttpContext.Current.Session["UserConnection"];
            
            /* Retrieve the ID of the primary language. */
            var sysCulture = new SysCulture(userConnection);
            
            /* Return an error message if the primary language is not found. */
            if (!sysCulture.FetchPrimaryInfoFromDB("Name", "en-us")) {
                return "No culture found";
            }
            Guid CultureId = sysCulture.Id;

            /* The EntitySchemaQuery instance that accesses the "UsrContactAddressVw" database table. */
            var esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "UsrContactAddressVw");
            
            /* Add columns to the query. */
            esqResult.AddColumn("UsrContactAddressType");
            esqResult.AddColumn("UsrContactAddressValue");
            esqResult.SetLocalizationCultureId(CultureId);

            /* Retrieve the query results. */
            var entities = esqResult.GetEntityCollection(userConnection);
            var s = "";
            foreach(var item in entities) {
                s += item.GetTypedColumnValue<string>("UsrContactAddressType") + ": ";
                s += item.GetTypedColumnValue<string>("UsrContactAddressValue") + "; ";
            }
            return s;
        }
    }
}

Resources

Package with example implementation

1. Implement an object schema​

2. Create a view in the database​

3. Implement a custom web service​

4. Change UI language​

View the result​

Source code​

Resources​