8 Replies Latest reply on Feb 19, 2009 1:16 PM by sjr1956

    Primary keys

    sjr1956

      Title

      Primary keys

      Post

      I'm being thick of course (though in mitigation I am new to FM9), but where/how/do I store primary key data so that if I need to change it, it changes are propagated to all of the tables in which I need it to appear.  I think not changing it (i.e. using an auto-created serial number) is the best plan, but in any case I would still need to know how to get that auto-created serial number into the desired fields/tables.  Any suggestions gratefully received.  I've been reading "the only FM9 manual you will ever need" and it's like swimming uphill in treacle.

       

      Cheers - in advance....

        • 1. Re: Primary keys
          TSGal

          sjk1956:

           

          Thank you for your post.

           

          I'm sure others will pipe in and say "Don't ever change your key field".

           

          Since most of your tables are linked by a key field, the trick is to find those records with the old value and change it to the new value.  One way is to create a new field that will hold the new key, and then run a script which will find all records with the old key and replace it with the new key, and then change the original record key to the new key.  Assuming your key field is titled "KEY", and you put the new value into a field named "NEW KEY", the script may look like the following:

           

          Set Variable [ $old = KEY ]

          Set Variable [ $new = NEW KEY ]

           

          Go to Layout [ <layout with related information> ]

           

          Set Error Capture [On]

           

          Enter Find Mode []

          Set Field [ <key field> ; $old ]

          Perform Find []

           

          If [ Get ( FoundCount ) > 0 ]

             Replace Field Contents [ <key field> ; $new ]

          End If

           

          Set Error Capture [Off]

           

          Go to Layout [ <original layout> ]

           

          Set Field [ KEY ; $new ]

          Set Field [ NEW KEY ; "" ]

           

          -------------------

           

          In essence, two variables are created to hold the new and old key values.  We then switch to the layout of a related table and search for the old key.  When found, replace the old key with the new key.  If you have other related tables, do the same steps.  When finished, return to the original record and update the KEY field with the new value and null the NEW KEY field.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Primary keys
            raybaudi
              

            Hi sjr1956

             

            TSGal is a very good prophet ;)

             

            Don't ever change your key field.

             

            The answer of TSGal is just enough to know why but I have another question: which is the advantage ?

            • 3. Re: Primary keys
              sjr1956
                

              Dear TSGal,

               

              Thanks for this very detailed solution.  However, I guess I didn't phrase the question clearly.  What I'm wondering is when I create the database (for collecting scores, grades and other academic performance stuff for a school), do I need to import the primary key into each table separately.  Or, is there a way that the primary key can be propagated to all of the tables where is is necessary having imported it only once?  There will be about 20 tables which will need to have student ID as the key.

               

              Thanks again, and I apologise for the unclear question, though the solution is very useful, as it may come to that - our current system of student IDs is not always fool proof.

              • 4. Re: Primary keys
                TSGal

                sjr1956:

                 

                Actually, you phrased your question clearly.  It wasn't clear to us what you were trying to accomplish!  :-)

                 

                Seriously, a lot depends on how your tables are configured.  Right now, I'm assuming you are importing information into the tables based upon the student name, and everything links together by that.  Somehow, you will need a "key" field to relate to in your tables.  For example, in your students table, each student will have a name (and soon, some kind of ID).  In your classes table, you will have a link to the student table, a classID (subject), and a teacherID (who is teaching).  If you just import information into these tables, you still need some kind of "key" field that allows you to connect them together.

                 

                I know I am giving you some vague answers, but if you have 20 tables, and you are importing data into each of these 20 tables and you want them related, then make sure you have some kind of "key" identifier/field.

                 

                TSGal

                FileMaker, Inc. 

                • 5. Re: Primary keys
                  sjr1956
                    

                  Dear TSGal

                   

                  Thanks for your patient response. 

                   

                  I have student IDs, but do I need them in all tables - the one common element?  Or am I missing something?

                   

                   

                  • 6. Re: Primary keys
                    TSGal

                    sjr1956:

                     

                    I can't determine if you need the student ID's in every table.  That is something you need to determine.

                     

                    If you import the information, does it need to be connected to a student?  If the answer is "yes", then you will need some kind of identifier to let you know it is linked to a specific student.  If the records are unrelated to a student, then you don't need an identifier/key.

                     

                    TSGal

                    FileMaker, Inc. 

                    • 7. Re: Primary keys
                      Smerg
                        

                      I have what I believe is the same question. I'm starting my first database of related tables.

                       

                      Table 1: Organizations (primary key linked on foreign key in Table 2)

                      Table 2: Announcements (foreign key linked on primary key in Table 1)

                       

                      The primary key field in Table 1 will be automatically filled with a unique serial number. Is there a way to automatically send the same number into the foreign key field in Table 2 when I enter a new announcement into the database? Does the link itself do that?


                       


                       


                      • 8. Re: Primary keys
                        sjr1956
                          

                        Dear TSGal,

                         

                        I guess you answered my question.  Nearly every item (%, grade, class, tutor, etc) has to relate to a student eventually.  I am dealing with student performance data.

                         

                        Thanks, though i think auto-creation may be the best way to go in the end, rather than using our student ID system - manually generated and not infallible.

                         

                        Thanks for your time and help.