What field is the relationship between the tables based upon? Make sure it's not based on the SSN. Create a new Key Data primary key?
What you've encountered is why experienced database developers avoid building systems where a user can enter a value to be used as a primary key. (Field that uniquely identifies a record and is used to link it to records in other tables.)
I'd define a new auto-entered serial number key and use it to link your tables. This field does not have to be present on any layouts, though you may want to put it on a layout temporarily if you need to confirm that it is working correctly. If users expect to see your current PatientID field on reports and such, you can keep it in your table to use for reports and in finds--just don't use it in a relationship to link to other tables.
thanks a lot
I was playing around with it and it kind of donned on me to do exactly what you said. Just hide an auto serial number field and build relationships from that. Easy solution but I'm pretty new to this.
Thanks for the help!
SSN is pre-defined by the government to be unique which leads people to believe it's a good choice for a PK. Of course the issue is: what do you use when dealing with a non-USA-resident/citizen? Easy to get caught on that sort of thing . . . I've been.
A good point and also note that in this case only the last 4 digits were being used so there was no guarantee of uniqueness even without such issues. You can also factor in the fact that some people will give a false SSN and that storing SSN's in a database when you don't need to can expose you to unecessary legal liability if a security breach results in someone using the stored SSN's to steal identities...
As someone who sub-contracts many people to work on many projects I do record Social Insurance and GST Numbers (Canada), as well as SSN. Each field is validated as unique. On a couple of occasions I've had to inform people that they're using an incorrect number. In both cases it turned out to be human error . . . one misread digit. Uncorrected this would have led to no end of tedium dealing with the tax folks at the end of the year. Further . . . a person could offer a bogus number that would go undetected if it's not a duplicate of one already stored in a given solution. Given the privacy issues involved, I believe it would be impossible to verify a number with any government agency, but I'm not certain.
If it's necessary to store this data, the computer should be locked down tight; firewall(s), encrypted backups, and password required to log in, wake from sleep, and even to unlock the screen saver.
All points for meaningless primary keys and there are more:
1. Key Data
Fields: 1. Last Name 2. First Name 3. SSN 4. Patient ID (Pulls first initial of First Name, first initial of last name and last 4 of SSN to create Patient ID)
So Wilma Flintstone gets the ID of WF1234. But then she divorces Fred and marries Barney and her ID becomes WB1234. Ooops, broken relationships (and I'm not talking about hers with Barney either.) :smileyhappy:
Take a look at : http://www.nightwing.com.au/FileMaker/demos9/demo910.html
Can be very handy indeed for creating Primary Keys when you don't want to mess around with serials after importing / updating a solution.