Problem sorting a view based on a text field with numeric values in Lotus Notes

Question

An existing Lotus Notes database form has a text field whic contains a numeric value. I am trying create a view to sort on this field but the result is coming up as follows

99 1000 1002 1003 101 1011

Is there any way to convert the field so I can sort numerically. Lotus Notes 6.5.

Answer 1

In the column formula, convert your item to a number using

@TextToNumber( itemname )

Answer 2

Because the text field has numeric values, they are still treated as literal string. So, the easiest way I have found to format them and avoiding @Errors' is to pad them with leading zeroes as well and continue to treat them as a string.

The good thing about this solution is that we're still dealing with strings. No mucking about with data type conversions where you may have data that cannot be converted into a number.

So put this into your column formula and set the sorting options as ascending or descending as you require:

MaxSize := 10;
len := @Length(@trim(yourField));
Pad := MaxSize - Len;
@Repeat("0";pad) + @trim(yourField);

Make sure that your "MaxSize" value is greater than the length of any value it receives. From the sample you've provided, 4 looks like it will do the trick.

(As per Ken's suggestion), if the appearance of the padded column to the user is not appealing, you can make this column a hidden, sorted column and then present the original unformatted (and unsorted) column to the user so it appears in a numerical order.