It would help to know how you have related these two tables and the purpose for each table. Why do you need the names table? What does it do for you?
Ideally, your artifacts table should have a primary key field. In FileMaker, we often begin the name of such a field with __fk or something similar to make it easy to spot which field serves as the primary key. In FileMaker, this field is almost always an auto-entered serial number, though Filemaker 12 offers an alternative using Get ( UID ).
This field would then be used to link a record in artifacts to other related tables such as:
Artifacts::__pkArtifactID = Names::_fkArtifactID
But this assumes a one to many relationship, one Artifact to one or more records in Names. That may or may not be what you need here. It may actually be a many to many relationship where more than one artifact can be linked to the same name and can link to more than one Name. But that's something you will need to determine and let us know.
currently setup Names:: pk_nameID =Artifcats::_fkNameID
Let me explain why i went this way.
The museum has many objects e.g wedding certificates, artifacts, letter,post cards etc. I noticed that just about every table has names, location and date fields. That's why i thought i should setup a name, location and date table to relate every object to.
For example a postcard will have a 'from' and 'to/recipient' field as well as the location/landmark.
This way if the same name is in another table such as in a letter table the name will show up on the dropdown which will:
1)ensure that the name won't be misspelled, the user will notice it is already there (he will figure out if its the same peroson or another)
2)if we would like to search any information on a specifiic person we will find any letter and post cards that he has sent.
So each record in names represents a specific person?
Might a single artifact record be linked to more than one such record in Names?
Using the postcard as an example, you might record the name of the person who donated the item, the name of the artist who created the image on it, the name of the person who sent it and the name of the person who received it.
This suggests a many to many relationship between names and artifacts.
If so, a join table is in order so that you can link many individuals to many artifacts and a given artifact to many individuals:
Names::pk_NameID = artifact_name::_fkNameID
Artifacts::pk_ArtivactID = artifact_name::_fkArtifactID
Yes. each record i the names represents a new person.
Yes, it would be a many to many realtionship.
Thank you for your help i will use a join table .
Would any of this explain why i when a user chooses a name form the drop down under e.g. postcard table that it creates a new instance of the name with a new id, eventhough it's already under the names table?
For example . if multiple people lived in Chicago, the first time Chicago is added thru the post card table ( which is related to the Location table) then Chicago gets added under the Location table and get ID. When the next post card info is added Chicago can be picked thru the drop down and seems to work fine. However , once i open the Location table i see 2 instances of Chicago with 2 different ID's which i'm assuming will cause problems down the road since any given person / city can have multiple ID's which i dont supose is a good thing.
Forgive me for being a newbie...
Would any of this explain why...
It's hard to say without knowing more about how you designed the layout. Adding a new name record is best added on a layout based on the name table and validation rules can catch instances where the new name is already used in another record in the names table. You'd select the "unique values" validation rule to do that.
Here's a demo file on many to many relationships that you may find helpful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
Great! will check it out.
Thanks for your help!