This is an Archived topic. The solution is available in the Thinkstore inside the Software Factory.
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]', [type_of_object], '[transl_object_id]', '[transl]')[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
After generating, the resulting function will look something like this:
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
Usage
To use the function, simply call it using the type_of_object and object_id of the object you want to translate:
-- 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
