Best way to overcome "Also display values from second field" display issues?

[CyborgSam]

I asked this a year or so ago, but I'm hoping more elegant solutions have been done.

Given: Table A with serial # key field and some text field. Value list of Table A where the first field is the serial # and the second is the text. Table B with a # field set to display the TableA value list as a popup menu or list.

Issues:

1) The popups don't display the second value list field if the layout field is not popped up.

2) Seeing the serial # is ugly, especially when sorting on the second field. Putting a calc field with just the 2nd field's value over the popup isn't elegant.

3) A reverse relationship where only the text is in the value list and a lookup occurs to get the serial # will fail if there are duplicates in the text field or if a record in TableA with changes to the text field is committed in one client while the menu is popped up in another client.

4) Editing a font to remove numbers works, but then the text cannot contain #s.

5) Opening another window/layout with a portal to choose a value isn't elegant and adds complexity.

How do FileMaker gurus do value lists like this? The attached .fp7 is a bare-bones demo which can be easily modified (no password).

ValueListDisplay.fp7.zip

[Reed]

version 8 eases much of the pain in this regard, and I still use a display field to mask the ID when I'm not in a field. The last thing I do is just change the text color of the actual id field to match the color of the layout and field fill, so users don't see the number at all.

[CyborgSam]

Reed-> Thanks! I hadn't noticed this change in 8 (just got it & need more play-time).

Question is: will FMSA 8 display like this in IWP? FMSA 7 didn't display the second value at all...

I'd still like to learn from others on how they do value lists in 7 (and earlier)?

[SlimJim]

Whether you use v7 or v8 you still have the same problem. Unless the second value that you are displaying uniquely determines the serial number you are also going to have to show the serial number.

If the second value does determine the serial number then the second relationship and lookup method completely hides the serial number.

[CyborgSam]

SlimJim->

Am I interpreting you correctly: you're pointing out that duplicates will always be an issue if only the second value is visible (I agree).

Assuming there are no duplicates in the second field, I'll use the checkbox in the Specify Fields... dialog "Show values only from second field."

[SlimJim]

Yes I beleive that duplicates will always be an issue.

As you say in v8 the option to show only the second field is available but it will still drop the serial into the field and unless you take some sort of step to hide it it will be visible.

Here you can use your imagination - hide the entire field behind a drop down arrow character (this is easier in v8 because there are native arrows) - make the field 1-pixel wide, and put it next to an adjacent field into which you will drop the value shown(a related field) which is set up as a button which goes to select/perform the serial number field - make the field invisible against the background as described earlier inthis thread - etc etc

[CyborgSam]

SlimJim-> I'm not seeing the behavior you're describing in FM8A.

One table has a serial# and a text field. The value list for this table has the serial# first, the text second and the "Show values only from second field" checkbox is checked. Another table has a number field, setup as a popup menu using the value list.

The field always displays just the second value, even though it is properly putting the serial# in the field.

[SlimJim]

I think we are saying the same thing - I have a Client Number field on my layout, the field is set up as a drop-down list with values from Client Number also display values from Company Name (show values only from the second field). The drop-down as expected only shows company names but once a name is chosen the Client Number shows in the field not the Company Name.

I hide this field with a Button which looks Ok here because I have symmetry: Choose Existing Client button and Enter a New Client button.

[CyborgSam]

The drop-down as expected only shows company names but once a name is chosen the Client Number shows in the field not the Company Name.

I just did some testing: drop-down lists and popup menus work differently in FM8 (on both Mac and PC)...

The drop-down list does show the first field (the #) once the field is exited. The popup menu always shows the second field (the text). This appears to me to be a bug...

[SlimJim]

You are right the pop-up menu does show the second value. (It never occurred to me to use a pop-up menu for a long value list) and the field behaves as if it were the serial number - better and better. Why doesn't this happen with drop-down lists?

So you can have a serial number field on your layout. Set it up as a pop-up menu with values from the field show a only a second field and it will behave like the serial number but only show the value in the second field.

This is weird!!!

[bruceR]

You are right the pop-up menu does show the second value. (It never occurred to me to use a pop-up menu for a long value list) and the field behaves as if it were the serial number - better and better. Why doesn't this happen with drop-down lists?

The purpose of a drop-down list is to allow entry of values that are not in the list. So a drop down list MUST display the key field. How would it otherwise know which value it is supposed to display. In another forum there is an effort being made to let you tab into a dropdown menu, that is the real solution to this problem.

[SlimJim]

The purpose of a drop-down list is to allow entry of values that are not in the list. So a drop down list MUST display the key field. How would it otherwise know which value it is supposed to display.

I can't agree that that is the sole purpose of a drop-down list. On a pop-up menu typeahead is not supported so on a long menu you have to use the arrows to scroll through to find your selection whereas on a drop-down you can type the first few letters and quickly arrive at the item you are searching for. In FM8 the list does not have to show the key-field, however, the field on the layout does show the key field and here we have the difference.

Easily demonstrated on a blank layout. Make three copies of your key-field. Set one up as a drop-down list another as a pop-up menu (both with a VL of the key field and another field - show only the second) and the third as an Edit Box. When you choose a value either by the drop-down list, pop-up menu or direct entry into the edit box the copy of the keyfield set up as an pop-up shows the second value, the other two copies shows the keyfield value.

I still consider this to be wierd - in effect using a pop-up menu decides what the key field displays on the layout.

[sledg]

That is weird SlimJim. I just figured out that difference between drop-down and pop-up today. It was a little frustrating to learn that.

I would expect that the "drop-down list" option is a bug. I don't think they intended it to be that way. If they did, then who knows!