3 Replies Latest reply on Jan 4, 2009 12:23 PM by CountryBoy1

    Reordering data in Multiple Tables



      Reordering data in Multiple Tables


        If the data is reordered (sorted) in a main table, is there any

       convenient, SAFE, way to do the following:


         I.  Reorder the data in all the dependent child tables as per the main table.

        II.  Reset the Index (key) in the main table and the dependent Index value

             in all the child tables as sequential values (1,2,3,....N).  The Index key

             in the main table would not like this action for it is: indexed, auto enter serial,

             unique, allow override.  Also, most of the Indexes in the child tables are also

             unique and "unique" would find conflicts when resetting the Index. 


             It seems like there could be all kinds of turmoil in Step II as relationship values

             were swapped around before the Index values were finalized. This may be a

             bad idea.  Perhaps Step I could be achieved.  Thanks for your help.


        • 1. Re: Reordering data in Multiple Tables
          Jens Teich
             Do I understand correctly that you want to change primary keys?

          This is to be avoided and will indeed cause a lot of trouble.


          • 2. Re: Reordering data in Multiple Tables

            I. To change the order of records in a table, I believe that if you sort them, then export them, they will be exported in the order in which they are sorted. The records could then be imported again.


            II. You can't change the values in the key field without losing the connection to the children. But, you could create a new key field. Add a new field to the main table, then fill it with new serial numbers. Then go to the related table, add a new field to be the new match field. Show all records, then click in the new field and choose 'Replace Field Contents'. Click 'Replace with calculated result, then specify, and choose the NEW key field from the parent table. Click 'Replace'.


             Once that is done, you can edit the relationship graph to reflect the new relationship that you have created. Don't delete your old match fields until you are sure that the new relationship is working.

            • 3. Re: Reordering data in Multiple Tables

              Dear Scott:

                      Thanks for your reply.  I tried the exporting of sorted records and it worked OK.  As far

               as the reassigning a new key, I think that would also work, but I would have to be very careful

               as how it was done, and as you say, first backup the original data.  When I get brave enough,
               I will try this.


                       I am still wondering how to reorder (sort) other child tables as per the sort of the

                main table.  I have three fields in the main table that are used in a specific order to

                get the sort that I want.  The other child tables do not have the three fields that the

                main table does.


                       Thank you very much for your help.