How to find largest serial text value?

[K1200]

I have two files that use text keys defined as auto-enter serial values. For example, one file uses keys that progress as follows: A01, A02 ... A99, A100, A101 ... A999, A1000, A1001. I need to locate the "largest" key (A1001 in this example), but a simple sort does a column-by-column evaluation that results in A999 being deemed the largest. Is there a way to cause the sort to determime that A1001 is greater that A999? Or is this just an accepted problem when using alphanumric keys?

Thanks for any suggestions.

Edited September 27, 2006 by Guest

[BobWeaver]

If all the serial numbers start with alphabetic part followed by a variable number of digits, then you can split out the numeric part with the filter function:

Filter (SerialNo;"0123456789")

into a separate field and then sort on that field. (This new field should be defined as numeric.)

You would be better off to set up your serial numbers to have leading zeroes (i.e., A00001...A01001) so that you don't run into this problem in the future.

[Genx]

Bob, instead of using filter, couldn't you just use get as number given that leading zero's arent really a factor here?

[BobWeaver]

Yes. There are probably a number of different ways of doing it. I've just had the Filter function on my mind today though. :D

[K1200]

Thanks for the suggestions. I think the GetAsNumber approach will work for me.

To summarize: I'll loop through the records (there could be thousands) using GetAsNumber(TextID) to extract the numeric part, compare each against a $$Largest variable and replace the variable if the ID is larger than the current. Then I'll affix the letter to the final number to get the answer. Luckily this doesn't have to be done very often.

[Mikhail Edoshin]

If the keys are FileMaker auto-enter serials, then the largest key must be the last one. Thus

Show All Records

Go to Record[ Last ]

Set Variable [ $$largest, Record::ID ]

This won't work if the order of records was modified somehow, e.g. by import.

[K1200]

That's exactly the case I'm dealing with: imported records ... which can have gaps (OK) when loading into an empty database, but I have to adjust the next serial value to avoid duplicate keys when creating new records.

(As a safety net, I want to make sure new values never track back over any existing key, hence the need to find the largest key and not trust any previous sequences.)

[Genx]

... Dont bother looping...

Create that extra calc field, mark a "cartesian?" -- "X" relationship between another TO, sort the records by your calc field, and then simply reference the related calc field -- if you've sorted correctly, the first related record (and the highest value) will be returned.

[Genx]

... or even as i just read Bob suggest, simply sort in a layout based on that TO, and go to the first record to collect your value.