Sorting in natural order

[Macman]

I did a search on this and came up empty... Is there a way to search and sort so that the result is in natural order?

For example, if we sort by the way computers sort, the number 202 comes before the number 3 even though 3 is a lesser value.

[BobWeaver]

If your field is defined as a number field, it will sort correctly.

[Macman]

But, what if some of the records (in this case, part numbers) contain alpha characters?

[andygaunt]

Can you give examples of some of your part numbers?

quote:

---

Originally posted by Macman:

But, what if some of the records (in this case, part numbers) contain alpha characters?

---

[danjacoby]

Actually, I'm not thrilled with the concept of "natural order"; a general rule of thumb is that anyone who talks about the "natural order" of anything is looking to commit genocide.

[BobWeaver]

Dan's right. Natural order really has no meaning in sorting. You basically have numerical or alphabetical sorting. If there are any alphabetic characters in the field, you are stuck to do an alphabetic sort. If you are using data of the form ABC8, ABC9, ABC10, then there is no way to sort these without creating a calculated field to massage the data. In this situation you should always use leading zeroes like this: ABC08, ABC09, ABC10. Then these will sort correctly.

[Fitch]

If you don't have too terribly many items, you could define a value list, and then sort on that.

------------

Dawkins 9:11

[Macman]

quote:

---

Originally posted by danjacoby:

Actually, I'm not thrilled with the concept of "natural order"; a general rule of thumb is that anyone who talks about the "natural order" of anything is looking to commit genocide.

---

Arlo Guthrie:

"Kill?..... KILL!!!:.........."

Actually, I got the term from a system 8 extension that would do this with Finder windows.

Part Numbers:

25, 201, 17594-N, S7133R

then something like this

1067

1067M

1067R

I'm going to try a few different things to see if I can get this to work. I'd love to be able to use leading zeroes, but let's face it, that would be way too easy...

[BobWeaver]

I got news for ya. There is no natural order for 25, 201, 17594-N, S7133R

[danjacoby]

quote:

---

Originally posted by BobWeaver:

I got news for ya. There is no natural order for 25, 201, 17594-N, S7133R

---

And that is the natural order of things.

[BobWeaver]

Actually, if you change the field type to number, you can still put non-numeric characters in it, but it should sort the way you want.

[Macman]

Bob - what I meant by it was that the number was in order based on quantity, and would be followed by the letters; the accounting program the company uses sorts in this method. It's up to me to find ways to use the data that the program can't.

See, this is what can happen when you work with people who are used to DOS-based programs... unfortunately in my case, they're the ones with the money...

BTW, the Natural Order extension and it's web page can be found here:

http://naturalordersort.org/

[ February 21, 2002, 12:43 PM: Message edited by: Macman ]

[Macman]

Yeah, I tried that, but it puts the letters first...

[BobWeaver]

There must be something wrong here. I tried a test with your sample numbers and got it sort in this order:

25

201

1067

1067R

1067M

S7133R

17594-N

Notice that since these are in a number field all alpha characters are ignored, so the 1067, 1067R, and 1067M are not sorted by the suffix letter. Also if you have a dash between a prefix letter and any digits, Filemaker will interpret that as a minus sign and negate the number, creating more sorting problems.

Having given it a bit more thought, I think the best all around solution is to have the field formatted as text and then set up a calculated SortField with this formula:

TextToNum(Substitute(PartNo,"-",""))

Make sure the result type of the calculation is number. Then, set up your sort order to sort first by the calculated SortField, and then second by the PartNo field.

[Macman]

Thanks Bob!

You know, I have four books on FMPro, and not one of them speaks about these types of internal functions... I would love to try and figure some of this stuff for myself, but this situation exposes much about what I don't know about FM... can you recommend a good learning resource?

[BobWeaver]

The problem is that there are so many little details in Filemaker. They may be covered in the books, but their importance is not always immediately obvious. Many books need to better emphasize some of more important details and their consequences.

The only book I have (other than the FM manual) is the Colombre and Price book "Special Edition using Filemaker Pro 5." It seems to be pretty good.

[Macman]

Again, Thank you!