Skip to Content

Item code sorting issue

Dec 05, 2017 at 06:58 AM


avatar image

Hi All,

we have created items



XyZ02....XYZ09 while finding the items they are not displaying is series..Say After XYZ16 XYZ17 should come but XYZ160 is coming

How to solve this issue

Thank You

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Johan Hakkesteegt Dec 05, 2017 at 07:29 AM


The Item Code is text. and as such, it will sort accordingly. It works the same way in Excel for example.

There are two ways to deal with this, you can either accept this behavior, and plan your business process accordingly, or you can use equal length item codes, with a common pattern. So for example always 3 letters and 3 numbers: ABC123, ABC124, DEF100, DEF110, DEF120, XYZ001, XYZ012, etc.

For the sake of system reports (i.e. Sales Analysis, etc), you could use a system where the 3 letters represent an item category, and then follow with 5 or 6 numbers. In this case try to count from the end, not the beginning. In other words use XYZ00001, XYZ00002, etc instead of XYZ10000, XYZ20000

However, I strongly recommend that you try not to put meaning into Item Codes. Basically the Item Code is meant for use by the system as a unique identifier. It is better that the user use the Item Name, and perhaps the bar code, to identify an item. Using the Item Code for that purpose will come back to haunt you in the long run, trust me. Take the example above for example, what happens when an item was put in the wrong item group? Or what if you decide that you want to change your item groups completely at some point? What if someone accidentally sets up a new item in the wrong item group?



10 |10000 characters needed characters left characters exceeded
Agustin Marcos Cividanes Dec 05, 2017 at 12:21 PM


in your query, in the order clausule try to sort by len(itemcode), itemcode.

You will obtain first the items with the small code.

Kind regards

Agustín Marcos Cividanes

10 |10000 characters needed characters left characters exceeded