1 2 3 Previous Next 35 Replies Latest reply on Jan 26, 2017 12:49 AM by behedety

    Sort a layout with specific alphabetic order ?

    behedety

      Hi everyone,

      I'm also quite new to FileMaker and had to learn on my own so not quite good with Scripts and everything yet.

       

      I'm working on a lexicon for a dead language and would like to be able to sort my entries according to a specific alphabetic order but can't figure out how to do it. I tried with a "Value List" but it's not working and I'm not knowledgeable enough with the "Scripts"

       

      There is also a catch since the "alphabet" we are using makes a difference with capitals ("A" and "a" are not the same letter).

       

      So is there a way to sort all my entries by this kind of order? :

      "A, j, a, w, b, p, f, m, n, r, h, H, x, X, ..."

       

       

      Thanks a lot for the help !

       

       

       

      P.S. for those wondering, it's called "transliteration", which is to convert in roman letter a text that is in a different writing system, and what I'm working on is ancient Egyptian.

        • 1. Re: Sort a layout with specific alphabetic order ?
          JackRodges

          One side method would be to create a word list and assign each word a sort order. You can update the value in the sort order field using replace and incrementing the values by 10 or 100 allowing the insertion of new words.

           

          Old list

          100

          122

          123

          130

           

          Replaced

          100

          200

          300

          400

           

          You could also create a sort field using code or char and substitute with an initial space

           

          substitute(text

          ; [ "A" ; " 001" ]

          ; [ "j" ; " 002" ]

          etc

          )

          Now your calc word would be < 001 004 033 0123>  etc. which might sort ok and avoid the text problem

          • 2. Re: Sort a layout with specific alphabetic order ?
            philmodjunk

            Hmmm, maybe a "sort field" that's actually a calculation with a result type of number. Use a custom function to replace each character with a two digit numeric value and then concatenate a fairly large number of trailing zeroes, but in a manner that adjust to produce each number with the same number of digits...

             

            Just brainstorming here. There are several folks here in the forum that delight in crafting custom functions for such purposes and maybe this post will spark one of the to produce a full up CF for this...

            • 3. Re: Sort a layout with specific alphabetic order ?
              behedety

              I've already sorted the entries by number but this number is the order in which they appear in the text, one text after the other. So far, I've got 730 entries, and I am not yet halfway done so... yeah. I guess this could work fine for a small amount of words.

              Thanks for the idea

              • 4. Re: Sort a layout with specific alphabetic order ?
                richardsrussell

                Just to be clear, when you say "lexicon", you're referring to a word list, right? Like this?

                     apple

                     bear

                     candy

                     dendritic

                     esophagus

                 

                Not to a passage of text, such as "The bear believed the apple to be candy, but encountered dendritic difficulties when it entered his esophagus."

                • 5. Re: Sort a layout with specific alphabetic order ?
                  philmodjunk
                  I've already sorted the entries by number

                   

                  The trick is to calculate a value from the letters of the word that produces a value that you can sort on to get the desired order without having to manually assign such an order to each word in your table. Hence a custom function that works from the letters in your string should be able to do this. A custom function makes sense here as you'd need to loop through the letters of the word to calculate the needed numeric value--something a CF can do using recursion.

                  • 6. Re: Sort a layout with specific alphabetic order ?
                    JackRodges

                    Use the idea I posted to create the sort as it is letter by letter.

                     

                    This string prevents an error caused by summing the values of the letters which might result with similar letters like ear and are if you just summed the code() of the letters.

                    • 7. Re: Sort a layout with specific alphabetic order ?
                      behedety

                      Yes, what I'm doing looks like that :

                       

                      1 = Why

                      2 = is

                      3 = a

                      4 = raven

                      5 = like

                      6 = a

                      7 = writing-desk

                       

                      So it's a text but word by word, each entry being a "different" word.

                       

                      JackRodges

                      I think I misunderstood what you wrote.

                       

                      So there is a way to  associate a value to a letter and then use that valur to sort the file, is that right ?

                      • 8. Re: Sort a layout with specific alphabetic order ?
                        philmodjunk

                        Since no one else took up my suggestion for a custom function, here's what I suggest.

                         

                        Two custom functions. The first is used only once to get the needed list of values that you then use in the second custom function. You may want to hang on to this CF in case you find that your sort string needs updating.

                         

                        First function:

                        SortCodes ( SortString ) =

                        Case ( IsEmpty (SortString ) ; "" ;

                                  Code ( Left ( SortString ; 1 ) ) & "; " & SortCodes ( Right ( SortString ; Length ( SortString ) - 1 ) )

                                )

                         

                        Use it just once as a watch expression in the data viewer like this:

                        SortCodes ( "AjawbpfmnrhHxX" )

                        this produces this text:

                         

                        65; 106; 97; 119; 98; 112; 102; 109; 110; 114; 104; 72; 120; 88;

                         

                        Paste that string of semi-colon delimited values in place of the red text in this custom function:

                         

                        SortCode ( TheWord ) =

                        Let ( SortCodeList = List ( 65; 106; 97; 119; 98; 112; 102; 109; 110; 114; 104; 72; 120; 88 ) ;

                                Case ( IsEmpty ( TheWord ) ; "" ;

                                          Right ( "0" & GetValue ( SortCodeList ; ValueCount ( Left ( SortCodeList ; Position ( ¶ & SortCodeList & ¶ ; ¶ & Code ( Left ( TheWord ; 1 ) ) & ¶ ; 1 ; 1 ) ) ) ) ; 3 ) &

                                                  SortCode ( Right ( TheWord ; Length ( TheWord ) - 1 ) )

                                          )

                                )

                         

                        I originally suggested a number result but upon further thought, set up a calculation field to use this second custom function with a TEXT result to turn your word into a string of digits that you can use for sorting. It replaces each letter with a different 3 digit number. With text based sorting, this string of digits should sort your records into the order specified in your sortstring used with the first custom function.

                         

                        Note: You haven't provided the full string of characters in their sort order, only a sample. You'll need to use the first function to get a list of values to use in the second one by replacing "AjawbpfmnrhHxX" with the full list of characters in the desired order.

                         

                        2nd Note: if you do not have FileMaker Advanced, you need to get such a copy, but you can also use looping scripts in place of these two custom functions to get the needed values.

                        • 9. Re: Sort a layout with specific alphabetic order ?
                          philmodjunk

                          Hmm, after more thought, the second function needs to be changed to report the list position, not the Code value at that position for each letter in the word. This returns a two digit value for every character in the word on which to sort.

                           

                          Let ( SortCodeList = List ( 65; 106; 97; 119; 98; 112; 102; 109; 110; 114; 104; 72; 120; 88 ) ;

                                  Case ( IsEmpty ( TheWord ) ; "" ;

                                             Right ( "0" & ValueCount ( Left ( SortCodeList ; Position ( ¶ & SortCodeList & ¶ ; ¶ & Code ( Left ( TheWord ; 1 ) ) & ¶ ; 1 ; 1 ) )) ; 2 ) &

                                                     SortCode ( Right ( TheWord ; Length ( TheWord ) - 1 ) )

                                            )

                                   )

                          • 10. Re: Sort a layout with specific alphabetic order ?
                            JackRodges

                            By the word would be really easy if you use a related file.

                             

                            Let's say you have 1000 words. So you need 1000 records. Each record can have a unique ID number.

                             

                            Word Field for each record

                            Character fields for one character: first, second, third, etc. characters. This is filled by script grabbing the first, second, third character of the word.

                            Sort number field for each character: first, second, third, etc This will be filled via the relationships below.

                             

                            Create a table that has the alphabet with a field for character order:

                             

                            A = 1

                            B = 2

                            etc.

                             

                            Create a relationship for each of the character fields listed above to this alphabet table. One relationship for first character, one for second character, etc.

                             

                            Then add a calculation for each of the sort number fields that grabs the sort number for the particular letter.

                            Relation A: nbr

                            Relation B: nbr

                            Relation C: nbr

                             

                            Now your word list has your assigned sort values for the characters in these three (or more fields).

                             

                            Then you sort one Sort 1, Sort 2, Sort 3... and your word list is sorted by the assigned alphabet order.

                             

                            This idea can be adapted for any language.

                             

                            If my description is less than clear I can create a sample.

                            • 11. Re: Sort a layout with specific alphabetic order ?
                              Philip_Jaffe

                              You might also try changing the text field's default language from English to Unicode.

                               

                               

                              (In manage database, select the field > field options (CMD-O or CTRL-O) > Storage tab > Default Language dropdown list.)

                               

                              Then enter your desired character order in a custom value list and sort by the value list.  This accounts for upper and lower case separately.

                              • 12. Re: Sort a layout with specific alphabetic order ?
                                user19752

                                There may not be so many characters in the language, so you can use

                                Char(n)

                                for substituting, n is sort order, with some adding to avoid using control characters.

                                 

                                730 entries? Don't you want sort words like as in dictionary?

                                Are there some "combination of character" to be sorted as one character?

                                • 13. Re: Sort a layout with specific alphabetic order ?
                                  behedety

                                  Well, one of the long term plan is to make some sort of a dictionnary for the texts that I'm working on, so I need to place them in "alphabetical" order, but not by "our" standard, by "theirs". As for the "combination of character", do you mean like "th" being one character? If so, no; those "sounds" are recorded as one letter (ex. kh = x, sh = S), which can make some weird words like "sDm" (to listen).

                                   

                                  I ckecked the "Char(n)" in FileMaker's references and it sure can be usefull, but how am I to use it to sort in a script?

                                  Wouldn't changing the default language to Unicode, like what Philip_Jaffe said, be simpler or better?

                                  If I change to Unicode, or if I do the "Char", how do I sort after that?

                                  Isn't the custom fonction from philmodjunk doing fine?

                                   

                                  As I said, I'm not so experienced yet so I trust you guys.

                                   

                                  By the way, I'm working on FileMaker 15 Pro, not Advanced.

                                  • 14. Re: Sort a layout with specific alphabetic order ?
                                    Philip_Jaffe

                                    Ok.  I have a solution.  It's not the most elegant, but since you don't have FileMaker Pro Advanced and can't take advantage of custom functions, this is what I came up with.  It works and it's fast.  But it will take a little setting up.

                                     

                                    1.  Create a custom value list using your custom alphabet (FIG 1)

                                    2.  Determine what the maximum character length of any word in your lexicon may be (eg. 50 chars)

                                    3.  Create calculation fields for each character (I called mine Char01, Char02, Char03, etc...)

                                         a.  The calculation result should be text

                                         b.  The formula is Middle ( yourtextfield ; characterposition ; 1 )  (FIG 2)

                                         c.  In Storage Options... set default language to Unicode and Indexing to ALL (FIG 3)

                                    4.  Set your sort order based on the value list and sort by each char field (FIG 4)

                                     

                                    Your text is now sorted according to your custom alphabet!  (FIG 5)

                                    1 2 3 Previous Next