Ranking a number field against the same field in all records in the table

you can use Get ( RecordNumber ) when records are sorted, but there is no real ranking function.
Also note that the excel rank is not dynamic either if you edit the matrix list.

I confirm, value lists are pretty bad at updating when they reflect a table field. On top if you have 100k lines in the table, it will be slow...

I'm respectful of Lesouef's doubts on the topic, but I would like to try JoJohn's idea out.  Unfortunately I don't understand how to set it up, and the formatting of the response left me very unclear.
I have the number field called Size, and I have a current database table called Companies.  I'm not clear what fields, relationships, and calculations to set up.  Sorry to be slow, and I'm learning fast, but at the moment JoJohn's idea is beyond my current compentence.
My Companies Table will have 2500 records in it, although at the moment, while setting it up and testing, I only have 4.
Could you explain more simply how to set your solution up?
Thanks
Tony

You don't need any relationships to do this. Create a value list called 'rank' that uses a field to make its list. It will be the first choice in the value list dialog box. The field it will look at is 'size'.

then create a field called rankOrder which is a calculation that returns a number and paste this into it:
Let(
[
List = ValueListItems ( "RankOrder"; "rank" ) & ¶;
Stop=Position(List;Size;0;1)+ 1

];

WordCount(Left(List;Stop))
)

the only change you will have to make is to change 'RankOrder' to the name of your database file (not table name). give it a try and if you are still having trouble I will post a sample.

Thanks.  I've done as you say (where can I look to understand the formatting of the Calculation?) and it worked fine to rank the 4 companies in the database.  But when I added a new company, which should have been ranked 4, pushing the previous 4 to 5, it was instead ranked 1, and the previous 4 ranks were not changed.  As you said, it does not refresh on its own.
I created a script with the single command Refresh Window, and ran it, but the rankings did not change.
Do you have any other ideas?
I do not need the ranking to change often, but I do need it to change about monthly.
Thanks
Tony

when you create the calculation, change the storage option to 'do not store, calculation as needed'. The refresh script should then work.

Oh and as for the understanding part...

The basic idea of this calculation is to let the value list create the list of all the possible sizes. the value list will automatically sort them. Then use the Position function to determine where in the list the particular size falls and truncate the list at that point. finally, count the number of values in the truncated list.

you don't really need to use a let function to do this. If you replace List and Stop with the statements they are equal to, you can remove all the Let() function parts. I just find it easier to define the parts separately and then use the variable in the final function.

I changed the storage option, and I finally got the Refresh Window command script to work by selecting "Flush cached join results" although I'm not clear on what that means.
Apart from that script :
After closing the define fields dialogue after making that change, the existing incorrect rank fields changed to the correct ranking.
If I then enter a new company, or if I change the size of an existing company I can get the ranking to work by closing and reopening the file.
Thanks very much indeed
I'm a bit nervous of what will happen when I load 3000 records into the Companies Table, because I'm not going to be able to check whether it's working correctly, and I'm not going to know if the program is just being slow recalculating.  Anyway, this is an excellent start, and I'm very grateful.
Tony

Could you explain the script a bit more?  I would like to understand the method
What do the "[  ...  ]" brackets mean?
Why is the "[ " after Let( on a new line?
Why is List on a new line?
What is the character just before the ";" at the end of the list line?
why is there a clear blank line underneath the Stop line

Finally, is the shorter rewrite that you mentioned, correct as follows:

WordCount(Left(ValueListItems ( "RankOrder"; "rank" ) & ¶;Position(List;Size;0;1)+ 1))

Thanks in advance
Tony

It's not mine but I can answer this for him...
Brackets are necessary in a Let function if you define several variables.
New lines is just for clarity, no real use apart from making it more readable.
List on a new line: same.
; could be anywhere, it just separates the 2 assignments.
blank line... still the same, make it clear...

And yes, it is the same except that you can't really understand what it does just by reading it.

Now try the speed on your 2500 lines before you keep it. A simple sort + replace contents may be faster... records can be indexed, not lists.

whew! so many questions! The answer to your last question is yes. that is another way to write it. As for the others, The Let() function permits the declaration of variables that you can then use in the calculation. the [ ] separate the declarations from the actual calculation. the paragraph mark (backwards P) ends the value list with a carriage return. I am not sure if it is necessary but I like to end my lists that way. When you create a calculation, you can put whatever you like on a new line or put in blank lines. It won't affect the calculation and it makes it easier to read.

Also, I think you will find that the contents of the field changes immediately but just doesn't show them. Even if all you do is open a new window without closing the file, you should see the correct numbers. Since nothing is being saved, it generates a value as soon as it draws the field (or redraws it)

Thanks very much, I'm much clearer now.
Very grateful
Tony