8 Replies Latest reply on Feb 2, 2010 10:17 AM by Steve Wright

    Portal Problem



      Portal Problem


      Here's my issue:


      2 tables


      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)


      2. Personal Info

      Fields: Numerous Misc. Fields including Date of birth, Children, Birthplace etc.


      Patient ID is the related field.  Under the Key Data table I have a portal to Personal Info which displays all of those fields (only one record is displayed at a time)


      I have made it so you can create a new record through the portal. The issue is that say I entered the SSN incorrectly in the key data table. Now if I go back and change it, it treats it as a completely new and different record under the Personal Info table so all of that information disappears and has to be re-entered. Any way around this?


      Thanks for any comments 


        • 1. Re: Portal Problem
             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?
          • 2. Re: Portal Problem

            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.

            • 3. Re: Portal Problem

              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! 

              • 4. Re: Portal Problem
                   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.
                • 5. Re: Portal Problem
                     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...
                  • 6. Re: Portal Problem

                    Yes Phil,


                    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. 



                    • 7. Re: Portal Problem

                      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:

                      • 8. Re: Portal Problem
                        Steve Wright

                        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.