issue sorting by field with decimals as separators

[erickaz]

I am working with an inventory database where each record has a number which is formatted with a letter prefix followed by a number, sometimes sub-numbers.

example

TS.1

TS.2.1

TS.2.3

TS.10

TS.15

TS.15.1

TS.20

My issue is now sorting it as it appears above, whether I set the field type as number or text, it still sorts like this.

TS.1

TS.10

TS.15

TS.15.1

TS.20

TS.2.1

TS.2.3

Any suggestions on how I can sort them as if they were files in finder on a mac.

Thank You.

[eos]

Create a new calculation field with this formula; substitute mySerialNumber with your serial number field.

Make sure result type is number. Then sort by the new field.

The formula assumes that, as in your sample data, the prefix is three characters long

and the sub-number is one digit. Adjust it for other formats.

Let (

[

theField = Replace ( mySerialNumber ; 1 ; 3 ; "" ) ;

hasPoint = PatternCount ( theField ; "." ) ;

pointPosition = Position ( theField ; "." ; 1 ; 1 )

] ;

Case (

hasPoint ;

Left ( theField ; pointPosition - 1 ) + ( Right ( theField ; 1 ) / 10 ) ;

theField

)

)

[comment]

You'll have to sort by a calculation field (result is Text), e.g. =

Let ( [

words = Substitute ( OriginalField ; "." ; " " )

] ;

LeftWords ( words ; 1 ) & SerialIncrement ( "000000000" ; MiddleWords ( words ; 2 ; 1 ) ) & SerialIncrement ( "000000000" ; MiddleWords ( words ; 3 ; 1 ) )

)

The original field should be of type Text.