Idea ID: 2703441

Primary Key sort numerically instead of alphabetically for tables using a number record

Status : Already Offered
Already Offered
See status update history
over 1 year ago

Sorting on a Primary / Unique Key on a table that uses a number record containing a prefix or suffix does not sort numerically but sort alphabetically.

So for anything using a number record and you attempt a sort of the primary key it's not a numerical sequence, it's alphabetical, which annoys a large chunk of our customers as the "Last Page" isn't the last records or first records submitted it's just whatever came last or first in the alphabetical order.

e.g. List will display as:

- RF100289

- RF10029

- RF100290



  •  thank you for the guidance here.

  • The issue is that these number fields are actually strings - and therefore sorted alphabetically.

    This is no trouble as long as the numeric part is always the same length. So zeros to filling up from left side to would help.

    The way to configure this is in the sequential numbers:

    1. database manager, access table "number"

    2. search for the number record to edit (i.e. "incident management")

    3. Find near bottom how the string representation for new numbers is constructed and increase the "lenght" to a size sufficiently large. Think about how many records will be opened in the next n years.

    Note: This change is for new numbers. Existing records will keep their number. As the number might be used by other records referencing the incident, changing existing records is a difficult task...

  • I believe this is by design since the value is alphanumeric. However, in SM, most of the id field consist of 2 alpha characters as prefix, followed by numbers. I like the idea of redesigning the sort to be more user friendly. Break the value to the 2 alpha characters section and the numbers section. If the view display more than one type of records, sort alpha section and then sort the numbers section. If view display only one type of record, sort only the numbers section.

    Most common users are not programmers and do not understand difference between alphanumeric and numeric sort. To them, the sort is broken, it cannot even sort the numbers. We should redesign the sort so it caters for the majority users.