Skip to main content
Question

Sorting on Del no in Sales order

  • June 21, 2023
  • 1 reply
  • 64 views

  • Do Gooder (Customer)

Is there a way to  sort the Delivery number Alphanumeric from for example 1 to 100. Now it sorts all numbers 1, 10 ,11, etc, and then 2, 20, 21 etc. This is very unclear. 

 

1 reply

Michael Kaiser
Sidekick (Customer)
Forum|alt.badge.img+9
  • Sidekick (Customer)
  • 117 replies
  • November 7, 2023

Hi Jchi,

I’m not very good with custom fields but when you use PL/SQL the code should look like this:
substr( CONCAT(TO_CHAR('00'),TO_CHAR(rel_no)) ,-2    )

The problem with different number lengths is very common.
(calendar weeks, calendar months, etc)
We solve that with “leading zeros” and the RIGTH function in MS SQL.
In ORACLE you have to use substring with a negativ number; here -2.
If you have 100 and above (not very probably) you can use 3 instead of 2 (digits).

Please let me know how to add a custom field with this code.
Good luck.
Michael