4 Replies Latest reply on Jul 25, 2015 9:36 AM by philmodjunk

    Primary key field sorts out of order

    JosephFalduti

      Title

      Primary key field sorts out of order

      Post

      I have a portal linking to another table that I am trying to sort in descending order by primary key field, so that the most recent entries appear at the top.  Some of the recent entries have been sorted down lower and mixed in with older entries.  Upon checking the table itself and sorting by primary key field, I realized that after 1000 entries, the field is sorting by the first 3 digits.  This might have been my mistake, as I set the automatic entry to be three digits, like "Note001" with increments of one.

      Now I'm getting a descending sort that looks like this:
      note1122
      note1121
      note112
      note1117
      note1116...
      note1110
      note111

      Is there a way I can fix this without having to manually reset all this?  Keep in mind that this is data linked to another table.  Though, it's linked through the primary key field of that other table, so I think changing the IDs shouldn't be a problem.

      Thanks.

        • 1. Re: Primary key field sorts out of order
          SteveMartino

          That's the way text sorts.  If you change the field to a number type, it will sort properly.  Don't know if it will cost you other problems.

          • 2. Re: Primary key field sorts out of order
            philmodjunk

            If your numbers included leading zeros so that the numeric portion had the same number of digits in every case, then your values would sort as text to become:

            note1122
            note1121
            note1117
            note1116
            note1110
            note0112
            note0111

            • 3. Re: Primary key field sorts out of order
              JosephFalduti

              Is there an ideal way to fix this without having to manually re-enter them?

              • 4. Re: Primary key field sorts out of order
                philmodjunk

                I would first see if changing the field type to number works for you as Steve suggested. That's the simplest fix.

                It's possible to set up a calculation to use with Replace field contents to add leading zeroes where needed, but you are updating keys here and that should be avoided where possible as a mistake can scramble your data. (And do it on a copy of your DB first before doing it for real, then make a back up copy before doing it on your "live" file.)

                Left ( YourField ; 4 ) & Right ( "0" & GetAsNumber ( yourField ) ; 4 )

                But you will need to change your Primary key in the parent table in exactly the same way for them to match. And if other tables have a foreign key that matches to this field (or another foreign key with the same value), they will need an identical update.

                But also consider how these values are initially generated as the method that initially enters them will also need to be updated.

                Which is why a switch to a number data type could be a much simpler fix.

                And you could also add a calculation field to the portal's table: GetAsNumber ( ForeignKeyHere ) and sort on it instead of the original key if you don't want to make any changes to primary and foreign keys--which is the safest option here.