4 Replies Latest reply on Mar 27, 2014 3:42 PM by philmodjunk

    Moving fields from one table in a file to another is it possible?

    ThomasBurke

      Title

      Moving fields from one table in a file to another is it possible?

      Post

           So i currently have a file with 1 table that has about 5000 records, and I am looking to split all the fields into multiple tables.

           Is it possible to move fields from table 1 to table 2 and have all the data transferred with it? Right now the only way i can think to do this is.

           1) export all my records and fields.

           2) create a new file with all the needed tables

           3) re-import the original data back into the new file with multiple table.

           I would prefer to not have to do this since it will require me to have to re-create a brand new file and then worry records might get changed during the export/import process.

            

           thanks for the help.

            

        • 1. Re: Moving fields from one table in a file to another is it possible?
          ThomasBurke

               I guess long question short. Can you split 1 table into multiple tables while keeping all the record information in tack.

          • 2. Re: Moving fields from one table in a file to another is it possible?
            philmodjunk

                 There's no need to export nor to create a new file.

                 Let's say that you have data in  table named "Produce" in a Field named "Fruit" And you want to set up a related table with Fruit moved to that new table:

                 1) Define the new table and field in your existing file. Since this is to be a related table define a new Fruit field and also a foreign key field to link to the primary key field of your Produce table. Then link the new table to the original by foreign key to primary key.

                 2) Go to a layout based on your original table and Show All Records

                 3) go to a layout for your new table and select Import Records | File. Select the same file that you have open and select the Produce table as the source table. Then use the import field mapping dialog to map the source table's primary key to the new table's foreign key and to match up the two fruit fields.

                 4) Import your records.

                 5) Now you can go back to manage database and delete the Fruit field from the Produce table. You'll also need to update layouts as needed to refer to the new field of the new related table.

            • 3. Re: Moving fields from one table in a file to another is it possible?
              ThomasBurke

                   Thanks Philmod,

                    

                   I guess i have 1 follow up questions. I have split my database into a bunch of tables and everything is working great for all of my existing records. I was curious if there was an easy way to create a new record in all tables at once.

                   For example if my tables are linked with the relationship associated with the Field name "Sample Number". If i create a new record in Table 1 for Sample 1. Is there a way to Auto create a record in tables 2, 3, 4, 5 ect for Sample 1? or do you have to do this for each table individually.

                   My tables are 1 to 1 so each table should all have the same number of records with the same samples i am just collecting different information on the sample in each table. I currently have 18 tables so it might be cumbersome to have to create 18 records for the same sample. Thanks again for all of your help

              • 4. Re: Moving fields from one table in a file to another is it possible?
                philmodjunk

                     A script can do that, but this is something that is very rarely necessary.

                     FileMaker can automatically create the related records in tables 2-5 on an "as needed" basis from the context of the parent record in table 1 if you enable "allow creation of records via this relationship" for the related tables. If there is no related record in table 2 for the current table 1 record, a field on the table 1 layout from table 2 will appear blank. But with allow creation" enabled for table 2, you can simply enter data in that empty field and FileMaker will automatically create the needed record in table 2 and link it to table 1 by copying over the sampleNumber value from the current table 1 record.

                     And if a Set Field step assigned a value to that same field from table 2, this would also create that related record if one does not already exist.

                     And to answer the precise question answered, you could set up a script that creates a new record in table 1, sets a variable to the value of the SampleNumber field (if SampleNumber auot-enters a serial number or UUID value) and then goes to a layout for each of the other tables to create a new record and use set field to copy the value from the variable into the matching sample number fields.