Skip to Content
avatar image
Former Member

Item code sorting issue

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • 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?



    Add comment
    10|10000 characters needed characters exceeded

  • 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

    Add comment
    10|10000 characters needed characters exceeded