Searching

[jamal]

A quick question:

When I sort a field with values like 1.1, 1.2, ..., 1.10, 1.11, FileMaker returnes 1.1, 1.10, 1.11, 1.2 etc, Is there a way to do the regular search which will return the values like 1.1, 1.2, 1.3,... 1.10, 1.11?

Thanks for youe help!

[John Mark Osborne]

Sounds like you are storing your data in a text field. If you want the data to be sorted as numbers rather than text, change the field type in Define Fields to Number.

[jamal]

Thanks for your help. But it doesn't work. No, it was a general number field. The problem is it doesn't work after decimial.

[comment]

It does work - as a number (1.10 = 1.1 < 1.2 ).

What you want is text sort, so change the field to type Text.

[jamal]

Thanks for your help. However, it doesn't work neither as a text nor as a number. If I use as a number, it sorts OK except the part after the decimal i.e. 1.1, ..1.10, 1.2. However, if I use as a text, it doesn't work at all. It comes like this: 1.1, 1.2, 12.5, 2.1, 1.11 etc.

Thanks.

[comment]

I see. I don't think it's possible to do the sort you want with what you have. Either split your data into two fields, e.g. Chapter and SubChapter, or use true decimal fractions to indicate the sub-chapter, i.e. 1.01, 1.02 ... 1.11, etc.

[Raybaudi]

Hi

make a calculation field, result TEXT, with calc:

[color:blue]

Case(

PatternCount ( yourField ; "." );Right ( "0000000000" & Middle ( yourField ; 1 ; Position ( yourField ; "." ; 1 ; 1 ) -1 ) ; 10 ) & "." & Right ( "0000000000" & Middle ( yourField ; Position ( yourField ; "." ; 1 ; 1 ) + 1 ;Length ( yourField )) ; 10 );

Right ( "0000000000" & yourField ; 10 ) & ".0000000000"

)

and sort by this new field.

( this will be good for text till 9999999999.9999999999 )

[comment]

Here's a "pseudo custom function" that will deal with up to 4 levels of legal numbering, i.e.:

1

1.1

1.1.1

1.1.1.1

Topics and subtopics can go up to 999,999,999.

Let ( [

text =Substitute ( Topic ; "." ; " " ) ;

string = "000000000"

] ;

Right ( string & MiddleWords ( text ; 1 ; 1 ) ; 9 ) & " " &

Right ( string & MiddleWords ( text ; 2 ; 1 ) ; 9 ) & " " &

Right ( string & MiddleWords ( text ; 3 ; 1 ) ; 9 ) & " " &

Right ( string & MiddleWords ( text ; 4 ; 1 ) ; 9 )

)