Skip to main content

Translating model objects in business logic


Jasper
Superhero

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

 

 
Did this topic help you find an answer to your question?
This topic has been closed for comments

16 replies

Forum|alt.badge.img+15
  • Superhero
  • 196 replies
  • May 27, 2021

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:

     


Jasper
Superhero
  • Author
  • 678 replies
  • May 27, 2021

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.

 


Forum|alt.badge.img+15
  • Superhero
  • 196 replies
  • May 27, 2021
Jasper wrote:

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 :-).


Kasper Reijnders
Forum|alt.badge.img+5

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


Mark Jongeling
Administrator
Forum|alt.badge.img+23

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


Kasper Reijnders
Forum|alt.badge.img+5

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. 

 

 


Mark Jongeling
Administrator
Forum|alt.badge.img+23

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. 


Kasper Reijnders
Forum|alt.badge.img+5

This one also returns NULL. 


Mark Jongeling
Administrator
Forum|alt.badge.img+23

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.


Kasper Reijnders
Forum|alt.badge.img+5

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')

 


Mark Jongeling
Administrator
Forum|alt.badge.img+23

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


Kasper Reijnders
Forum|alt.badge.img+5

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

 

It works in Windows 

But not in universal

 


Mark Jongeling
Administrator
Forum|alt.badge.img+23
Kasper Reijnders wrote:

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.


Kasper Reijnders
Forum|alt.badge.img+5

Currently directly to the SF.

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

 


Mark Jongeling
Administrator
Forum|alt.badge.img+23

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. 


Kasper Reijnders
Forum|alt.badge.img+5

Yes I'll do that :) 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings