Sorting data in a pop-up list

[nightdesigns]

I have a field that you select the value you want in it via a pop-up box that have the values based off that field. I'd like to sort the available selections in reverse format (high to low). How can i do this?

[Vaughan]

Only by making a custom value list and manually typing them in order.

[nightdesigns]

Doesn't do much good since the list is based off values in recrods. Oh well, the user will just have to suffer.

[CobaltSky]

As Vaughan says, values taken from a field are going to be alpha sorted - there is no provision to control it.

If presenting the values in descending order is crucial, then you may wish to consider using a portal to display the values (portals can display values in sorted order - including descending sorts) and a script to copy the selected value into the field when you click on it.

In fact, depending on how your layout is arranged, you may be able to set a portal up to look and work exactly like a pop-up list, using the 'portal visibility trick' (as demomstrated in various download files available from DatabasePros.com and LayoutMode.com) to make the portal appear (drop-down) when the field is clicked on and disappear again as soon as a value is selected.

I hesitate to suggest this technique because it can be fiddly to implement well, is not suited to all layout configurations, and can end up being 'clunky' in some cases. However since it is possible that it will get you where you want to go, in the absence of other suitable options, you may wish to give it a try.

[BobWeaver]

Another kind of clunky option is to include a second field in the value list and set it to sort by the second field. You then enter numbers in this second field in an order that will cause the list to sort the way you want. You'll just have to tolerate having these numbers appear in the value list

[djgogi]

I wouldn't call it clunky. It's only about giving some meanful valu as prefix to sorting value like "item" or "lst" or whatever else is appropriate (just for avoiding confusion) and one simple SelfJoin on that field.

Now, I can see 2 approaches here:

One involves two-step scripting (iow you need Sort and Relook-up step) and is fair straightforward,

The other one is more complex but easy to implement.

Both methodes could be use for creating value lists with custom order.

Methode 1)

First you'll need some additional fields:

1)ID (auto enter serial number)

2) unstored calculation recNum=Status(CurrentRecordNumber) result text

3)Self Relationship SelfID::ID-->ID

4)look up field storedRecNum as look up values from field SelfID::recNum

5)Stored calculation field prefix="some string" & storedRecNum

6)Self Relationship "PrefixRel" based on field prefix

Finally define value list as use value from field (or only related values if your list is relational) prefix also diplay values from field "your original list field", sort on first field.

Now after attaching this value list to your field disable entry into it and create the following script:

//if the list is relational than use GTRR otherwise use entire foundset or subset( the ///method works with minor modifications in script also for subsets of records)

Sort (do whatever sort you want)

Relookup [no dialog,"SelfID"]

Go to Field [the field with value list attached]

to use above with subset of records first you'll need to clear the field storedRecNum than perform find and use the above script.

2) second method

I will assume that you are dealing with numerical values ( probably dates), otherwise I really don't see why decreasing order should be prefered to increasing, however the following could be also applied to strings (after appropriate transformations)

So , basically you need to rotate an vector.

First let's find the extremes, iow min and max.

If you could anticipate those values (and this is particulary true in case of date ranges) the following is just an extra that you can omit)

If you are dealing with physical records (I mean you are not using related values for the list) define 2 summary fields:

Max and Min

noww define an stored calculation field

range=Case(yourField,GetField("Max")+GetField("Min"),GetField("Max")+GetField("Min"))

Define stored calculation field

prefix="Dome String" & (Range-yourField)

As in method 1 define an self rel prefix-->prefix and than access theselected value thru PrefixRel::yourField

To make everything look better use

styledPrefix="some String" & Right(Right( 10 ^6, 6) & (Range-yourField),6)

and

"some string" &Right(Right( 10 ^6, 6) & storedRecNum,6)

at place of prefix field.

HTH

Dj

PS

The above methodes could be use to implement the relational value