Either a standalone script or a button which calls that script would work depending on your situation.
If you want to use a button to call a script...
Create the script to do the sorting you want and then in the Button Setup, you can call the script.
Hope this helps.
In addition to the advice from Mork, you can also create 2 additional fields from the same table that don't have to be on layout. They are both auto enter fields, that will parse out the id number. Assuming your id field is named YourIdField:
1. IdNumber, type-number, with an auto enter calc, uncheck "Do not replace..." with the following calc:
Middle ( YourIdField ; 2 ; 6 )
2. IdLetter, type-text, with an auto enter calc, uncheck "Do not replace..." with the following calc:
Left ( YourIdField;1 )
Then in your sort script, you sort by: IdNumber (ascending) and IdLetter (ascending)
thanks both of you!
Mork, could you please suggest the script for my case? i want to sort the code by numbers instead of alphabets. thx
If the number part doesn't start with a zero, you could create a new calculated field with calc: GetAsNumber( Code ) and then sort by that new field. Try this by hand and, if the result is acceptable, create the script.
raybaudi's suggestion above looks like the way to go. It's simple (which is good) and it works...see screenshot below.
All you need to do is create the calculated field and then sort by that field. Your script would really only need one line in it.
You could also get this sort in a SQL statement like the one below (assuming you always have one leading character and five digits to the right):
ExecuteSQL ( "select RIGHT(code, 5) as sortedCode, code from testDB order by sortedcode" ; " " ; "¶" )
If your codes were more complicated, like a variable, but understandable, number of leading characters, you could still do basically whatever you want by using the text functions like "Position" and others to extract just the parts you need in a more complicated script. Copy those pieces to another field for each record in a script, and, again, sort on that column.
Hope this helps.
Edit: If I did Raybaudi's method correctly, it didn't work for me
GetAsNumber ( Code ( YourIdFieldHere ) )
Consider the records:
Sort ascending, by using the Code calculation, puts them in that exact order
Steve, my calc was simply:
GetAsNumber( Code )
where Code is the field name.
Tried that too. Sorts the number fields correctly, but not the id field.
Un sorted order: Sorted by GetAsNumber(IdField):
But the part I forgot, which does work, is if you sort by Raybaudi's calc field, THEN sort by the id field, now the sort works fine, and only uses 1 extra field. It also works in my first example with just the extra IdNumber field. But keep in mind Raybaudi's method is better (if first number is not zero) and if the id is any number of digits.