6 Replies Latest reply on Sep 20, 2012 8:57 AM by philmodjunk

    Changing key IDs



      Changing key IDs


           We are using FM11Adv on Windows.

           The original key IDs for the main table need to be changed, the database has been active on 3 stand alone computers, with each creating is own new records with serial numbers independently of each other.

           The key ID has then been used in several auto-enter calcs.

           When we change the key IDs, how can the auto-enter calcs be retriggered, or can they?

           Over 25,000 records




        • 1. Re: Changing key IDs

               Are there records with duplicate values in this field?

               Are there related records in related tables?

               Is the data still in separate files or have you imported to combine them?

               Replace Field Contents has an option for entering serial numbers and updating the serial number settings at the same time, but the answers to my questions may indicate that such an operation by itself may not perfectly resolve this issue.

          • 2. Re: Changing key IDs

                 Yes there are duplicates and there are the same table, on different computers. They had placed a copy of the file on each  computer. Each working independently of each other.

            • 3. Re: Changing key IDs

                   That leaves this very important question unanswered:

              Are there related records in related tables?

                   In otherwords, is this key used to link to other records in other tables? If so, that issue has to be dealt with before you combine the data from your separate computers or a records created on one computer may link to related records created on another computer due to the duplicate values in the key field.

              • 4. Re: Changing key IDs

                     Yes to your related tables question.

                     This field is also part of several auto-enter calcs

                • 5. Re: Changing key IDs

                       Then you will need to very carefully do this in stages.

                       First, before you import records to merge the data, you will need to resolve the duplicate values issue.

                       I will assume that you have a field of type number that is auto-entered as a serial number, but variations of this approach can work with text and with other auto-entered calculations.

                       Go to a layout based on the table where this field is defined and where this field is present so that you can edit its value.

                       Select Show All Records

                       Put the cursor in the field.

                       Select Replace Field Contents and select the calculation option.

                       Use this calculation, but substitute your field name for "Yourfield" in the calculation:

                       YourField + 0.1

                       Click OK twice to close the specify calculation dialog and to update the key field.

                       Go to one layout for each table that is related to this table and do the same exact operation on the match field.

                       Your auto-enter calculations will update automatically if they are for a field defined in the same table and if the "do not replace existing value" check box is NOT selected for it. To update auto-enter calcualtions where this is not the case, see this link: Updating values in auto-enter calc fields without using Replace Field Contents but you may want to wait to do such an update until you have merged your data so that you only have to do this once for each field.

                       Do this for all but one of your copies of this database file, adding a different decimal for each computer. This eliminates any duplicate values. Now you can merge your data by importing the records and a record created on one computer will not match to related records on another computer.

                       Once you have the data merged, it's possible to use Replace Field Contents to assign new, integer serial numbers to each record, but you have to be very careful doing so. It might not be worth the effort:

                       Add a new number field to your Parent Table. Use Replace Field Contents with the Serial Number option to load it every record in the table with a serial number. Then go to a layout for each related table and use Replace Field Contents to copy the value from this new field into the existing match field. Return to the layout for the parent table and use Replace Field Contents to copy the value in the new field into the original key field. Finally, Update the "next serial value" setting on the key field so that the next new record will not receive a value that duplicates an existing serial number in this field.

                  • 6. Re: Changing key IDs

                         Oh yes, please do all of this with copies of your files so that you can toss them out and start over if a Replace Field Contents operation doesn't produce the results you expected.