Automating Translation of Customizations

2 minute read time.

Out of the box, Sage CRM provides users with an interface that is fully translated into English, French, Spanish, and German.  It is possible to add any number of additional languages to Sage CRM.  And if you are creating an extension to Sage CRM then you may want to consider translation into other languages to ensure that the widest possible customer base can use your new add.

This is a trick shared by Ken Schmitt from S5 Solutions.  It allows for the very quick population of custom captions for a new project.  

This is an example of 'Machine Translation' or transformation.  Machine Translation is not with out its problems as every language has idioms and phrases that just do not translate word-for-word into another language.  You may want to read the article "Localisation — More Than Just Translation".   

Ken was working with a specific Caption Family and uses an SQL call to make the change.

You can see the untranslated caption here:

  

Note: This will need to be run to allow the http call within MS SQL.

EXEC master.dbo.sp_configure 'show advanced options', 1

GO
RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE WITH OVERRIDE
GO

The SQL.

UPDATE [CRM].[dbo].[Custom_Captions] 
SET    capt_uk = Rtrim(capt_us), 
       capt_fr = [dbo].[Fns5translate]([Capt_US],'FR'), 
       capt_de = [dbo].[Fns5translate]([Capt_US],'DE'), 
       capt_es = [dbo].[Fns5translate]([Capt_US],'ES'), 
       capt_du = [dbo].[Fns5translate]([Capt_US],'NL'), 
       capt_cs = [dbo].[Fns5translate]([Capt_US],'CS') 
       --,Capt_JP = [dbo].[fnS5translate]([Capt_US],'JA') – didn’t work in my version of SQL?? 
WHERE  capt_deleted IS NULL 
AND    capt_family = '???' – enter capt family that you want TO limitUPDATE for.FUNCTION:USE [CRM]goSET ansi_nulls ONgoSET quoted_identifier ONgoCREATE FUNCTION [dbo].[Fns5translate] ( @text VARCHAR(150), 
@code                                         VARCHAR(2) ) 
returns VARCHAR(1024) AS 
BEGIN 
  IF (Isnull(@text,'') = '') 
  BEGIN 
    RETURN NULL 
  END 
  DECLARE @Key VARCHAR(40) 
  SET @Key = 'Google API KEY' 
  DECLARE @translation VARCHAR(1024) 
  DECLARE @Response    VARCHAR(8000) 
  DECLARE @Obj         INT 
  DECLARE @Result      INT 
  DECLARE @HTTPStatus  INT 
  DECLARE @URL         VARCHAR(max) 
  SET @translation = '---' 
  SET @URL = 'https://translation.googleapis.com/language/translate/v2?key='+@Key+'&source=EN&target='+@code+'&q='+dbo.Urlencode(@text) 
  EXEC @Result 
    = Sp_oacreate 'MSXML2.ServerXMLHttp', 
    @Obj out 
  IF @Result = 0 
  BEGIN 
    DECLARE @Method VARCHAR(350) 
    EXEC @Result 
      = Sp_oamethod @Obj, 
      'setTimeouts(45000, 45000, 45000, 45000)' 
    IF @Result = 0 
    BEGIN 
      EXEC @Result 
        = Sp_oamethod @Obj, 
        'open', 
        NULL, 
        'GET', 
        @URL, 
        false 
      IF @Result = 0 
      BEGIN 
        EXEC @Result 
          = Sp_oamethod @Obj, 
          'setRequestHeader', 
          NULL, 
          'Content-Type', 
          'application/x-www-form-urlencoded' 
        EXEC @Result 
          = Sp_oamethod @Obj, 
          send, 
          NULL, 
          '' 
        IF @Result = 0 
        BEGIN 
          EXEC @Result 
            = Sp_oagetproperty @Obj, 
            'status', 
            @HTTPStatus out 
          EXEC @Result 
            = Sp_oagetproperty @Obj, 
            'responseText', 
            @Response out 
        END 
        IF (@HTTPStatus = 200) 
        BEGIN 
          SET @translation = Json_value(@Response,'$.data.translations[0].translatedText') 
        END 
      END 
    END 
  END 
  EXEC @Result 
    = Sp_oadestroy @Obj 
  RETURN Rtrim(@translation) 
END

This resulted in the following translations

Additional considerations

You may run into issues with characters containing diacritics and accents (Umlauts, circumflex etc) and you will need to consider encoding. 

Thanks to Ken Schmitt for sharing this.

If you are interested in exploring Sage CRM's language capability there is small library of language packs available.  These provide partial translation of the users screens into:

  • Dutch
  • Portuguese
  • Italian
  • Turkish
  • Simplified Chinese