Key Driven Drop-Down List

[Sequential Logic]

I'm trying to find a solution to a problem, but I'm not sure how to explain the problem, so I'm going to give the entire list of events that lead up to the problem I'm trying to find a solution to.

Create a new file "test.fp7".

Create a Table called "A" with a Field named "B_ID".

Create a Table called "B" with a Field named "ID", and a Field named "NAME".

Create a Relationship from A.B_ID to B.ID.

Create a Layout with A.B_ID & B.NAME (as a dropdown box showing all possible entries from Table .

Don't really have to go any futher then here as it doesn't usually work this far!

But if it did the next thing would be to make sure that A.B_ID gets updated with B.ID whenever the dropdown box is changed to a different name from B.NAME.

Anyone know how to do this in FileMaker?

What do I have to do?

Please Help!

[-Queue-]

You can add another field in A to hold B_Name, that can be either text or global. Create a relationship from A.B_Name to B.Name, and make A.B_ID an auto-enter calculation with 'Do not replace existing value' deselected and a value of Case( IsEmpty(B_Name); B_ID; B 2::ID ), where B 2 is the new relationship.

You wouldn't want to put B.Name directly on the layout for selection or else you would be changing the related name field each time, according to the original relationship.

Is this what you're trying to do?

[Sequential Logic]

I know that that was the only way in previous versions of FileMaker before 7.

But FileMaker 7 seems to be more like a "real database" and a Relationship is more like a "Foreign Key" would normally be.

In any SQL Database I'm just trying to do something really simple, I just kind of hopeing that FileMaker 7 can take care of it the same way.

[-Queue-]

Yes, but changing a related field's value in SQL won't change the key either; it will only change your related data, which doesn't sound like what you would want to do. I think what might work for you is a value list based on id and name, where both display in the drop-down, but only the id is changed when you make a selection. Of course the menu would have to be used on the id field and the related name field would not appear on the layout.

[Sequential Logic]

That sounds closer to what I want to do....

I think the simplest way to put it is that I want to show the names in a dropdown but only store the id of the name that was chosen.

[Ugo DI LUCA]

Hmm...

That's currently how field based value list work.

You can display 2 fields for "user friendly choices" and then the first value is what you get.

If you need the name to be filled based on the key selected, either a lookup, an autoentered calc or a related field according to what exactly the purpose is would do the trick.

I'm not sure I understand how far you want it to go ?

[Sequential Logic]

Ok... is there a way to do this then:

Have a dropdown on the layout displaying the names

and have a field that just holds the id that automatically changes to the correct # whenever the dropdown field gets changed?

[Ugo DI LUCA]

No because it isn't accurate. SMITH, John is not unique vs ID00055 *might* be.

The only way to hide an ID AFAIK is to use a portal, and use a script to set the content of the ID based on the portal selection.

Involving this trick with 7 is more like a good elegant solution as the list can come into a new window poping up at needs.