Lookup Lists

[Kevin12853]

Hi

I'm currently trying to get the hang of FM. I am stuck on lookup lists. I have googled myself silly trying to resolve this as I am sure I am missing something very simple

I want to use a drop down list to present a list of values from a 'lookup table' and have the selected value put into the main table.

ie Main table is a list of properties in a property portfolio, it has the following fields...

PropertyID

PropertyAddress

PurchasePrice

etc..

[color:red]fk_CurrentStatus

The Lookup table has the fields..

StatusID

StatusTypeName

StatusTypeName contains for example...

'Under Offer'

'Awaiting Mortgage Offer'

'Owned'

etc.

On a layout I have a drop down list to offer the Status types for the user to select one.

In the manage value lists dialogue I have set up a list as shown in 1.jpg attached.

I have set up the field/control setup dialogue as in 2.jpg attached.

I have created a relationship as shown in 2.jpg

What I was expecting and wanting to see in the layout was the StatusTypeName taken from the lookup table dependant on the fk_CurrentStatus value eg. Under Offer, in the dropdown list text and the list of status names when I drop the list down.

This is in fact what I get, until I select a new or even reselect the current value. What seems to happen now is the value in the lookup table's StatusTypeName eg. 'Under Offer' is replaced with the StatusID eg. 1 so now I am only offered a number in the drop down rather than a name.

Also.....

I cannot select the drop down when creating a new record or at least the dropdown does not 'drop' and the text portion is blank. I will explain this in a seperate post if someone thinks it is relevant.

Sorry this is so drawn out but I want to expalin as best I can.

Any ideas??

Many thanks

Kevin

[Raybaudi]

Hi Kevin

and welcome to this Forum.

The "Property Status" Value list contains numbers ( the values from lookup_tablePropertyStatus::IdStatus ) even if it shows the StatusTypeName.

So you can't attach those values to the field StatusTypeName ( you can, but you'll see numbers ).

[Kevin12853]

Hi Daniele, thanks for the reply

I want the StatusID value from the lookup table to go into the the fkCurrentStatus field in the main table, which are both numbers. I want the user to see the text value of the StatusTypeName from the lookup table in in the drop down list. What seems to be happening is that the StatusTypeName is being altered to contain the numerical value from the main table so I end up with both the StaustypeID and the StatusTypeName in the lookup table containing numbers. ie the lookup table is being updated with the value from the main table. Is it something to do with the relationships?

I am very confused!!

Thanks

Kevin

[Fenton]

Your Field Setup has the wrong field. Where it says "Display data from:" it should be the LOCAL ID field, not the related Name field. You are likely confusing the term "display" of the Value List (where you've chosen to show only the 2nd field), with the same term "display" of the Field Setup dialog.

But they don't mean the same thing really, as they are for two different purposes. In the Value List it just means which field to "also show."

In Field Setup, "display data from:" really means, "what field is this?"* That would be the real data field, the ID field in this case. It has little to do with what field is showing as the 2nd field only in the value list.

It more equivalent to the "Specify field" dialog. It is above the drop-down for the relationship, so that is one reason it uses that terminology. BTW, there is much the same confusion in the Go To Related Record dialog.

*The confusion is compounded by the fact that you got to this Field Setup dialog by having the field selected and using the menu or command keys. So one wonders, why is it asking me which field again? We'll call it a "feature", for flexibility, as you could reassign the field and the value list in one dialog. I do this all the time, because I tend to duplicate field objects on the layout, in order to keep all the formatting settings. Otherwise, if you are just adding/editing which value list it uses, you can ignore the "display data from:", it will be the field you had selected.

[P.S. If you want the local ID field on the layout to look like the value list, ie., only show the Name field, then use the "popup menu" choice when you assign the value list. Alternatively, use "drop-down list," but cover the local ID field on the layout with an instance of the related Name field, opaque, non-enterable. The cursor will drop thru it. Either way works.]

Edited December 16, 2008 by Guest

[Kevin12853]

Thanks fenton, that seems to have sorted it.

Thanks again

Kevin