How to make query with orderBy on Translated Text field?

peter_repan
Tera Guru

‎10-08-2015 11:41 AM

Hi all,

we have a column "Title" with type Translated text. In database we have English and German translation of this field.

English version is default and German version is stored in sys_translated_text table.

When I set language of my user to German and run GlideRecord query with orderBy('u_title'), then it's ordered not by German, but English titles.

So the results are sorted in wrong order.

It seems as OOB behavior of ServiceNow (Using Translated Text - ServiceNow Wiki).

Since we are displaying all records from table in macro, we can fetch the values from database in "wrong" order and sort the items on the client (f.e. using jQuery) in onload script.

Does anybody have an another (smarter) solution for sorting tables with such a column?

0 Helpfuls
  • 1,602 Views
1 REPLY 1

Valor1
Giga Guru

‎10-12-2015 06:15 PM

You'd have to query against the "sys_translated_text" table ordering by the German, loop through the results, adding the Document ID (sys_id of the   to an array.



Then, once you have that array, do a gr.get(sys_id) on each one of the sys_ids in the array, in order, and build your Macro display.



This would be "costly" from a DB/lookup perspective, so if you have a large result set, it would probably be prohibitively laggy.


0 Helpfuls