Field indicating lowest to highest

[HendersonD]

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?

[aaa]

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, 2005 by Guest

[Kent Searight]

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

[LaRetta]

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:

[Kent Searight]

Hi LaRetta,

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