script to determine minimum value in a database

[horus]

I am newbie with FM and I have a very stupid question :

How to create a script wich determines the minimum value in the database for a specific text field ?

I hope with you can help me

[Pupiweb]

You don't need a script, you can use either a Summary field returning the Minimum of the field in the found set or a self join relationship linking all the records and a field calculated as Min(SelfJoin::TheField)

In both cases the formulas apply to *number* fields while your post would appear to point to text field (?)

[LiveOak]

What exactly IS the minimum value of a text field? Would you consider "," to be a lower value than "*", "%" than "@", "a" than "A", etc.?

-bd

[horus]

Yes, I mean that "a" is lower than "A, "A" is lower than "B", etc.

For example, I have 5 records in my database Cities

(1) New York

(2) Paris

(3) Brussels

(4) London

(5) San Francisco

The Minimum value in my database should be "Brussels" and the Maximum value should be "San Francisco".. but I can write the script/field because it is not a numeric field

[LiveOak]

Use the script:

Show All Records

Sort (setup to sort by the field you wish)

Go to Record/Request/Page (first)

Set Field (gFirstOne, Country)

Go to Record/Request/Page (last)

Set Field (gLastOne, Country)

"Country" is the field you wish to find the "minimum" and "maximum" of, gLastOne and gFirstOne are global text fields to provide temporary storage of the first and last values. To differentiate "a" and "A" (if you really need to) you must set the field indexing for Country to ASCII, but then searching for an "a" won't find an "A".

-bd