Localize the view in the database

Level: advanced

Example

Create a view that generates a collection. The collection must comprise localizable values of contact addresses (the [Name] column of the [AddressType] database table) and values of contact addresses (the [Address] column of the [ContactAddress] database table).

Creatio includes the ContactAddress object schema. The schema column links to the AddressType lookup that contains the Name localizable column. View the table structure and relationships in the figure below.

• The [ContactAddress] database table contains the index of contact address values. Linked to the [AddressType] table via the [AddressTypeId] column.
• The [AddressType] database table contains the index of contact address types. The [Name] table column contains the index of address type values in the primary language. The [SysAddressTypeLcz] table contains values in additional languages.
• The [SysAddressTypeLcz] database system table contains the index of localizable values of contact address types. Generated automatically. Linked to the [AddressType] table via the [RecordId] column and to the [SysCulture] table via the [SysCultureId] column. The [Name] table 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.
• The [SysCulture] system database table contains the index of language cultures.

1. Create a view object schema

1. Open the Configuration section and select a custom package to add the schema.

2. Click Add → Object in the section list toolbar.

   

3. Fill out the schema properties in the Object Designer.

   • Set Code to "UsrVwContactAddress."
   • Set Title to "ContactAddressView."
   • Select "BaseEntity" in the Parent object property.

   

4. Select the Represent Structure of Database View checkbox in the Behavior property block.

   

2. Add columns

1. Add a column that contains the index of contact address values in the primary language.

   1. Click next to the Columns node of the object structure. This opens a menu.

   2. Hold the pointer over Text → click Text (50 characters) in the menu.

      

   3. Fill out the column properties in the Object Designer.

      • Set Code to "UsrAddress."
      • Set Title to "Address."

      

2. Add a column that contains the index of contact address types in an additional language.

   1. Click next to the Columns node of the object structure. This opens a menu.

   2. Hold the pointer over Text → click Text (50 characters) in the menu.

      

   3. Fill out the column properties in the Object Designer.

      • Set Code to "UsrAddressType."
      • Set Title to "AddressType."
      • Select the Localizable text checkbox.

      

   4. Click Save then Publish on the Object Designer toolbar.

3. Create views in the database

1. Create the [UsrVwContactAddress] view in the database. Execute the following SQL query to do this.

   SQL query

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

2. Create the [SysUsrVwContactAddressLcz] localizable view in the database. Execute the following SQL query to do this.

   SQL query

   -- The view name must match the localizable table name.
   CREATE VIEW dbo.SysUsrVwContactAddressLcz
   AS
   SELECT
       SysAddressTypeLcz.Id,
       ContactAddress.id AS RecordId,
       SysAddressTypeLcz.SysCultureId,
       -- View columns must match the schema column names.
       SysAddressTypeLcz.Name AS UsrAddressType
   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 [UsrAddressType] column of the [UsrVwContactAddress] view using EntitySchemaQuery, the correct values for different languages will be displayed.

Outcome of the example

Create a custom web service that uses cookie-based authentication to verify the outcome of the example.

1. Create a Source code schema

1. Open the Configuration section and select a custom package to add the schema.

2. Click Add → Source code on the section list toolbar.

   

3. Fill out the schema properties in the Schema Designer.

   • Set Code to "UsrViewLocalizationService."
   • Set Title to "UsrViewLocalizationService."

   

   Click Apply to apply the changes.

2. Create a service class

1. Add the Terrasoft.Configuration namespace in the Schema Designer.
2. Add the namespaces whose data types 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.

3. Implement the class method

1. Implement a method that returns the index of values of contact address types and contact addresses from the created [UsrVwContactAddress] non-localizable view. Add the public string GetNonLocalizableView() class method that implements the endpoint of the custom web service. The method executes database queries using EntitySchemaQuery.
2. Implement a method that returns the index of localizable values of contact address types and contact addresses from the created [UsrVwContactAddress] localizable view. Add the public string GetLocalizableView() class method that implements the endpoint of the custom web service. The method executes database queries using EntitySchemaQuery.

View the source code of the UsrViewLocalizationService custom web service below.

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 {

        [OperationContract]
        [WebInvoke(Method = "GET", UriTemplate = "GetNonLocalizableView")]
        public string GetNonLocalizableView() {
            var userConnection = (UserConnection) HttpContext.Current.Session["UserConnection"];
            var esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "UsrVwContactAddress");
            esqResult.AddColumn("UsrAddress");
            esqResult.AddColumn("UsrAddressType");
            var entities = esqResult.GetEntityCollection(userConnection);
            var s = "";
            foreach(var item in entities) {
                s += item.GetTypedColumnValue <string> ("UsrAddressType") + ": ";
                s += item.GetTypedColumnValue <string> ("UsrAddress") + "; ";
            }
            return s;
        }

        [OperationContract]
        [WebInvoke(Method = "GET", UriTemplate = "GetLocalizableView")]
        public string GetLocalizableView() {
            var userConnection = (UserConnection) HttpContext.Current.Session["UserConnection"];
            var sysCulture = new SysCulture(userConnection);
            if (!sysCulture.FetchPrimaryInfoFromDB("Name", "en-us")) {
                return "No culture found";
            }
            Guid CultureId = sysCulture.Id;

            var esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "UsrVwContactAddress");
            esqResult.AddColumn("UsrAddress");
            esqResult.AddColumn("UsrAddressType");
            esqResult.SetLocalizationCultureId(CultureId);

            var entities = esqResult.GetEntityCollection(userConnection);
            var s = "";
            foreach(var item in entities) {
                s += item.GetTypedColumnValue <string> ("UsrAddressType") + ": ";
                s += item.GetTypedColumnValue <string> ("UsrAddress") + "; ";
            }
            return s;
        }
    }
}

Click Save then Publish on the Designer’s toolbar.

Outcome of the custom web service

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

Log in to Creatio and access the GetLocalizableView endpoint of the web service from the browser.

Request string

http://mycreatio.com/0/rest/UsrViewLocalizationService/GetLocalizableView

As a result, you will receive a selection that contains the localizable values of contact address types and contact addresses.

Access the GetNonLocalizableView endpoint of the web service from the browser.

Request string

http://mycreatio.com/0/rest/UsrViewLocalizationService/GetNonLocalizableView

As a result, you will receive a collection that contains the non-localizable values of contact address types and contact addresses.

---

Resources

Package with example implementation