Finding the Primary Key of linked table

[mdfOffman]

Example:

I have an Asset table with a FK linked to the PK of an Owner table.

On the Asset layout, I create a field called "Owner" which I select from the OwnerName field in the Owner table.

With that selection, the system doesn't seem to know what the fk/pk field content is.  So, if I want to go to that record, Ii can't use the Owner::PrimaryKey as the search parameter cos this layout doesn't know what it is.

The only way I can make it know the FK/PK content is by selecting from a value list in which the PrimaryKey is the first field in the search - which stores a mile long text on the layout and means I can't put a connected field in without also including the Owner PrimaryKey field.

How can I select "OwnerName" in the value list and have the layout identify the FK/PK content for my lookup script without selecting from a PK value list?

Thanks

[comment]

Define your value list to use values from Owner::PrimaryKey, also display Owner::OwnerName and select the 'Show values only from second field' option.

On the Asset layout, either format the FK field to display as pop-up menu, or place the Owner::OwnerName field onto the layout, make it non-enterable, and place the FK field under it. Use this to select the owner for the asset. There is no need for an Owner field in the Asset table (in fact, it would be bad practice).

Note that this requires the values in Owner::OwnerName field to be unique.

 

Edited November 30, 2022 by comment

[mdfOffman]

Thanks. 

1.   I've tried showing only the second field but, as the help message says, it's still the first field that gets stored.  The only thing I admire in MS Access as against FM is te combo box in which you can link the first field and display the second field.

2.   I'm trying to save the space of not including the fk fild on the layout!!

Thanks for trying

[comment]

1 minute ago, mdfOffman said:

it's still the first field that gets stored.

Yes, and that is the only thing that you want to be stored. All the other information about the owner is stored in the Owner table, and only in the Owner table. That's what a relational database is all about.

 

3 minutes ago, mdfOffman said:

link the first field and display the second field.

That is exactly what will happen if you implement my suggestion.

 

5 minutes ago, mdfOffman said:

I'm trying to save the space of not including the fk fild on the layout!!

If you want to be able to select the owner, then you must place the FK field on the layout (disregarding for the moment other means of selection). It does not take any additional space because it either displays the owner's name directly (if formatted as pop-up menu) or is is hidden under the owner's name.

 

 

[bcooney]

Google FileMaker picker techniques. When the available choices for a foreign key are greater than 10 values, better ux methods are used.