Order of records with multiple numeric indices 1

[anthonygalea]

I use FPD version 2.6.
I have a file with a numeric field and a character field. I would like to index first on the numeric field and then on the character field. I am using the following:

index on 'numeric_field_name' + val(character_field_name) to 'xxx'

However, the records are not shown grouped on the first field and then ordered on the character field. What am I doing worng?

 

[alvechurchdata]

'numeric_field_name' + val(character_field_name

The best way to mix numeric and character fields is by adding them as strings, not as numbers. With your index, 1 + "10000" will come out as 10001 and will appear a long way after 2 + "10" which gives you 12.

Convert the numeric field to a string then pad with leading zeroes so that it will sort properly:

PADL(ALLTRIM(STR(numeric_field_name, 10, 0)), 10 , "0") + character_field_name

You need the leading zeroes so that you get:

"00001", "00002", "00010", "00011"

rather than the simple alphabetical order of:

"1", "10", "11", "2"

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[anthonygalea]

Thanks alvechurchdata. That was a great help.