[FM6] Create a rank calculation

[Michael Allison]

Hey all,

I have a list of 150 projects that need to be ranked using a numerical order (1,2,3,4, etc.) Currently the list is sorted by a priority field in the form of (A-1, B-1, etc) then by the date the project is due. The list is in the order I want, I'm just not sure how to make the ranking field stick if I filter the list. So I'm looking for the following:

1. How to create a numerical order for the entire list.

2. How to create a numerical list for sub-groups. i.e. Give the same type of ranking but at the department level. (Dept A has 5 projects in the list. The first project in the list for dept A would be 1, etc.)

Here is a sample:

ProjectID Dept Rank DueDate RankAll RankDept

1 A A-1 04/20/04 1 1

2 B A-2 04/21/04 2 1

3 A B-3 05/01/04 3 2

4 B C-1 05/01/04 4 2

Thank you.

[CyborgSam]

One way to do this is via scripting. Create a number field called RankOrder. Sort the records the way you want. Now do the script step:

Replace Contents [No dialog, "RankOrder", "Serial numbers"]

This technique is used for sorting. For example, in value lists where the first field isn't in the order you want, so you sort on the RankOrder.

Sam