Translating model objects in business logic

  • 13 April 2021
  • 3 replies
  • 109 views

Userlevel 6
Badge +9

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

 

 

3 replies

Userlevel 5
Badge +15

I'm trying this out but unfortunately I don't succeed. I end up having an error when generating:


The INSERT statement conflicted with the FOREIGN KEY constraint "ref_prog_object_prog_object_item". The conflict occurred in database "SANDBOX_SF", table "dbo.prog_object".

Any clues?

I've taken the following steps:

  1. Created a new project
  2. Added a new subroutine:
  3. Added parameters to the subroutine:
  4. Added a new control procedure under Dynamic model with the provided code:
  5. Added under functionallity a new control procedure:
  6. Added a template with the provided code and assigned it.
  7. Generated via Creation:

     

Userlevel 6
Badge +9

Hi René,

The provided control procedure code should be added to the Functionality control procedure, and it’s assignment type set to SQL (which is what I meant with dynamic, I’ll update the description to be clearer about it). You don’t need a Dynamic model control procedure.

 

Userlevel 5
Badge +15

Hi René,

The provided control procedure code should be added to the Functionality control procedure, and it’s assignment type set to SQL (which is what I meant with dynamic, I’ll update the description to be clearer about it). You don’t need a Dynamic model control procedure.

 

Thanks, that was it 🙂.

Reply