lookups - dropdown/dialog inconsistency

[Steve Wood]

I have a simple lookup table with ID and Language, as in:

01 German
02 French
03 English

I've defined a field named Language in a second form, with a table-based lookup to the Language table.

In toggling between using dropdown and dialog version of a table-lookup, I find that dropdown returns the language value (which is what I want), while the dialog returns the ID field.

Example:
Dropdown returns German
Dialog returns 01

Its important that the link be on the ID to maintain relational integrity.

See graphics.

So - is this how it is supposed to work?

[Al Buchholz]

Re: lookups - dropdown/dialog inconsistency

Steve

I think you are referring to coded fields.

http://support.alphasoftware.com/alp...ded_Fields.htm

[Steve Wood]

Re: lookups - dropdown/dialog inconsistency

Yes, and, I am doing things properly per the instructions. Still, it has the problem I defined.

[G Gabriel]

Re: lookups - dropdown/dialog inconsistency

I've defined a field named Language in a second form

What do you mean?
Is this "field", based on a field in the table? is it a variable?

[Steve Wood]

Re: lookups - dropdown/dialog inconsistency

No variables. Lookup table has fields Language_ID and Language. Table that has the lookup defined has just Language_ID field.

Lookup table:
- Language_ID
- Language

Main table
- Language_ID -- defined as a lookup in field rules

[G Gabriel]

Re: lookups - dropdown/dialog inconsistency

Steve:
I am not sure if this matter, but in both of your images, you do not assign the value of the language from the lookup table (the source) to the current table (fill). What would be the purpose of the lookup then?

[Steve Wood]

Re: lookups - dropdown/dialog inconsistency

There is obviously a lot of confusion over how these lookups work. Table-based lookups are supposed to assign the ID field in the lookup but SHOW the value. And that's what it says in the help. If you look at my first graphic it is doing this just fine without assigning the value. What I've done is consistent with the Help instructions.

[G Gabriel]

Re: lookups - dropdown/dialog inconsistency

I understand, but don't you eventually want to insert the language itself as opposed to the ID? or maybe both? have you tried dialog with filling the language?

[Steve Wood]

Re: lookups - dropdown/dialog inconsistency

Gabrial,

I'm sorry but you are not reading what I am writing. Yes, of course I want the value (e.g. German) - that is what this lookup method is supposed to do.

[G Gabriel]

Re: lookups - dropdown/dialog inconsistency

That's my point Steve. If that's what you want, then how come you are not using the source to fill the current table with that value? you are using it only for display only. And my other question, have you tried to use it to fill in a dialog and does it display if you do that?

[Steve Wood]

Re: lookups - dropdown/dialog inconsistency

Gabriel,

I'm doing it correctly. It is SUPPOSED to work this way. You display the values in the picklist, but you link on the ID. The table that performs the lookup STORES the ID value, but SHOWS the value. Its what Al referred to as "coded fields" above.

So, it stores "01", but it shows the current value from the lookup table, "German". This is very important concept - its so you can go back to the lookup table and change German to Latvian and it fixes everywhere you have ID of "01". If I stored the actual value, I would have to go back and fix every record that had German in it, and change the value to Latvian.

The only thing I am asking is why is it different between the dropdown and the dialog method. Its just one check mark on the lookup definition. In both cases, and according with the Help, it should be storing the ID but showing the value.

[Tim Kiebert]

Re: lookups - dropdown/dialog inconsistency

Steve,

This thread is similar to yours. It doesn't answer your question though.

I would say the logic is like this.
( I know you know all this but just incude it for clarity)

1. In both cases a lookup is being performed.
2. The point of which is to choose and retrieve data from another table.
3. Not only can you view the data you will bring across but also other fields to help you recognise the data you want.
4. In both cases the data returned is the same.
5. Although the difference is only a check mark some how alpha performs some magic and makes the value of the first field set to display in the list of choices actually be what is displayed in the field.
6. Note though that you have to set your actual data field to not display otherwise it will behave just like a popup lookup.

I discovered this not long after starting with alpha and put it down as a 'quirk' and added it to the learning curve. I have made good use of it as well. Now thanks to the link provided by Al I have learned that it is actually intended behaviour and a feature with a title 'Coded Fields' ( I am usually quite thorough at using the help file but missed this one for sure. I now see it in, of all places, the section on Field Rules->Lookups. :o )

One draw back of this feature is that since it is a field rule if you actually want to see the Id in your case you can't. Unless you assign the value to a variable on your form.

To answer your question:

In both cases, and according with the Help, it should be storing the ID but showing the value.

Could you elaborate on the help sections you are using? I think this is refering to what is presented in the list of choices.

1. With the popup style no matter what you see in the list of choices you will see the Id in the field.
2. with the drop down style if you have the Id set to display in the list of choices then the ID will display in the field.
3. With the drop down style if you have the ID set to not display then the first vakue that is set to display will be displayed in the field.

I can verify that with the 'coded field' set up the ID will be stored in the field.

[csda1]

Re: lookups - dropdown/dialog inconsistency

Hi Steve,

In both cases, and according with the Help, it should be storing the ID but showing the value.

I've asked for this since Alpha 4 version 2. I agree that there should be an allowed display value that is different from the internally stored values. You can do this with cascaded lists in the field rules, but I agree it should be allowed in the table lookups (and everywhere).

[Steve Wood]

Re: lookups - dropdown/dialog inconsistency

Ira,

It IS working like that, but only for table-based dropdowns, not the Dialog (or I'm configuring it wrong). Stores the ID, shows the value.

And on reports it shows the value as well. Expanding on what I wrote above, here are the field lengths and what appears for each field:

Lookup table:
- Language_ID, Charactor 5
- Language, Charactor 30

Main table
- Language_ID, Charactor 5 - but Formatted for 30 in Field Properties

So, the Language_ID field in the Main Table is storing (somewhere internally) the ID, such as "01", but it displays the text value, "German", and this carries over to reports. That's why you have to set the field properties Format to a length long enough to hold the text value.

[Steve Wood]

Re: lookups - dropdown/dialog inconsistency

Meant to edit the post just above...

Tim - Its clear to me after lots of testing, that the Dialog does not perform the "coded fields" as the dropdown does. The Dialog deposits the first field in the list of choices, regardless. The Help I am using is from a 22 page document titled Table Lookups written in 2002. It deals only with the dropdown option set on. The regular Help document does not draw any distinction between Dialog and Dropdown in terms of how you have to structure the list of choices.

Ira,

It IS working like that, but only for table-based dropdowns, not the Dialog. Stores the ID, shows the value.

And on reports it shows the value as well. Expanding on what I wrote above, here are the field lengths and what appears for each field:

Lookup table:
- Language_ID, Charactor 5
- Language, Charactor 30

Main table
- Language_ID, Charactor 5 - but Formatted for 30 in Field Properties

So, the Language_ID field in the Main Table is storing (somewhere internally) the ID, such as "01", but it displays the text value, "German", and this carries over to reports. That's why you have to set the field properties Format to a length long enough to hold the text value.