In some cases it may be desirable to know the translation of a model object, such as a table, column or domain element, inside your business logic. See for example this idea: Translating domain elements | Thinkwise Community (thinkwisesoftware.com)

Starting with the last releases of Indicium and soon available in the Windows and Web GUIs, a session variable is added that provides the language of the current user. This makes it possible to automatically generate a function that returns the translation of model objects in the user’s language.

Function

To add this dynamic function to your Thinkwise application, first create a function subroutine named tsf_translate  with the following properties:

• Return value: scalar
• Return scalar domain: transl - nvarchar(200)
• Input parameters:
  • type_of_object - int
  • transl_object_id - varchar(100)

Control procedure

Next, add a control procedure in the Functionality modeler with assignment type SQL to create the body of the function:

Code:

-- Determine of which type of objects we want to include the translations
declare @type_of_object table (type_of_object int)

insert into @type_of_object 
values 
    (0), --tab
    (1), --col
    (2) -- dom_elemnt

-- Add the template to the program object
insert into prog_object_item (
    project_id,
    project_vrs_id,
    prog_object_id,
    prog_object_item_id,
    order_no,
    control_proc_id,
    template_id
)
values (
    @project_id,
    @project_vrs_id,
    'func_tsf_translate',
    'func_tsf_translate',
    100,
    @control_proc_id,
    @control_proc_id
)

-- Add the parameters for every translation object
insert into prog_object_item_parmtr (
    project_id,
    project_vrs_id,
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no
)
select
    @project_id,
    @project_vrs_id,
    'func_tsf_translate',
    'func_tsf_translate',
    'appl_lang_id',
    appl_lang_id,
    row_number() over(order by appl_lang_id, type_of_object, transl_object_id)
from transl_object_transl
where project_id = @project_id 
  and project_vrs_id = @project_vrs_id
  and type_of_object in (select type_of_object from @type_of_object)

insert into prog_object_item_parmtr (
    project_id,
    project_vrs_id,
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no
)
select
    @project_id,
    @project_vrs_id,
    'func_tsf_translate',
    'func_tsf_translate',
    'type_of_object',
    convert(varchar(10), type_of_object),
    row_number() over(order by appl_lang_id, type_of_object, transl_object_id)
from transl_object_transl
where project_id = @project_id 
  and project_vrs_id = @project_vrs_id
  and type_of_object in (select type_of_object from @type_of_object)

insert into prog_object_item_parmtr (
    project_id,
    project_vrs_id,
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no
)
select
    @project_id,
    @project_vrs_id,
    'func_tsf_translate',
    'func_tsf_translate',
    'transl_object_id',
    transl_object_id,
    row_number() over(order by appl_lang_id, type_of_object, transl_object_id)
from transl_object_transl
where project_id = @project_id 
  and project_vrs_id = @project_vrs_id
  and type_of_object in (select type_of_object from @type_of_object)

insert into prog_object_item_parmtr (
    project_id,
    project_vrs_id,
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no
)
select
    @project_id,
    @project_vrs_id,
    'func_tsf_translate',
    'func_tsf_translate',
    'comma',
    -- Use lead to check if it is the last value
    case when lead(transl_object_id) over(order by appl_lang_id, type_of_object, transl_object_id) is null then '' else ',' end,
    row_number() over(order by appl_lang_id, type_of_object, transl_object_id)
from transl_object_transl
where project_id = @project_id 
  and project_vrs_id = @project_vrs_id
  and type_of_object in (select type_of_object from @type_of_object)

insert into prog_object_item_parmtr (
    project_id,
    project_vrs_id,
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no
)
select
    @project_id,
    @project_vrs_id,
    'func_tsf_translate',
    'func_tsf_translate',
    'transl',
    replace(transl, '''', ''''''),
    row_number() over(order by appl_lang_id, type_of_object, transl_object_id)
from transl_object_transl
where project_id = @project_id 
  and project_vrs_id = @project_vrs_id
  and type_of_object in (select type_of_object from @type_of_object)

Template

Add the template to the control procedure:

return (
    select transl 
    from (values
            (' appl_lang_id]', ptype_of_object], '_transl_object_id]', 'otransl]')'comma]
         ) t (appl_lang_id, type_of_object, transl_object_id, transl)    
    where appl_lang_id     = session_context(N'tsf_appl_lang_id')
      and type_of_object   = @type_of_object
      and transl_object_id = @transl_object_id
)

Notice how the language for the current user is retrieved using  session_context(N’tsf_appl_lang_id').

Result

create function tsf_translate
(
   @type_of_object   type_of_object,
   @transl_object_id transl_object_id   
)
returns transl
as
begin

    return (
        select transl 
        from (values
                ('NL', 0, 'table',  'Tabel'),
                ('NL', 1, 'column', 'Kolom'),
                ('NL', 2, 'open',   'Open'),
                ('NL', 2, 'closed', 'Gesloten'),
                ('NL', 0, 'table',  'Table'),
                ('NL', 1, 'column', 'Column'),
                ('NL', 2, 'open',   'Open'),
                ('NL', 2, 'closed', 'Closed')
             ) t (appl_lang_id, type_of_object, transl_object_id, transl)    
        where appl_lang_id     = session_context(N'tsf_appl_lang_id') 
          and type_of_object   = @type_of_object
          and transl_object_id = @transl_object_id
    )

end
go

-- To test this function, set the session variable using the following statement:
-- EXEC sys.sp_set_session_context N'tsf_appl_lang_id', @value = 'NL'
-- This is normally done by Indicium or the GUI.

select dbo.tsf_translate(2, 'closed')

-- Returns 'Gesloten' for NL users and 'Closed' for ENG users