Items disappearing from value list

[BeckhamTX]

I have made a value list to use on a field where the user selects an ID number (the PK for a related table). The value list is set up to contain values from a field.

This works fine. I have 400 records, and the value list has 400 items.

This is good, except the user has no idea what ID number is associated with which name. So I have also displayed values from a secind field (the name field). This still works fine, except there are 400 items in the list, so it would be nice to sort them alphabetically by last name. When I go in and tell it to sort by the second field, then the field with the value list attached to it only displays two items instead of 400.

I have checked the option to show all values, not only related values.

To sum it up, everything works fine until I tell it to sort by the second field, which causes the value list to display only 2 instead of the 400 items. it is essential to sort by the sencond field, since the first field (ID/PK) means nothing to the client and thus would be a hassle to scroll through 400 items without any type of meaning to the sorting of them.

[BFrost]

Is indexing fully on in referenced fields? Especially the second field. Check the Storage settings in the field definitions.

[BeckhamTX]

The first field is fully indexed.

The second field, I am trying to sort by, is a calculated field (it concatanates Last name, First name). The settings for the calculated field are: set to store calculations, and be fully indexed.

I did change the second field to be just last name, a text field, as opposed to the calculation field, and it showed all 400 people in ABC order by last name...

So the problem seems to be with using the calculation field as the second field, and sorting by that calculation field. I could just use last names, but with 400 people in the list, there are sure to be multiple smith's, johnson's, etc.. so that wouldnt help.

Any ideas on why I am having the problems with the calculation field, and how to resolve it?

thank you

[BFrost]

It's not the calculation field that is the problem.

It sounds like you may have hit on the explanation with 'duplicate last names'. If you have the first names avalailable, how about making a calculation field which results in a directory style "Last Name, First Name, Middle Name" and use that field as the second field. That's the way I've been doing it for years and I seem to get 'em all. Although if two people had exactly the same name, you'd only see one picklist entry between them, too.

To eliminate that possibility, append the record ID to the directory style field you created but in a 2nd new calculation field. (Having a directory style listing is useful all by itself and having a number tagged on the end would make it look dumb.) Since the Record Id is (presumably) unique, you can select 'display only the second field' in the pick list.

I just tried what you described on a database with about 15000 names. There were about 15 last name 'Adams' records but only one showed up to pick from when sorting by the 2nd field (last name). So you did find your own answer.

The indexing function only shows one of everything no matter how many of some may have duplicates.

Therefore, fields used must each be unique to assure full representation in selection.

Is that a PIA or what?

[LaRetta]

Hi BeckhamTX,

I think that calculation is indeed the problem ... make sure that concatenated LastName & ", " & FirstName calc is not specified as a number. That will produce just a few results in your value list, ie, it will only display the first entry in the value list (first in your table) and/or only if the field contains a number - which sounds exactly like your 2-out-of-400 display issue with it. And then sort by this field (as your second field) as you originally intended.

But keep in mind that two people can have the same name and only one will show. So you will NEVER be able to select the second John L. Smith. This may seem silly but I assure you it happens. Odds depend upon your record size but even if you only have 400 customers, it would only take ONE duplicate to ruin the first John L. Smith's day to be invoiced for something the SECOND John L. Smith ordered. When something is as critical as identifying a UNIQUE entity, I provide portals which produce ALL matching records (which also lists city/state/zip/phone so the User can make an intelligent, precise choice ... OR, I include one of these additional unique fields within the concatenation so Users see that as well (phone would be better than city/state).

LaRetta

[BFrost]

LaRetta, BeckhamTX hasn't done a concatenation of last and first names yet. He has isolated only the last name and has successfully sorted the names alphabetically by last name. So we already know the field he has is not a number result. You are correct to remind us all to make sure a text result is specified when a text result is wanted.

Providing portals from which selections are made is a good solution and may be appropriate for what BeckhamTX wants to do. Otherwise, as you reinforced in your post, duplicate names must append or prehend something that makes each selection possibility a unique possibility.

I think using the unique record ID is the easiest, least complicated way to do that, as I posted above. And it could be used in reference list where both data are useful to use. I believe it to be an intelligent way to generate the kind of picklist solution sought in the initial post.

Portals can be a better solution if you have the visual real estate to accomodate the additional perceptual information to make selections visually unique.

City, State, Phone do not guarantee elimination of duplicates. Phone numbers especially can be an easy ambush for John Smith, Sr; John Smith, Jr; and John Smith, III could all live in the same place with the same phone and not use or provide the distinguishing suffixes. Not only that but 123-555-1234, (123)555 123 1234, (123) 555 123 1234, and 123 555 123 1234 will all dial to the same number but each is a different text string for indexing purposes.

Daughters named for their mothers or grandmothers or both are not uncommon and there is no standard way to say Jane Smith, Jr. without getting funny looks.

City and State are even worse for similar reasons and adds a significant degree of likelihood that otherwise unrelated people in either or both areas have the same name.

[LaRetta]

Bob, neither do I depend upon non-uniqueness of anything other than a system-generated unique key and I can provide 30 or more posts where I expound on the problems of name/city/state/zip/phone as a unique identifier. I sound exactly like you and I agree!

As for the fact that the problem is not a number field - and that a concatenated calculation was not used:

So I have also displayed values from a secind field (the name field). This still works fine, except there are 400 items in the list, so it would be nice to sort them alphabetically by last name. When I go in and tell it to sort by the second field, then the field with the value list attached to it only displays two items instead of 400.

The second field, I am trying to sort by, is a calculated field (it concatanates Last name, First name). The settings for the calculated field are: set to store calculations, and be fully indexed.

The concatenated calculation produced TWO VL items out of 400 names. Of course the problem is the calculation - it SHOULD produce correct results - at least one unique of every LastName/FirstName combination in the table. No? And I can't believe there are 398 duplicate names in the table. Just as the attached shows, I think the calc is probably a number. The results indicated match this file exactly. When something is wrong, suspect a data-type mismatch FIRST. I could be wrong (about it being a number) but a calculation should work - period. So if it doesn't (and it is indexed), it IS the problem...

NumberVL.zip

[BFrost]

LaRetta, your right about the presence of a concat field which is prolly a number. I missed that part somehow and focused on the

I did change the second field to be just last name, a text field, as opposed to the calculation field, and it showed all 400 people in ABC order by last name...

part . And it should only miss on unique combinations of last and 1st names.

Good catch, same page.

[BeckhamTX]

THANKX y'all for the help. The problem was indeed the way I set up the calc. field. It was set to evaluate to a number, not text. I guess that number is the default, and I just overlooked cahnign it on that particular field.

[LaRetta]

Default is indeed a number. And FM is smart enough to look at the field type and open the calc box showing the same default result but it doesn't. It is true that we (many times) specify a result NOT the same as the field type. But, when we create a Date or TimeStamp field and set it to Auto-Enter CreationDate or Modification, FM knows to assume the same data-type (which is very nice of them). It would be nice if they defaulted calculations as text (I think). I will do my part and send them that suggestion.

In the meantime, I've learned to immediately zero in and verify the data-type first - open calc box and ask "what is the RESULT I need?" It's easy, once I begin writing the calc, to forget about that box - so I make that first priority. I've been caught on this one myself.

I'm glad it worked out for you. :wink2: