Sorting on Calculated Field?

Discussion created by PatPhelan on Dec 21, 2016
Latest reply on Dec 21, 2016 by PatPhelan

I have three fields in my file that are calculated: calculated in that they are text subsets of a fourth field.


The fourth field is (city, state, structure). The other three fields are individually city, state, and structure and were calculated from the larger field using the formula:


Let (

  theList = Substitute ( Name ; [ "," ; Char(10) ] ; [ "-" ; Char(10) ] ) ;

  Trim ( GetValue ( theList ; 1 ) ) // 1 = city, 2 = state, 3 = structure



(formula which was provide by a kind FileMaker Community user)


Whenever I try to sort on these new, calculated fields I get unexpected results in that they do not sort correctly. I'm trying to sort by State, then City. The State appears to sort correctly, but the cities do not begin to sort.


I'm relatively new to Filemaker, but have never had any problems sorting. On the other hand, I've never tried to sort a calculated field. Is this what is causing my problem, or could there be something else?


Many thanks for any input.