Notifications

23 views

Symptoms

The ordering of Service Catalog Categories was not as expected when non-English language was selected for the user's session. The categories were always displayed in the same order (as per English) instead of the order of the translated text.

The customer was using a customized widget that was retrieving a list of parent categories using the following server script:

var sc = new GlideRecord('sc_category');
sc.addQuery('sys_class_name', 'sc_category');
sc.addActiveQuery();
sc.orderBy('title');

The script is trying to ordering the query results based on the "title" field.

Release

All

Environment

Internationalization Language plugins were active. eg. German and French language support was enabled

Cause

To verify behavior I ran the same equivalent query code directly against the instance as background script:

var sc = new GlideRecord('sc_category');
sc.addQuery('sys_class_name', 'sc_category');
sc.addActiveQuery();
sc.orderBy('title');
sc.addQuery('sc_catalog', 'e0d08b13c3330100c8b837659bba8fb4');
sc.addQuery('parent', '');
sc.query();
while (sc.next())
{
gs.print(sc.title)+"\n";
}

With English language selected for the session it was displayed:

*** Script: Catalog & Model Management
*** Script: Click & Collect
*** Script: Employee Lifecycle
*** Script: Group & Role Provisioning in IT4You
*** Script: IT Internal Services
*** Script: My Workplace
*** Script: Rentals
*** Script: Services

When German language was selected the script returned:

*** Script: Katalogverwaltung
*** Script: Click & Collect
*** Script: Mitarbeiter Lifecycle
*** Script: Gruppen & Rollenbereitstellung
*** Script: IT interne Services
*** Script: Mein Arbeitsplatz
*** Script: Verleihung
*** Script: Services

This confirms that the title field which is type "Translated Text" is being successfully translated but the ordering is always based on the English title text.

This is also confirmed by debugging the SQL. The following statement is executed regardless of what language is currently selected:

SELECT ... FROM (sc_category sc_category0 INNER JOIN sys_metadata sys_metadata0 ON sc_category0.`sys_id` = sys_metadata0.`sys_id` ) WHERE sys_metadata0.`sys_class_name` = 'sc_category' AND sc_category0.`active` = 1 AND sc_category0.`sc_catalog` = 'e0d08b13c3330100c8b837659bba8fb4' AND sc_category0.`parent` IS NULL ORDER BY sc_category0.`title`

It is relying on the SQL query to order the category data based on the current value of the title column (eg. the English key value).

When Greman language is selected, there is then an additional query executed to perform the translation after the initial English data has been returned.

SELECT ... FROM sys_translated_text sys_translated_text0 WHERE (sys_translated_text0.`tablename` = 'sc_category' OR sys_translated_text0.`tablename` = 'sc_category') AND sys_translated_text0.`documentkey` IN ('9966584d37d276001d982b2943990e64' , '825d7838dbf72340d01c2b43059619e3' , '8e055cc937d276001d982b2943990ec2' , '2fd74630377cea001d982b2943990e03' , '36565c4d37d276001d982b2943990e43' , 'a455504d37d276001d982b2943990e58' , '9f46584d37d276001d982b2943990efc' , 'e716184d37d276001d982b2943990e7e') AND sys_translated_text0.`fieldname` = 'title' AND sys_translated_text0.`language` = 'de'

 

 

Resolution

I found an old problem that was raised to address your observed behaviour:

PRB576612 [TBF] Translated fields still using English Sort Order

It was closed by development as "Won't Fix" as it was designed to work in this way eg. sort based on key value.

The only way to modify the way that this is operating would be to modify the widget server code that implements your custom widget and populates the data property set with category data. For example, read the translated titles into an array and then implement your own custom sort logic to reorder it based on the translated text value and place into the data structure in your desired order.

Additional Information

This is not a Service Portal specific issue. It is a general issue related to trying to order the results of GlideRecord query base on the value of a translated text field.

Article Information

Last Updated:2019-04-16 08:09:17
Published:2019-04-16