1. CPQ
  2. Resources
  3. Tables and Databases

Databases 3: Localizing

Article last updated:

When localizing a configurator to a different language, you will generally also want to localize the options that show up in select fields.  Some of those options might be coming from database tables through a connected option filter.  Follow these steps to create columns of localized text in your database tables that option filters will automatically sense and use.


A. Create the translated column

  1. If you haven't already, connect to your database by following one of the two walkthroughs.  The screenshots here are from the database tool SSMS, but you can perform these tasks using the database tool Data Studio instead.
  2. Navigate to the database table you will be localizing, and expand the Columns folder.  For example, if my custom table is called x.Cars, it should looks something like this:


  3. Right click on the Columns folder, and click 'New Column'

  4. Now we will create a new column that represents translations of another column in a specific language.  To do this, name the column the same as the original column plus the 2-letter language iso code for the language you are translating to.  For reference, you can find the 2-letter ISO codes here:  https://en.wikipedia.org/wiki/List_of_ISO_639-1_codes

    As an example, in our Vehicle table, if we want to add a translated column based on the 'Model' column, and we want to translate to Italian, then we will name the column 'ModelIT'.  Give the new column a datatype of nvarchar(MAX).  It should look like this:


  5. Save the changes to the table.  Then right click the Columns folder in the tree again and hit 'Refresh'.  You should now see your new column in the list of columns.

 

B. Apply 2 attributes to the column 

This new column must have 2 extended properties added for it to function as a translation column.  

Name Value Case Example
languageIso Set this to the 2-letter ISO code for the language you are translating to lower case 
it
translatedFrom Set this to the name of the column that this column is acting as a translation of Matching the name of your original column 
Model

We'll add those properties by following the steps below.

  1. In SSMS, choose "File > New > Query with current connection" from the menubar.  In Data Studio, right-click the database, and choose "New Query".  Either way, a new blank query window will appear.
  2. Paste into the query window the "Update Query 1 of 2" below, which will add the first property we need on this column.  Be sure to replace the example text below with the value and type parameters for your own project.

    • Set "languageIso" property to "it" on the column "ModelIT" in the "Cars" table in your "x" schema:

      EXEC sp_addextendedproperty 
      @name= N'languageIso', --the name of the first of two extended properties we want to set
      @value='it', --the value of the extended property 
      @level0type= N'Schema', @level0name='x', --the x schema 
      @level1type= N'Table', @level1name='Cars', --the name of the table the column is in 
      @level2type= N'Column', @level2name='ModelIT'; --the name of the column  

       

      For example, your pasted text would look like this.  (We've highlighted the two properties you're adding in yellow.)

  3. Click "Run Query" to apply your change.
  4. Prepare the query window for a second command: Select all the text from the query window, and delete it. 
  5. In the blank query window, paste the "Update Query 2 of 2" below, which will add the second property we need on this column

    1. Set "translatedFrom" property to "Model" on the column "ModelIT" in the "Cars" table in your "x" schema:

      EXEC sp_addextendedproperty 
      @name= N'translatedFrom', --the name of the second of two extended properties we want to set
      @value='Model', --the value of the extended property 
      @level0type= N'Schema', @level0name='x', --the x schema 
      @level1type= N'Table', @level1name='Cars', --the name of the table the column is in 
      @level2type= N'Column', @level2name='ModelIT'; --the name of the column 
      For example, your pasted text would look like this.  (We've again highlighted the second set of two properties you're adding in yellow.)

  6. Click "Run Query" to apply your change.

 

C. Confirm your changes

Related articles

  1. Prepare the query window for your third and last command: Select all the text from the query window, and delete it. 
  2. Paste into the query window the "Audit Query" below, which will show you your work. 

    • list any extended properties defined on any of your tables:

      SELECT t.name AS [Table Name], 
             c.name AS [Column Name], ep.name AS [Extended Property], valueAS [Extended Property Value] 
      FROM sys.extended_properties AS ep 
           INNERJOIN sys.tables AS t ON ep.major_id = t.object_id 
           INNERJOIN sys.columns AS c ON ep.major_id = c.object_idAND ep.minor_id = c.column_id
      WHEREclass=1
      ORDERBY c.name, ep.name; 
  3. Click "Run Query".  You should see a table, summarizing the two extended properties we have just set.  If you see you made a typo or other mistake, repeat the steps above.
  4. Close the Query Window.  You can now begin entering translated values into your new database table.  When an option filter from a configurator is tied to the Model column, it will automatically know where the translations are, and serve them to the user if appropriate.

 


Was this article helpful?