Translating model objects in business logic


Userlevel 7
Badge +9

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

 

 

This topic has been closed for comments

16 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 7
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 🙂.

Userlevel 4

Hi Jasper,

 

I've tried applying this to our project and once I click generate it stays completely empty. 

Subroutine

Domain id is an int, name is nvarchar 100

 

 

Function

 

At first I got the same error as Rene, but after a while no errors occurred, but the generation stays empty... Do you have an idea where this could have gone wrong?

 

 

SF version is 2022.1

Userlevel 7
Badge +19

Hi @Kasper Reijnders ,

In 2022.1, the default Strategy of Control procedures has changed to Staged (from Delete). This also affects how the SQL-code of the Control procedure should be written. To make this Control procedure work, change the strategy to Delete.

More info on the Staged strategy here: https://docs.thinkwisesoftware.com/docs/sf/functionality#managing-code-generation-with-staging-tables

Userlevel 4

Nice! That indeed works. However, now I am facing the following issue. I want to add this to a calculated field so the user always sees the right word: (30 days or 30 dagen) 

The result however is NULL. 

 

 

Userlevel 7
Badge +19

When you run the following code on your end product database, is the result still NULL?:

EXEC sys.sp_set_session_context N'tsf_appl_lang_id', @value = 'ENG'

select dbo.tsf_translate(0, 'days')

Can you confirm that the user's language is indeed a language that has a corresponding translation in this function? For instance, if your user language is Portuguese and you do not have any translations for this language, then no translation can be returned. 

Userlevel 4

This one also returns NULL. 

Userlevel 7
Badge +19

Could you share the Function code? It seems there must be something wrong there then; or else sp_set_session_context is not working properly.

Userlevel 4

Crap I see where I went wrong, I assumed the number to be the actual number, but it is the type. Therefore the code we should use is not 

dbo.tsf_translate(0, 'days') 

but 

dbo.tsf_translate(2, 'days')

 

Userlevel 7
Badge +19

I meant the Generated code to see that the translation is indeed in the Function :sweat_smile:

Such like this one:

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
)

For the language you use, type_of_object = 0 and transl_object_id = 'days', there should be a record

Userlevel 4

I see, my edit and your response were almost at the same time. 

 

It works in Windows 

But not in universal

 

Userlevel 7
Badge +19

I see, my edit and your response were almost at the same time. 

 

It works in Windows 

But not in universal

 

Great!, for Universal GUI, do you run the application via IAM or SF?

When running the application via SF, the Language of the User is used. This field is not mandatory and can therefore result in not having a language. 

My user with English as language

When running the application via IAM, the Application language of the User is used. This is a mandatory field and that should mean that session_context should at least return a language. Then that language with translation should of course exist in the tsf_translate function to return the correct translation.

Userlevel 4

Currently directly to the SF.

Both IAM and SF have ENG/English as language set. For this user.

 

Userlevel 7
Badge +19

Could you report a ticket for this in TCP? We need to investigate your situation. From what you show and describe, it all should work just fine. 

Userlevel 4

Yes I'll do that :)