You would do this with a calculation and sort on the calculation in stead of the TitleField. This calculation will make any title that looks like this:
A walk in the park
The great gatsby
look like this:
walk in the park, A
Great gatsby, The
The calculation would be:
(Where TitleField is the name of the field with the title)
FirstWord = LeftWords(TitleField;1);
If(FirsWord = "a" or Firstword = "The"; RightWords(TitleField;WordCount(TitleField) - 1) & ", " FirstWord; TitleField)
Then, sort on this field. Make sure this calculation is indexed
This calculation does work. When I try to close/save, it highlights the last occurance of "FirstWord" and error reads:
"An operator is expected here"
FirstWord = LeftWords(titleField;1);
If(FirstWord = "a" or Firstword = "The"; RightWords(titleField;WordCount(titleField) - 1) & ", " & FirstWord; titleField)
The bolded ampersand was missing by mistake.
(Replace titleField with the name of your field)
OK...the script works but...when I try to sort on the calculation field (titleSort) it does sort!!
Where is the calculation entered in at? Is it under Manage > Database, then choosing the title field and entering the calculation?
I believe DeltaTango is suggesting creating a NEW field as he described and then sorting by that field rather than the (already existing) title field.
THAT'S what I missed. I didn't have the calculation set to text!!! Sorry about that. Thanks for all the help, This forum has been very helpful. I am a newbie (only been using FM for 2 months) so I need all the help I can get. I do recommend the book "FileMaker Pro 10 - the missing manual" by Susan Prosser & Geoff Coffey
For whatever reason I still can not seem to get it to work. I have made sure that it is set as a text field but whenever I go to sort after entering in the calculation, applying it and it doesn't affect it all. I have tried in multiple new databases and different fields. Not sure what I am missing.
what field are you sorting by? it should be the calculation field.
Also, "whenever I go to sort after entering in the calculation, applying it and it doesn't affect it all" is scaring me a bit.
This isn't something you should have to do more than once. You create the calculation field and forget about it. Then just enter the text in the regular text field and sort by the CALCULATION field.
Alright now I got it working. Sorry for not understanding right away. Thanks everyone for your help. :)