Sorting in unsupported language (Klingon)

[Robyn]

I understand that Klingon is not supported as an interface language because while it would be fun, no one really needs it, but I and others do have a need to sort and enter Klingon text.

The problem is that the Klingon alphabet is as follows

a b ch D e gh H I j l m n ng o p q Q r S t tlh u v w y '

So some of the "letters" are more than one letter and one of them is the English apostrophe. Note also that Q and q are different letters.

If I want to sort text alphabetically, the words starting in {'} end up first and the words starting in tlh end up in the middle of the t words. Traditionally one gets around this by replacing ch with c, gh with f, ng with nx, Q with Qx, tlh with tx and ' with z, sorting and then reversing the replacements. Does the power of Open Office give me a better way? I thought the custom sort order would help, but it's only for small closed lists like names of months and weekdays, right?

Also is there a way to turn off interpretation of ' as a formatting command so that when I enter a word like 'urmang into a cell, I don't have to remember to type two apostrophes?

Thank you everyone for working on and supporting this product and thanks to Hagar Delest for pointing me here when I e-mailed him.

[MrProgrammer]

Also is there a way to turn off interpretation of ' as a formatting command so that when I enter a word like 'urmang into a cell, I don't have to remember to type two apostrophes?

Preformat the cells where you will enter Klingon as Format > Cells > Numbers > Text. They are text and not numbers. The prefix apostrophe is only needed when entering data into a non-Text cell and when one needs to bypass interpretation of the value as a number. In a text cell you would simply type 'urmang. (If you type ''urmang in a text cell it will begin with two apostrophes.) Even better than Format > Cells, use Format > Styles and Formatting, build a Text style for these cells and apply that.

Does the power of Open Office give me a better way?

There's no simple way to do this. The "easiest" method is probably writing macro functions to perform the translations that you have suggested. Translating the values via Calc-supplied functions will be very difficult or impossible. I believe you will need to write the function's skeleton in Basic but you can do most of the work in a Python, BeanShell, or JavaScript subroutine if you prefer. Other volunteers can correct me if I'm wrong about that. Since Klingon seems to have 26 letters I would recommend that you translate them all to single upper case letters A though Z. The translated words won't be as readable but I think you are less likely to run into trouble that way. There's a separate forum for macro topics.

[Robyn]

Thank you very much. I've been playing with macros, so I'll take the problems I've encountered there over to the macro forum.

[kingfisher]

You could try Tools > Options > Calc > Sort lists. Type the alphabet in a column and select the range before trying the menu selection.

If you define the range being sorted as a database range ( Data > Define ), the sort parameters will be remembered.

I have seen similar questions in other threads. The only keyword I remember is 'SUBSTITUTE' (referring to the SUBSTITUTE function). You may find a solution by searching the forum boards.

[kingfisher]

Thinking over this again, the creation of a sort list should solve the problem. Do not, however, use the tool bar icons to sort. Use Data > Sort and pay particular attention to the sort settings on both tabs. On the second tab you can select a custom sort list.

[floris v]

I encountered a similar problem years ago when I wanted to sort lists of Dutch words with accented letters like é in them - at the time such words were not sorted correctly if you sorted by ascii values. So I defined a list of records with two fields: the real word and the word converted to "flat" ascii - without the accents. Then I used a separate compare function that would compare the converted words instead of the real ones, and in that way the list could be properly sorted. After that the real words could be saved sorted without any conversion back being necessary. I don't know if that can be done in OOo BASIC.

[Charlie Young]

The sorting by ascii values that floris v alludes to is the real heart of the problem. In strict ascii all uppercase letters are before all the lowercase letters, so "Z" is before "a" (and so on). This all comes down to collation, which is a pretty big topic. OO supposedly uses ICU Collation, which has packages for c++ and java (ICU = International Components for Unicode). I don't think either OOo or the ICU has defined a locale for Klingon, but it may well be possible. The OOo documentation has a page on implementing a new locale, and the ICU docs suggest it is possible to have multiple character "letters." I have only recently been looking at OOo's XCollator in another project I'm playing with, but there I'm just using the default collator for a given locale. The US-en default puts lowercase letters first (a A, b B..) and allows one also to ignore case with the compareString method. As the documentation also indicates, collation is slow. A sort that just uses straight string comparisons is several times faster than one that uses collation, but if one works with the OOo OUString class in c++, one finds that all the comparison functions/operators come withe the caveat "This function can't be used for language specific sorting. "

[Robyn]

The database is not large enough that collation speed should be an issue. I may take the quick and dirty approach of sorting based on a hidden column that remains in mangled but sortable form, "flat" as floris v calls it. I have successfully recorded a macro that will convert a column to the sortable form, but for some reason when I try to undo that sort I keep getting "not found" errors. I think it keeps unselecting the range.

Is there a way to view the code the program creates when you 'record' a macro, so that I could edit it?

I tried using the alphabet as a sort list, but as I suspected that can be used only when sorting a list that consists only of members of the sort list.

I'll take my macro problems over to the macro forum, but did want to assure you that your suggestions here are appreciated. Mr. Programmer, simply turning off the need to type a double apostrophe has been a great help.

[Charlie Young]

The database is not large enough that collation speed should be an issue. I may take the quick and dirty approach of sorting based on a hidden column that remains in mangled but sortable form, "flat" as floris v calls it. I have successfully recorded a macro that will convert a column to the sortable form, but for some reason when I try to undo that sort I keep getting "not found" errors. I think it keeps unselecting the range.

Is there a way to view the code the program creates when you 'record' a macro, so that I could edit it?

I tried using the alphabet as a sort list, but as I suspected that can be used only when sorting a list that consists only of members of the sort list.

I'll take my macro problems over to the macro forum, but did want to assure you that your suggestions here are appreciated. Mr. Programmer, simply turning off the need to type a double apostrophe has been a great help.

You should have been asked to give your macro a name when you hit "stop record," and you should have either selected a module to put the macro in or been asked to create a new one if none existed in the current document. Just look for your macro in the appropriate module.

Meanwhile, I have been having some fun with this, and I will post the results in the macro forum, as it will be apparent that that is where discussion of it belongs.