7 Replies Latest reply on Jul 6, 2012 9:28 AM by firth5

    changing the parent ID field -- Best practice ?

    firth5

      Title

      changing the parent ID field -- Best practice ?

      Post

      Before I learned to use auto-gen serial numbers as parent ID's for related tables, I built a database that related to normal data-entry fields.  Years later I'm returning to this database, and want to update it (create new serialized ID fields and link the tables by these).  I've got probably 15 tables with several thousand records each.  Obviously I don't want to lose all the links....

      If you have a method that works, please let me know.

      Thanks,

      eric

        • 1. Re: changing the parent ID field -- Best practice ?
          philmodjunk

          Keypoint, make back up copies just before you make any such update. That way if it goes bad, you can toss the file and try again with a copy of the most recent backup.

          In FileMaker, you can create new relationships with new occurrences of your tables without changing the existing relationships.

          Replace field contents can be used to assign serial numbers to a new serial number field for existing records and update the auto-enter options at the same time. This same tool can be used with your existing relationships to assign the foreign key field's matching value. You can then carefully test each new relationship using the new table occurrences before changing the original relationship to use the new key fields.

          • 2. Re: changing the parent ID field -- Best practice ?
            Abhaya

            Hi

            I'm explaining the details.

            Let there is exist 2 tables e.g parent and child[1,2,3....]

            So you want to make a Auto serial field in parent table then go for following steps

            1>Create a filed and make it as Auto serial and placed all serial value in it by script means all recods have a Id(serial value from 1 to last no of record let it be 200).After asssigning all fields value then check its auto enter option before than don't check it just do  by manually.

            2>Write a script in which  following steps must be there.

            Go the layout parent table

            Go to  record 1st

            loop

            set variable(var=Newautoenter filed value)

            go to related record from parent(it will show all child records for a single parent record)

            repalce fields contains with var in child table(setting FK)

            commit record request.

            go to record request next

            exit loop after last.

            It will set all parents Auto id in its childs foreign key fields.If  you will go through this process then you will not loose any existing link in your database.

            Hope you will get your exact answer.

             

            Thanks

             

            • 3. Re: changing the parent ID field -- Best practice ?
              philmodjunk

              Yes, but with Replace Field Contents you do not need to write a script as you do not need a loop. It can also update the next serial value setting on the field at the same time.

              • 4. Re: changing the parent ID field -- Best practice ?
                Abhaya

                Yes it's right.No need  for looping

                • 5. Re: changing the parent ID field -- Best practice ?
                  firth5

                  Thanks to both of you.

                  Phil, if I'm understanding correctly:

                  Make new SerialID field in parent table.  Replace field with serial numbers starting at 1, etc etc...

                  Make new childID field in child table.  Replace field with formula = parent table: SerialID

                  then use these new fields as the relationship.

                  I've tested and seems to be working.

                  ... Not sure why you're suggesting to make a new table occurance though.

                  Thanks!

                  eric

                  • 6. Re: changing the parent ID field -- Best practice ?
                    philmodjunk

                    It's not a requirement, but the idea is that if you add a new occurrence, you can leave the old relationship untouched while you test and evaluate the new relationship based on the new key fields. Then, when you are convinced the relationship works, update the original pair of occurrences to use the new pair of key fields.

                    • 7. Re: changing the parent ID field -- Best practice ?
                      firth5

                      Got it.  Thanks!