8 Replies Latest reply on Feb 22, 2011 10:00 AM by Davisms

sort -- number with letters

Title

sort -- number with letters

Post

I've got a field that always has numbers, but also occasionally has letters.

I'd like it to sort so that the numbers are always in order, but that when the letters appear, those sort as well.

for example:

101, 100, 103, 101A, P103, 101C, 101b, 103A

would sort as follows:

100

101

101a

101b

101c

103

P103

103a

If I make the field a NUMBER field, it ignores all the letters completely.

If I make it a TEXT field, it sorts everything correctly, but puts the "P" prefixes at the end of the list.

I invite your thoughts.

Thanks,

eric

• 1. Re: sort -- number with letters
Well if you cant split those off, you could create two calc fields that capture the number and text. Then you could sort by the number first and then the text.
• 2. Re: sort -- number with letters

Define calculation fields that separate your number and text portions into separate fields and sort on these fields instead.

cNumberKey: getasnumber (AlphaNumberField)

cLetterKey : Filter ( Right ( AlphaNumberField ; 1 ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )

cNumberKey

cLetterKey

• 3. Re: sort -- number with letters

If I'm understanding the cLetterKey correctly, it would just take 1 digit starting from the Right (if it's a letter).

With the understanding that the main number is always 3 digits,

We sometimes have the following come up:

A)

101AA

or

B)

101A1

101A2

In the case of A) I would guess that we could do

cLetterKey : Filter ( Right ( AlphaNumberField ; 2 ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )

correct?

But for B), would I need to write an elaborate IF script?  I presume so.

(Currently, there is too much history with the database before I came on to separate the AlphaNumberField into 3 or 4 separate fields (Prefix / Number / Suffix, or something to that effect.  However, if it becomes too cumbersome, I may lobby for it.)

• 4. Re: sort -- number with letters

But for B), would I need to write an elaborate IF script?  I presume so.

Not necessarily. Have you now posted all the possible permutations to your numbers or are there other combinations possible?

PS. I'd lobby and lobby hard for replacing the current set up with separate data fields.

• 5. Re: sort -- number with letters

Ok, here are the various options:

N = Number  (Always 4 digits)

A = Letter

NNNN

NNNNA

NNNNAN

NNNNAA

ANNNN

ANNNNA

ANNNNAN

ANNNNAA

AANNNN

AANNNNA

AANNNNAN

AANNNNAA

• 6. Re: sort -- number with letters

So preceding letters are ignored, first number section is always 4 digits followed by 0 to two digits of either letters, or one letter and one number....

The first field, NumberSection, could be evaluated as Left ( Filter ( AlphaNumeric ; "0123456789" ) ; 4 ) /* left 4 digits drops out any trailing digits */

For the second portion:

Middle ( AlphaNumeric ; Position ( AlphaNumeric ; NumberSection ; 1 ; 1 ) + 4 ) ; 2 )

Assuming you want values to sort in ascending order like this:

1001

1001A

X1001A1

1001AA

That should work for you.

• 7. Re: sort -- number with letters

A true wizard indeed....

:)

• 8. Re: sort -- number with letters

We've been without a Filemaker Pro tech support person for quite a while, and this particular issue has been troublesome for me.  I plugged the fields into the program and *voila* they worked!  Thank you!