6 Replies Latest reply on Jan 20, 2014 7:17 PM by DavidDuncan

# A simple problem of recognising order in integers (i.e., 1, 2, 3  NOT 1, 10, 100)

### Title

A simple problem of recognising order in integers (i.e., 1, 2, 3  NOT 1, 10, 100)

### Post

I have a simple problem of integer order that I have not encountered a simple solution to on Forums. I would be delighted to be scornfully shown a link to the solution by one of you, but alas I have not found it here or on other community forums.

I have a table of 60K records from a plant ecology study obtained from point-quadrating technique, which records interception of plant species along a sequence of pin drops. See the screenshot for example.

At each sampling location "TransectID" there are up to 360 pin drops (represented by "Autonumber"). I have made a calculation field "Point.ID" ("TransectID & "." &  Autonumber") to represent the unique pin locations.

Several species may be encountered at any PointID, thus in the table it is not unique (see there are several 262.10 for example).

I want to be able to order PointID so that the 262.1 is followed by 262.2, 262.3 etc, rather than 262.10, 262.100, etc.

Can anyone point me in the right direction here?

I imagine that if I could specify that Autonumber is a 3 digit integer (e.g., interpret 1 as 001), and then do the calculation, that the order should work, but I don't know if that is possible.

Thanks in anticipation.

D

• ###### 1. Re: A simple problem of recognising order in integers (i.e., 1, 2, 3  NOT 1, 10, 100)

When setting up a Table in a Database, it often is recommended that an auto- enter unique serial number record ID number be part of the setup.
Also that an auto-enter Creation Date and an auto-enter Modification Date field be included.  Created by User and Modifed by User fields are also mentioned.
The unique serial number starts with a variable numbers of zeros and increments by one, usually.

• ###### 2. Re: A simple problem of recognising order in integers (i.e., 1, 2, 3  NOT 1, 10, 100)

It would appear that you have a number in a field of type text. Change the field's data type to number in order to get it to sort numerically instead of by text (alphabetic) sorting rules.

And yes, this is a common gotcha in FileMaker.

• ###### 3. Re: A simple problem of recognising order in integers (i.e., 1, 2, 3  NOT 1, 10, 100)

Thanks @PhilModJunk, I was storing the calculation for PointID as text as you guessed, though the two component fields were as numbers (see attached image). However, even recalculated and stored as a number it still sorts the same way.

I think I was using text format because at one point it was cutting off trailing zeroes, thus rendering 262.1, 262.10 and 262.100 as "262.1" (for example).

There must be something very fundamental (and foolish) going on here, no?

• ###### 4. Re: A simple problem of recognising order in integers (i.e., 1, 2, 3  NOT 1, 10, 100)

Check the result type specified for the PointID field. I suspect that it still specifies text as it otherwise should not sort like you show in your screen shot. There's a drop down list inside the specify calculation dialog where you specify the result type. with a number result type, a trailing zero after the last digit will not affect how the records sort.

• ###### 5. Re: A simple problem of recognising order in integers (i.e., 1, 2, 3  NOT 1, 10, 100)

@PhilModJunk I had changed the result type prior to my previous, and i redid the whole thing after your most recent, replacing the contents as an auto-calculation.

What you say makes sense, but I can't get the result I expect.

• ###### 6. Re: A simple problem of recognising order in integers (i.e., 1, 2, 3  NOT 1, 10, 100)

@DavidAnders, thanks to you also. I followed the FM forum link to Leading Zero's Padding.  Whilst it looked at face value like I should have been able to use the pasted sample text [ SerialIncrement("000"; Self) ] to prepare the Autonumber field (to a uniform 3 digit integer) it didn't seem to do anything.

When i created a new field and the Autonumber field instead of "Self" I did indeed get my leading zeroes.

SerialIncrement ( "000" ; Autonumber )

I'll spew if the difference was merely whitespace.