What is different between "Asset Code" and its "numeric primary key"? What is different between "Customer Primary Key" and Customer ID? You seem to have a pair of fields in each table that server the same purpose.
This is your table relationships, correct?
This implies that more than one customer can own the same asset. Is this truly the case? If not, you don't need the Assets_Customers join table.
The Join table should only have two fields: Asset Primary Key, Customer Primary Key
To select a customer in a record in Asset_Customer, format the Customer Primary Key field with a 2 column value list. Column 1 must be the Customer Primary Key field from Customers. Column 2 should be a name field or a calculation field that combines name with several other fields so you can see which customer goes with a given primary key. When you select a customer from this list, their primary key will be entered into Asset_Customer.
The table names are different, but here's a demo file of this concept: http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html
Thanks for responding. The example was very helpful. However, in the portal row I need a drop-down list with auto-complete and not a popup menu, because the list is going to be long i.e. the user should be able to enter an asset code, and the list will allow auto-completion. However, direct text entry means the user has to enter the primary key itself (and not the asset code, which is more meaningful), which is not a reasonable expectation from a user perspective. That is the very problem I'm experiencing.
Yes, that's an annoying limitation of FileMaker value lists. Just when you most need the auto-complete feature, you find it's not available because you need the auto complete for column 2 ( the asset code ) while the drop down enters the column 1 (primary key) data and FileMaker won't do that.
Here's a workaround that I've come up with. Perhaps you can adapt it to your needs. If asset codes are unique, then you can simplify the process quite a bit as this thread includes a method for dealing with duplicate names.
I'm assuming there are reasons why you don't use asset codes as your Primary Key.
I'll have a look at the example, thanks.
The Asset code is not being used as the primary key as it could change in future.