Hi, I want to increment the numeric numbers in code field.
For e.g. I have 1,10,2,3,4,5,6,7,8,9
I try to use findlast but it keep on increment from 9. How do I add increment from 10 onwards instead of 9?
Please help.
You’ve found out that a code type field doesn’t act like an integer type.
If it’s always going to be numbers why use a code type?
in order to have a code type sort as you “think” it should they need the same amount of characters.
example
01,02,03,04,05,06,07,08,09,10
this issue arises again once you hit 100, or 1000 etc…
unless you use 0000001,0000002, etc. but eventually that will fail too.
So rethink the type of your field.
Hi Ha101 (please update your accounts display name with your name, or “sign” your posts with your real name - like Harry does),
But Harry is correct. Code fields are behaving just like a text field and sorts that way.
The functionality you and many other users would expect, that it would sort by the numeric values, was actually present in the classic NAV using the native database. Here fields with numbers where sorted like numbers and fields with mixed numbers and characters like text.
But this is not possible with SQL Server, as SQL doesn’t have a code field. So Code fields are stored like VarChar fields. Although the SQL type Variant actually would give you the functionality similar to how it worked in the native database, except characters comes before numbers.The Variant data type wasn’t available when Navision first made their system able to run on SQL Server, and I really don’t know why they haven’t changed this, except it would mean that they couldn’t run it on a SQL Server version 7 or earlier!
In NAV you actually have the ability to change the “SQL Data Type” for code fields directly in the field. But I actually don’t want to recommend that you do this, as you then would end up with a system where the different CODE fields would behave differently, which would confuse any user.
So it’s better to follow Harry’s suggestion and add “0” to fill the fields equally.