It's possible to set up a value list to draw its contents from a
field. In such a case, the value list includes only unique values.
For example, if there are 5 records wherein the field "Color"
contains the values
red
black
red
black
red
a value list based on the "Color" field would read
black
red
Note that, even tho "red" came 1st in record-creation order, "black"
comes 1st in the value list, based on alphabetical order.
It's also possible to set up a value list that will SHOW records from
2 fields. For example, assuming the same set of 5 records, if we add
another field called "Critter" to get
red bull
black lab
red ant
black angus
red kangaroo
and specify that the value list should show "Color" as the 1st field
and "Critter" as the 2nd, we get
black lab
red bull
That is, the value list ignores the value of "Critter" for all but
the 1st occurrence of any unique value of "Color".
If we change the value list so that the "Show values only from second
field" option is checked, we get
angus
ant
bull
kangaroo
lab
In a case like this, even if you select "kangaroo", the value
"red" (the "Color" value associated with "kangaroo") still gets
entered in the field.
And, if we change the value list so that the "Sort values using"
feature is set to "Second field", the value list looks like this:
black angus
red ant
red bull
red kangaroo
black lab
and, once again, clicking on any of these enters only the color, not
the critter, in the field associated with the value list.
If we add a 6th record for brown kangaroo, it does NOT show up in a
value list sorted by 2nd field, and it is apparently impossible to
chooses the value "brown" for the "Color" field if we do this. If,
however, we revert to using the 1st field as the basis of sorting, we
get
black
brown
red
or possibly
black lab
brown kangaroo
red bull
if we've also specified that we want the 2nd field to be displayed.
OK, I've reviewed the above to show that I've tried a lot of
different options for setting up what I'm really after but haven't
been able to find quite the right combination. So here are my 2
questions:
(1) Is there any way I can set up a value list to SHOW values from 1
field and be SORTED by values from a 2nd (non-displayed) field?
OR
(2) What can I use as the contents of a 2nd field that will not be
visible in the drop-down list but will nonetheless enable me to use
the 2nd field as the basis of the higher-level sort?
These questions are based on a drop-down list of dog breeds. I'd like
the most common breeds (cocker spaniel, Labrador retriever, etc.) to
bubble up to the top of the list at data-entry time, but the only way
I can find to do it is to associate them with a 2nd field that I can
use as the primary basis for sorting the value list. Unfortunately,
this gives me a list that DISPLAYS the 2nd field, something like:
cocker spaniel 1
golden retriever 1
Labrador retriever 1
shih tzu 1
Affenpinscher 2
Afghan hound 2
airedale 2
akita 2
etc.
I had thot to be clever and use a soft (regular) space instead of a 1
and a hard space instead of a 2, so that they'd BE there in the drop-
down list but just wouldn't show up. Unfortunately, it appears that,
for sorting purposes, these 2 kinds of spaces are identical.
Any ideas?
FMPA 9.0v3 on Mac OS 10.4.11.
