I have a filemaker database, and I have a table of products that is filled out and a table of people that is filled out. I have a join-table with a foreign key to the product-pk, and one to the owner pk. There is another field on the join table that describes the relationship--an "aquisition date"
What I would like to do, is in a layout with the person details, to have a portal that has two fields onto the product table. I can type in the type of the product, and the name, and if a product with that type and name already exists, I'd like to add a relationship to it to the join table, so that it is related to the current person record If it doesn't exist already, I'd like to add it to the product table and add a relationship in the join table. I'd also like to be able to edit the acquisition date and have that change on the join-table only. I could do this fairly easily in an object relational database, but when I do it in filemaker by setting "allow creation of new records", I get lots of duplicate records in the Product table. Any help would be appreciated.
You are creating the records in the wrong table. You should be creating records in the join table and not in products table.
Linking to the product by typing the name is a bad practice, the drop-down and using the keys for linking will be better.
The uniqueness of the joint could be added through the validation