Show data from related record

[rhody]

It seems like this must be very basic, but it has me stumped.

I have two tables, agencies (kp_agency_id, agency_name) and contacts (kp_contact_id, kf_agency_id, contact_name). Each contact is a member of an agency.

I would like to store only the id of the relevant agency in the contacts table (as shown above). When a user edits a contact, I want them to be able to choose from a list of agency names, but I don't want them to see the agency id.

When the user changes the selected agency in the contacts table, I want contacts::kf_agency_id to change but I don't want anything to change in the agencies table.

Everything I've tried either makes changes to the agencies table or requires showing the id #. What am I missing?

Edited September 29, 2010 by Guest
Clarify where user is making changes

[bruceR]

Well, for on thing you're missing a more explicit description of what you have tried. Uploading an example file is best.

But if you set up a value list to show only the value from the second field, then you'll get the result you want. Note that this works with all value list display formats (radio button, check boxes, drop down menus) but not drop down list.

See attached.

Agencies.fp7.zip

[rhody]

Thank you. That was one of the techniques I tried, but only as a drop-down list and an edit box. Odd that drop-downs, at least, don't work.

Ultimately, there will be many agencies, so many that an edit box that auto-fills from the value list would be more user-friendly than a lengthy pop-up menu. Is there any way to achieve that while still only storing the agency id in the contacts table?

[bcooney]

This might help:

http://fmforums.com/forum/showpost.php?post/365428/

[rhody]

Thank you for that. It seems a little clunky, but it does work.

[bruceR]

Thank you. That was one of the techniques I tried, but only as a drop-down list and an edit box. Odd that drop-downs, at least, don't work.

Well, it's not so odd at all if you think about it.

The general design and purpose of the dropdown list feature is to allow any new value to be entered; as well as enabling easy selection of value list values. So there is a basic conflict here, and FileMaker has not yet resolved that by changing the options for drop down lists.

[rhody]

The general design and purpose of the dropdown list feature is to allow any new value to be entered; as well as enabling easy selection of value list values. So there is a basic conflict here, and FileMaker has not yet resolved that by changing the options for drop down lists.

What do you mean when you say "allow any new value to be entered?" Do you mean when you allow the value list to be edited and the last choice is "edit..."? That's the case with pop-up menus as well. I guess I'm not clear on the difference between drop-down lists and pop-up menus.

[Vaughan]

No, a drop down list allows items to be selected form the value list, OR the user to type *anything they want* into the field. The value list is just there to make things quicker.

IMHO drop down lists should only be used when values in a field re-occur frequently BUT there is the need to often enter new values.

No everybody agrees with me. :P

[comment]

I would agree with you in a second - if the popups weren't so hideous-looking and more importantly, if they didn't force a font of their own (for the sake of the checkmarks, I suppose) and even more importantly, if that font would support more than just basic ASCII characters.

[rhody]

I agree that drop-downs are more aesthetically pleasing than pop-ups.

[comment]

It's not (only) a matter of aesthetics:

popup.zip

[rhody]

What am I missing? They both show the value list "α, β, γ, δ"

[comment]

Maybe they fixed this in version 11 (I can't test it right now). It certainly doesn't work in previous versions.

[Lee Smith]

In v10 they show as question marks, and in v11 they show the values of the list.

Lee

[Vaughan]

"α, β, γ, δ"

That's just greek to me.

[rhody]

Back to my original question. I loaded the data into the file and the pop-up menus are unworkable because they are so long. I have switched to a technique similar to one used in the templates that come with FileMaker: One field with contacts::kf_agency_id and next to it agencies::agency_name. Contacts::kf_agency_id is shown as a drop-down list of the agency names; agencies::name cannot be edited.

This works fine but I would still rather not show the kf_agency_id. Ideally, I'd like an edit field with auto-complete, validated to make sure the name the user types actually exists. I don't want to allow changes to agency names from this layout, but I also don't want to store the names in the contact table. Is there a recommended technique to achieve that?

This is what I currently have:

Edited October 2, 2010 by Guest
Clarify what I'm trying to do

[LaRetta]

if they didn't force a font of their own (for the sake of the checkmarks, I suppose) and even more importantly, if that font would support more than just basic ASCII characters.

Interesting. As Rhody says, they both show the values (on Windows). Further, they both show the values in tested 7.0v3, 9.0v3 and 11.0v2 (on Windows XP Professional). I didn't realize the display was limited on Macs (in this single regard).

[LaRetta]

:funnypost:

[comment]

Here's "before and after" in version 10.0v3. As you can see, it's NOT Greek where it should be... :

Edited October 3, 2010 by Guest