Junction table layout / field lookup advice needed
I have an Assets table with a numeric primary key and Asset Code as fields. Another table is called Customers, also with a numeric primary key and Customer ID as fields. There is a junction table called Assets_Customers which has the following fields:
Asset primary key, Asset Code, Customer primary key, Customer ID.
I have set up relationships between all three tables.
In the Asset_Customers layout, I have drop-down lists for both Asset Code and Customer ID. They are made from Value Lists which are based on the individual Asset and Customer tables.
How can I replace/edit the Customer primary key field in the Asset_Customers junction table based on the user's selection from the Customer drop-down list?
I'm unsure if this is the "idiomatic"/conventional way to do things in FMP: use Asset Code fields in each table to establish the relationship. Then in the Asset_Customers junction table set the Asset primary key field to Lookup and copy the Asset table's primary key.