2 Replies Latest reply on May 11, 2017 7:34 AM by jeffdpia

    Numerical sorting in FM11

    jeffdpia

      Pardon me if this has been answered already but I am having difficulties sorting files according to one field that we use quite regularly. The field contains numbers sometimes with a letter and hyphen prefix (like G-123).

      If I try to sort the numbers 1, 10, 11 and 101 the sorted return is 1, 10, 101, 11. I'm a  new FM user on the create side but I am using someones old templates and dont want to make a global mess. Any help would be greatly appreciated,

       

      Cheers

        • 1. Re: Numerical sorting in FM11
          beverly

          numbers don't sort 'numerically' if they really are text. Leading 0's for example will ruin a numerical sort.

           

          So, make a field

          GetAsNumber ( <<your field here>>) // number type result, of course

          and sort on that. the field need not be on the layout to be used in the sort.

          look at various records to verify you have the correct number. If it's working well, don't make a calculation field (stored or unstored), but an auto-enter number (by calculation) that will update for each new record.

           

          beverly

          • 2. Re: Numerical sorting in FM11
            jeffdpia

            Thanks for the reply, unfortunately folks here are intent on using Filemaker in the wrong way and I cannot amend either the programming or their behavior.