1 Reply Latest reply on Apr 4, 2014 6:10 AM by philmodjunk

    Copy Key Field to Related Table

    PaulDougherty

      Title

      Copy Key Field to Related Table

      Post

           I have several spreadsheets that I've imported from Excel. I need to link them together but right now their only common fields are First, Last, and Middle Initial. As you know that will not work in production because someone's name is going to be changed through correcting a misspelling etc. and records in the child tables will be orphaned. 

           I'd like to know how to do 2 things:

           1. Have the parent table autonumber the key field by itself (it currently has 800 records that I'd rather not enter by hand)

           2. Copy that autonumbered primary key from the parent table to a field in the child table.

            

           After competing this I'll change the relationship to be on the autonumbered key field.

            

           Thanks,

           paul

        • 1. Re: Copy Key Field to Related Table
          philmodjunk

               1. Before importing the data, define the primary key field as an auto-entered serial number. After importing this data, still define that field as an auto-entered serial number but then do a show All records and use Replace Field Contents with the serial number option to number them and also update auto-enter options.

               2. To copy the correct value to the correct record in the child table requires FileMaker (or a person for that matter if you were to do this "by hand".) to be able to tell which record in the child table matches to a given record in the parent table. It would appear that name fields are your only option for this. If so, you can go to Manage | Database | Relationships, click on the box (Tutorial: What are Table Occurrences?) for the parent table and use the duplicate button to make a copy of it. Link this copy by all three name fields to the table occurrence of your child table.

               You can now go to a layout for the Child table, show all records and use Replace Field Contents with the calculation option to copy over the Value of the field you to which you gave a serial number value in 1. above.