Many to many table accepting values that don't match PK tables
I am new to FM and I have created a many to many relationship using a joining table as follows;
Table: Contacts; PK is "conIDNum"
Table: Supplies; PK is "supIDNum"
Joining Table (the many to many table): Contact_Supplies: this has its own primary key "consup_IDNum" just to give you that information, but the foreign keys are "consup_conIDNum" and "consup_supIDNum"
therefore: "consup_conIDNum" = "conIDNum" and "consup_supIDNum" = "supIDNum"
so I have joined appropiately in the realtionship graph, but I don't know how to set it so that the many table "Contact_Supplies" only accepts records if they already exist in the "Contacts" table and in the "supplies" table.
At the moment, I can add records to the "Contacts_Supplies" table but it seems to accept any values in the foreign key fields when I was testing it. I want it to only accept values in these foriegn key fields if the value exists in the related table PK's.
and yes they are all number data types, the PKs being unique with auto serial number.
I have tried ticking the checkbox in the "Edit Relationship" window where it says under "Contact_Supplies" table, "Allow creation of records in this table via this relationship" but this didn't work.
I tried going into the "Manage Databse", table; "Contact_Supplies" and highlighting the field "consup_conIDNum" and then clicking on "options" for the field, and ticking the "Exisiting Value". but this didn't work. I realised later that it refered to an exising value from the same field. SO sigh.... I tried on the "Auto-Enter" tab, the "Looked-up value" thinking that this may work, and I set it to look up the "conIDNum" in the "Contacts" table, but this didn't work either. so I am stuck.
Can someone point me in the right direction.