Home > Forum > Tips&Tricks > SQL command: Populating a multilingual choice field with form type names

SQL command: Populating a multilingual choice field with form type names
4

MVP

Hi everyone,

I had the requirement to provide a drop down which can be used to select a form type. Since I'm a fan of doing things the right way I've chosen to provide a multilingual label.
Example:
TMeasure$$de$$Maßnahme$$en$$Measure

I'm not sure whether I'm missed something because of the late hour, but I didn't found a better option than creating this on my own.
If someone else has a similar need, either selecting form types, workflows or whatever this command can be used as a template:

select DTYPE_ID
, DTYPE_Name+isnull((
select distinct '$$', substring(LAN_Name,1,2) +'$$'+TRANS_Name
FROM WFDocTypes as innerDocType join Translates on TRANS_ELEMID = DTYPE_ID and TRANS_OBJID = 13 join TranslateLanguages on TRANS_LANID = LAN_ID
where innerDocType.DTYPE_ID = outerDocType.DTYPE_ID
FOR XML PATH('')
),'') as Label
FROM WFDocTypes outerDocType
where DTYPE_ID in ({DT:4},{DT:7})

The TRANS_OBJID is fixed. You can get an overview of all ids and which fields, tables you need to replace in the above command from:
SELECT * FROM [dbo].[DicTranslationsObjects]


The attached image shows the executed SQLcommand and the drop down values for the German language.

Best regards,
Daniel

MVP

Two other samples where you only need to change the outer where condition.

Workflow name
select WF_GUID as Id
, WF_Name+isnull((
select distinct '$$', substring(LAN_Name,1,2) +'$$'+TRANS_Name
FROM WorkFlows as innerWorkFlows join Translates on TRANS_ELEMID = WF_ID and TRANS_OBJID = 3 join TranslateLanguages on TRANS_LANID = LAN_ID
where innerWorkFlows .WF_ID = outerWorkFlows.WF_ID
FOR XML PATH('')
),'') as Label
FROM WorkFlows outerWorkFlows
where WF_IsDeleted = 0
and WF_ID in ({WF:2})
order by Label

Step name

select STP_GUID as Id
, STP_Name+isnull((
select distinct '$$', substring(LAN_Name,1,2) +'$$'+TRANS_Name
FROM WFSteps as innerWFSteps join Translates on TRANS_ELEMID = STP_ID and TRANS_OBJID = 5 join TranslateLanguages on TRANS_LANID = LAN_ID
where innerWFSteps .STP_ID = outerWFSteps.STP_ID
FOR XML PATH('')
),'') as Label
FROM WFSteps outerWFSteps
where STP_IsDeleted = 0
and STP_WFID = (select WF_ID from Workflows where WF_GUID = '{SI:12}')
order by STP_Order