4 Replies Latest reply on Dec 18, 2012 11:27 AM by MattSchuurman

    Sorting Records Alphabetically, but skipping the word "the"

    MattSchuurman

      Title

      Sorting Records Alphabetically, but skipping the word "the"

      Post

           I would like to sort all the records in my database alphabetically, by name. Easy enough, except that some of record names begin with the word "The". When sorted, I would like all records that begin with "The" to be sorted by the next word in the name.

           For example: If there is a record named "The Coliseum", the order should look like this - Capital Centre, The Coliseum, Dover Place, etc. NOT - Texas Arena, The Coliseum, Theatre Hall, etc.

           Help?

           I'm using Filemaker Pro 9.

        • 1. Re: Sorting Records Alphabetically, but skipping the word "the"
          philmodjunk

               Use a calculation field--possibly with a custom function that excludes first words such as "The" and "a". Then sort on the value of this field instead of the current text field.

               I believe that there's a custom function for this purpose that you can find on the Dunning web site for custom functions.

               Here's a calculation you can use in a calculation field that omits first words of The and A from the field:

               Let ( [ T = YourTitleFieldHere ;
                        w = Leftwords ( T ; 1 ) ;
                      Case ( w = "the" ; RightWords ( T ; WordCount ( T ) - 1 ) ;
                                 w = "a" ; RightWords ( T ; WordCount ( T ) - 1 ) ;
                                 /* Else */ T
                               ) // case
                      ) // Let

               Select Text as the return type and specify sorting on this field instead of your TitleField.

               Note that this expression can easily be expanded to exclude other first words.

          • 2. Re: Sorting Records Alphabetically, but skipping the word "the"
            MattSchuurman

                 PhilModJunk to the rescue!

                  

                 I copied your calculation exactly, replacing YourTitleFieldHere with the proper field - "Member Name", but I'm getting an error on the open bracket between Case and w in the third line: A number, text constant, field name or "(" is expected here. Weird, right? It wants me to replace "(" with "("?

            • 3. Re: Sorting Records Alphabetically, but skipping the word "the"
              philmodjunk

                   left out a bracket:

                   Let ( [ T = YourTitleFieldHere ;
                            w = Leftwords ( T ; 1 )
                           ] ;
                          Case ( w = "the" ; RightWords ( T ; WordCount ( T ) - 1 ) ;
                                     w = "a" ; RightWords ( T ; WordCount ( T ) - 1 ) ;
                                     /* Else */ T
                                   ) // case
                          ) // Let

                   and note that there is no ; after the Leftwords ( T ; 1 ) text.