Sorting on document number doesn't work

[The Learner]

Hi,

I'm sorting a portal on Document Title, Document Author and Document Number. The sorting is done by clicking on column headers, using the script and calculated fields below. The problem is with the sorting on the document number. It does not really work as for instance, in ascending order, document number 786 displays before document 85. That's because the sorting is done on the first digit, so adding a zero in front of 85 would resolve the issue, but renumbering documents 1 to 999 is not an option. So based on my research, it looks like the solution to this (or at least one solution) would be for the sorting script to add three invisible 0 in front of document numbers 1 to 9, to add 2 invisible zeros in front of documents 10 to 99, and to add 1 invisible zero in front of documents 100 to 999. Documents 1000 and up will never be an issue as the database will never contain more 9999 documents.

Would anyone be kind enough to have a look at this and see what the best solution is, and explain how to implement it.

Thanks for your help.

 

[comment]

Try:

SerialIncrement ( "0000" ; Number )

 

[The Learner]

Thanks, but where? In the script?

[comment]

In the calculation fields you are using to sort the portal (at least I assume that's what you do - your post does not say so clearly). So instead of:

Case ( 
...
$$sort = "Number; ascending" ; Number ;

use:

Case ( 
...
$$sort = "Number; ascending" ; SerialIncrement ( "0000" ; Number ) ;

and likewise for the descending order field.

[The Learner]

Thank You very much. This was a brilliant solution.