Single Relational Lookup

[dhartin]

Hoping someone can help me what should be a simple thing? though I cant find any threads that help me out with this specificaly

Am new to FM though am accustomed with relational database design

To keep it simple - I have two tables:

1) Matters

2) Matter Types

The matters table has various fields relating to the matter.

What I want to be able to do is have a drop down list on the matter layout so I can pick a matter type from the matter types table.

Now I know how to do this through creating the relationship between the two tables (on MatterTypeID) but I cant get arround the drop down list displaying the MatterTypeID once selected.

I want the drop down list to show the types then when I select one - still display that MatterType - WHILST mainting the relationship (eg. if I change the spelling of a matter type I want it to be reflected in all the matters using that type)

All the examples I've seen seem to add the MatterType (or whatever the example field is) to the Matters table. So if the type changes - the applied matter type is still there with the old spelling?

In Access and VB this is a simple thing so am hoping that FM provides the same (or similar) functionality???

ANY comments would be appreciated

Thanks

Damian

[Fenton]

FileMaker 8 lets you do this, mostly. You can "show only" the 2nd field, the MatterType as the field to show in the Value List, while using the MatterTypeID as the 1st field (and the relational key).

Then, on the layout where the drop-down list is, put the related MatterType field (from its table) on TOP of the MatterTypeID. Either make the MatterType field opaque, or put the ID behind an opaque layout object.

Make the related MatterType field non-enterable (Field Behavior), while the MT ID is enterable. So, when you click on MT, you go thru to MT ID; but you only see MT values in the list. (Or you can put a "drop-down arrow" button, which uses the single step, Go To Field, specifying the ID field)

You do still see the ID in the field when you click into it however; just not in the list. You can make the text color either match the background color, or the highlight color (which you can't control). So it is not so noticeable. I don't think you can completely hide it however. It is, after all, the value in the field. You'd also want a little button to Clear the field probably. Because otherwise it's a little awkward, as you can barely see the ID in it.

Perhaps others have a more aesthetic approach, that is also simple. Personally I wouldn't build something complex for this.

[dhartin]

Thanks for the post - I will have a play and see what it looks like.

What do you (others) normaly do in this circumstance? show both the ID field and the text value? Or just leave the text value in a field in the table (and worry about updating the values in exisiting records when that happens?)

[Fenton]

If you do it right the only time they see the ID is when the list drops down; and they only see it in the actual field itself, not in the drop-down list. And it can be muted so it's hardly noticeable.

They would only be clicking in a populated field if they wanted to change the field's value. If you cover the ID field with an opaque non-enterable related field (which corresponds to the list dropping down), and soften its color, they would hardly notice; at that point they're mostly looking at the list.

And no, I would never sacrifice relational integrity just for looks. Most people who've used FileMaker for years have gotten used to the IDs (though few liked them). So I'm fairly happy with the value lists in 8. You can alternatively use a filtered portal for choices.

When you think about it, it would be somewhat strange if FileMaker did not show the actual value in the field at any time. Aesthetic, perhaps. But it raises some questions.

[dhartin]

Yep, works well thanks

I see what you mean about the ID value showing when you select the field, which is not that big a problem (especialy when you change the font color) but it means you lose the "auto-complete" functionality and cant see the value when the field is selected :

In MSAccess you can have two column combo boxes which let you have the first field(ID) the bound/related field but only display the second..

That way it's completly seemless to the user.

Still interested to hear what everyone else thinks about this - I agree that the relational model is best (eg I dont want to just use a lookup that stickes the text value in the main matter table) but I want the use to be able to autotype in the value or select it from a drop down and retain the ability to change the spelling/text of the related data...

The current solution is very neat though thank-you, didn't actualy think of doing that myself :

[dhartin]

Thought I might try revisiting this and see if anyone else had any comments.

I've ended up back at the start (with storing the text from the lookup directly in the table)

I figured I could write a script to allow changes to the lookup values - then bulk update all the records that already have if applied - rather than reduce the functionality for the end user??

Thoughts appreciated :)