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

Idea ID 2703441

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

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

clipboard_image_0.png

5 Comments
Micro Focus Expert
Micro Focus Expert

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.

 

Micro Focus Frequent Contributor
Micro Focus Frequent Contributor
Status changed to: Waiting for Votes
 
Micro Focus Expert
Micro Focus Expert

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...

Micro Focus Frequent Contributor
Micro Focus Frequent Contributor

@AFranke thank you for the guidance here.

Micro Focus Frequent Contributor
Micro Focus Frequent Contributor
Status changed to: Already Offered
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.