I have a field called ports that is a numeric field. Lets say I have 5 records and the ports field is filled in like this:

Ports

24

21

234

1096

400

I would like to creat a calculation field that assigns 1 to the lowest number in the ports field, 2 to the next lowest etc. In other words, if my field were called Sort it would look like this

Ports Sort

24 2

21 1

234 3

1096 5

400 4

I would like the sort field dynamic so when I add another record, lets say port 100, sort for that record becomes 3 and the rest of them follow suit. Is this even possible?

Hi, HendersonD!

But what if two records have the same value?

One of ways:

Create Value list by this field

Then use WordCount(), Postion(), Left() and ValueListItems() functions.

Edited December 1, 200520 yr by Guest

This should work for you. If you have duplicate records then you might have to adapt this with ValueListItems as aaa pointed out in his post.

I wrote this in 8 before I realized you were using 7 so I just modified the script to check for the version and branched it from there to use a global field or a variable as the counter.

NumberedOrder.zip

If the field is a number and it's sorted in ascending order, the record number is the sort order. You don't need anything else, right? I wouldn't write the sort order to a regular field - you'd be reserializing them constantly via script. Either create a calculation (text, unstored) of:

portField & " " & Get ( RecordNumber )

... or (if for display), forget the calculation and use merge field: <> @@.

Then just sort the puppy!

LaRetta :wink2:

Hi LaRetta,

Once again you've shown me how much I can overthink things :