What happens if you need a "spouse 3"? YOu may need to rethink how you link a contact to a spouse here as a join table may be much more flexible.
Using what you have at the moment, you could base your value list on a third field that combines the values of Spouse1 and Spouse2 such as this calculation:
List ( Spouse1 ; Spouse2 )
You'll see the contents of Spouse1 and Spouse2 listed as separate entries in the value list.
Spouse3 is not an option, as the table being referred to is read-only SQL. If the marriage ends (widowed, divorced) it doesn't really matter for this file, as it will be noted in the main, related SQL read-only file through another interface. I just need a value list of current husband, wife, both. Still don't know how to do it.
The method I specified with the List function is intended for you to use if you don't change your table/relationship design.
You specify the calculation field in place of your two separate fields as the source of values for your value list.
OK, I created a field called "Select_Spouse" with a calculated field as: List ("Husband"; "Wife") just to try to get the value list to work. Then, I made a value list named "Select Spouse Value List" and have it refer to the above table & field. But nothing pops up. What am I missing?
What return type did you specify for the calculation field? Text or Number? Should be text.
Why use literal text in quotes for this? If you just want the words "wife" and "husband" in your list, use a custom value list.
I used it as a test for the LIST function (haven't used it before). I'm learning each step so I'm taking what I know first, seeing if it works, then going on to the next thing (having it look up fields). I seem to be having real problems...
I found out that you can't use a calculation field in making Value Lists, so you have to have a calculated entry (similar, & do-able if I can get this to work). But I can't get the LIST function to work. In the Value List, I presume you have "Show only related values from..." the table you get the names from? I may just go with "Husband, Wife, Both" but then there are single people. The reports would be confusing... argh!
I found out that you can't use a calculation field in making Value Lists, so you have to have a calculated entry (similar, & do-able if I can get this to work).
Not true, you can use a calculation field as the SOURCE of your values for the value list, but you can't enter values into the calculation field nor would you want to. You would set up the original text fields with this value list that is drawn from the calculation field that combines the values.
I see no reason at this point for "include only related values..." unless there is a need for a relationship to limit the values listed to a related set of records.
It works! Thanks PMJ!!