I need to choose a value from a list depending on another field in the
list. I have set up a value list to do this. The problem is that the
pop-down list only displays unique values. Therefore I dont see the list
item with the cross reference if it has the same values as another
record in the source table.

Source table
OS $20000
SI $20000
PO $15000
PE $30000

Pop-down list (for price)
$15000 PO
$30000 PE
$20000 OS

The SI record is not displayed!

I have tried using a relationship between the tables but it means I need
to have dummy fields in the primary table and duplicated tables in the
relationship diagram. I have several fields that need the value list so
this solution is too cumbersome.

Surely if I choose to display a second field in a value list then I want
to be able to see all records but FM8 doesn't appear to allow this (or
have I missed an obscure checkbox somewhere?).

Michael Paine

I would reverse the items in the list value : instead of 'price' /
'item', I'd put 'item' / 'price'. Then you'll see all items.
Remi-Noel

"Michael Paine" <mpaine (AT) tpgi (DOT) com.au> a écrit

Thanks but I need to extract the price from the value list - I cannot
set it to exract the item name. FM8 only displays a unique list of
prices so I lose records.

Do you mean change the order of fields in the source table?
Michael Paine


Remi-Noel Menegaux wrote:

There is something I don't see. If you're looking for prices you don't
need to have twice the $20000, once seems enough. So I suspect that you
are looking for a combined item-price. Then why not create a "field =
item & price" and make a value list based on that field.
Am I totally wrong there ?
Remi-Noel

"Michael Paine" <mpaine (AT) tpgi (DOT) com.au> a écrit dans le message de news:
4329239b$0$1909$afc38c87 (AT) news (DOT) optusnet.com.au...

Following Remi-Noel#'s suggestion.

If you are using FMP7 or 8 then having chosen a value item-price from
the list you can do an auto-enter calculation on the field Price,
replace existing contents as
GetAsNumber(Price)
this will extract the number from the price-item field.

Remi-Noel Menegaux wrote:
--
Dan
Using
FMP7.03, WinXP SP2

Thanks for the tips. Obviously I will need to do a little scripting. My
original concern was: what is the point of being able to display a
second field in a drop down list if not all records for that field are
displayed?
The second field is needed to help the user make a choice about the
appropriate record to select so missing records defeat the purpose of
having the second field. In my example below I have no idea of the price
of "SI" because it is not displayed in the value list. If a second field
is selected for a value list then all source records should be displayed
by default.

Michael Paine


Dan wrote:

In my experience I have found the value list capabilities in Filemaker
to be poor. Another thing you can't do with them is make a value list
based on related records in a table, then allow the user to sort the
values. You're best off using them in a simple manner, then offloading
the hard logic on the more robust components (such as auto-enter calcs
per Dan's suggestion)

Kevin