Can you Sort a Value List

[Wingwalker]

Is there a way to sort a value list? I've looked all over and read the Help area but I do not see any information on sorting a value list.

 

If you can't sort a value list, what is the other option without going to a seperate file and a portal.

 

Thanks,

 

Jim

[bikergeek]

You can, if the list is defined by a specified field, and a second field is also displayed.

[Ender]

Custom value lists appear in the order you enter the items. Value lists based on a field are always sorted alphabetically by the field you specify.

 

If you wish to sort by a field other than what appears in the value list, it's better to use a portal, as these can be sorted by whatever related value you choose.

[Wingwalker]

Hi Bikergeek,

 

What does that mean,

 

[ QUOTE ]

You can, if the list is defined by a specified field, and a second field is also displayed.

 

[/ QUOTE ]

 

I have a field for the data. You're saying I need two fields? Please tell me more because I don't follow. And if I need 2 fields what does the 2nd field do and how do you make it sort? If I enter 5, 3, 9, I want it sorted 3, 5, 9.

 

Jim

[bikergeek]

If a value list is defined as being the contents of a specified field, the list will appear sorted alphabetically, as Ender says. An option available in the specify field dialog is to choose a second field from which to show data when the user selects an item from that list. If you choose to do that, only the data from the first field is entered into the field the user is editing, but the pop-up list displays values like this:

 

1 cat

2 dog

3 bird

 

etc. If the user selects "1 cat" only the value "1" will be put into the field. If you select to sort by the second field, the list will appear as:

 

3 bird

1 cat

2 dog

 

but the "1" value is still all that will be put into the field if the user selects "1 cat" from the list.

 

Each pair (number and description) reflects the values in a single record from the two specified fields.

 

Clearer yet?

[Wingwalker]

Hi guys and thanks for your input,

 

What I really want is to enter names into a field like "Bob Slider" and "Butch Casidy" and when you go to make a selection from the field it is sorted by the last name, first name. So if there are two Johnson's Kathy is above Ronda. The list will not have more than 40 names tops and I would rather have both first and last name in the same field if possible. If I need seperate first name and last name field that's fine. But I want to be able to click on the field that shows both names "Bob Slider" and both names are entered when I click the name, not just one! How is the best way to do that?

 

I guess one other consideration is Deleting names. I would want that to be a reasonable process as well.

 

Thanks guys, you're the best!

 

Jim

[Maarten Witberg]

[ QUOTE ]

What I really want is to enter names into a field like "Bob Slider" and "Butch Casidy" and when you go to make a selection from the field it is sorted by the last name, first name. So if there are two Johnson's Kathy is above Ronda.

 

[/ QUOTE ]

 

there may be a way to do that but it would be much more efficient to use two fields, first name and last name. then you can sort by either with a mouseclick.

If you desire to have one field for layout purposes, then a calculation along the lines of

 

NameLast&", "&NameFirst

 

or

 

NameFirst&" "&NameLast

 

is possible.

 

 

 

kjoe

[bikergeek]

I think I get it. Try this.

 

1. NameFull is a text field.

 

2. NameFull Auto-enter is calc = Evaluate ( "RightWords ( NameFull ; 1 ) & " & Quote ( " " ) & " & LeftWords ( NameFull ; 1 )" ; NameFull )

 

(Uncheck "Do Not Replace Existing Values")

 

3. NameLastFirst is a calc = RightWords ( NameFull ; 1 ) & " " & LeftWords ( NameFull ; 1 )

 

4. Value list "names" is defined as all values from field NameLastFirst. Default sorting will be by last name and then first name, alphabetically.

 

Nowm when you select the NameFull field, a list of names formatted "Last First" pops up. You select one and the auto-enter calc swaps first and last. You'll have to tweak the calc for middle names, two-part first or last names, &c., but this is the basic concept.

[Maarten Witberg]

[ QUOTE ]

you'll have to tweak the calc for middle names, two-part first or last names, &c.

 

[/ QUOTE ]

 

Hi Bikergeek,

 

Interesting solution. Only, how to make FM decide automatically which belongs where? Can you solve ambiguities? (please consider this a challenge )

 

Catherine Zeta-Jones

John Cougar Mellencamp

Gus Van Sant

Baron van Harinxma thoe Slooten (not kidding, dutch gentry)

 

"Zeta" belongs to the last name, "Cougar" is a middle name (a nickname even if I'm not mistaken).

"Van" in Van Sant would probably be drawn to the last name, but "van" in "van Harinxma" certainly not. (I don't know if these problems arise with English names).

 

For simplicity, you could force the calc to always take middle parts to the last name :

rightwords(namefull; wordcount(namefull)-1)

Not very elegant but you wouldn't miss any name parts. I think I'll stick with separate firstname, middlename and lastname fields and concatenate. There are too many exceptions to the rule.

 

kjoe

 

ps if filemakin' tom reads this: I think you once posted a parsing solution but I could not locate it. And did this deal with middle name ambiguities or with prefix and suffix parts such as dr., m.d., jr, sr etc?