1 2 Previous Next 20 Replies Latest reply on Dec 18, 2016 7:41 AM by data578

    Moving a database to an updated version of the program

    data578

      Hi - as a very new user, I would really appreciate some guidance. I suspect the answer is going to be quite simple and that I'm just missing something obvious! But after reading the FMP 'Help' and completing a big Lynda course, it still beats me ...

       

      I have FMP13 and have built a database (version 1) that will hold 10,000 records, split across four tables, including a 'Join' table. It works very well. I have now added some functionality to the database layouts (extra fields, some label text changes) to make a version 2.

       

      Question: What is the most efficient way to import all of the records in their tables from v1 into my new (empty) v2? All of the fields in v1 are in v2 so they will all match up and I shouldn't lose anything.

       

      Thanks for your help. Tim

        • 1. Re: Moving a database to an updated version of the program
          philmodjunk

          Do you know how to use Import Records manually to move data from one table in one file into a corresponding table in a different file?

           

          Once you can do that manually, you can script it.

           

          Do you have auto-entered serial numbers for some of your fields? If so, there's a script step set next serial value, that you can use to update those settings since the next serial value in your new version is unlikely to be correct for that of the data you import. Your script can sort by serial number to find the largest value in the imported data and set the next value to something larger than that. (if you use auto entered UUID's, this extra hassle is avoided.)

           

          The more sophisticated approach is to define a global container field and use Insert file as the first part of your script to insert a reference to your original file into a container field. Your script can then extract the file path from the container field and use it in $Path variables to import data from the file. This method presents the user with the standard "open file" dialog for their computer's OS, they find and select the file, and then the script starts importing.

           

          And you might consider setting up a two file, data separation type solution where the user interface and scripts are in one file and your tables are in the other. Then, many updates can be done by swapping out the old interface file for a new one with no importing needed.

          • 2. Re: Moving a database to an updated version of the program
            data578

            Thanks Phil. Yes, I tried Importing but got an error.

             

            I saved v1 of the database, with its records in four tables. I then modified some of the layouts, added some labels and a few more fields to make a v2 which has no records at all. All of the fields in v1 are in v2. The tables are named the same and the Relationships are unchanged.

             

            When I tried to Import my v1 fmp12 file, it asked me to match the incoming fields with the receiving fields, which I did. The problem is that when I started the import, Filemaker told me it couldn't import to the same Table. Both the v1 database Table with its records and the v2 Table with no records are the same name. Do I have to make a new Table from the imported Table, or re-name the v2 empty Table? Sounds a bit daft if I do!

             

            Thanks, Tim

            • 3. Re: Moving a database to an updated version of the program
              JackRodges

              This alert tells you that you have selected the same table for import that you are using and trying to import the current active table.

               

              The simplest method of preventing this error is to use File_New.fmp12 for the new file. Then when you select the old table you won't accidentally select the new one. You can now script an import as long as you don't move the files. After the import and final testing, name the old file File_Old.fmp12 and rename the new file and use it.

               

              Creating backups before the import is also a good idea.

               

              And for future reference, you can make changes to the file you are using and not create this second file. Make a backup before making any changes.

              • 4. Re: Moving a database to an updated version of the program
                data578

                Thanks for replying. Sorry but I'm missing something fundamental here. I'm also not yet into scripting so it needs to be a manual process, for now.

                 

                I have named my updated version of the file (with its layouts slightly changed, etc.) as File_New.fmp12. This file has 4 tables and no records. How do I avoid the alert about trying to import the current active table, called 'People'? If I don't specify the current table name in the right side of the import screen, but choose 'New Table' as the Target, I do successfully import all the fields and record from my original table. But this new table (it names it 'Table 2') is not using the layout I had created for the original table.

                 

                I tried editing the original 'People' table (the one whose layout I want) field by field (altering the location of every field from the new Table 2 to the 'People' table but that seems very tedious. I have another three tables to import as well.

                 

                Is there a simple way I can apply my original 'People' layout to this new 'Table 2' to avoid going through this?

                • 5. Re: Moving a database to an updated version of the program
                  siplus

                  Is your solution a multi-user one, thought to be shared on a server ?

                  • 6. Re: Moving a database to an updated version of the program
                    data578

                    'Is your solution a multi-user one, thought to be shared on a server ?'

                     

                    No it's not. It is a single user, standalone database, running on a Windows 10 PC.

                    • 7. Re: Moving a database to an updated version of the program
                      BruceRobertson

                      You avoid the alert by correctly specifying the OLD file as the source (left side).

                      • 8. Re: Moving a database to an updated version of the program
                        BruceRobertson

                        Delete Table 2. You should not need to do ANY remapping of anything and your import process should NOT create a new table.

                        1 of 1 people found this helpful
                        • 9. Re: Moving a database to an updated version of the program
                          data578

                          Thank you Bruce. I did have the OLD version on the left side as the source (that is, version 1, with all the records in it) On the right side I have my updated program with its revised layouts and labelling but containing no records.

                          • 10. Re: Moving a database to an updated version of the program
                            philmodjunk

                            Here's the step by step process for manually importing data from an older version of the file into a newer version:

                             

                            1. Open the new version.
                            2. Select a layout based on the table into which you want to import data. "Show Records From" in layout setup will tell you what table a given layout is based on. The name shown there will match the name of a "box" in your relationship graph. It, in turn will refer to a table defined on the tables tab in the same dialog.
                            3. Select Import Records | File from the File menu
                            4. This will open a dialog for selecting the file. Use it to find and select the older file from which you want to import data. This is where giving different names to the old and new versions can help you pick the correct file.
                            5. This will open a dialog where you can select a source table from the file from which you are importing data and where you can align (map) fields to determine which field of your target table will receive data from a given field in the source table. You cannot select a target table in this dialog unless you want to create a new table with this import and you do not want to do that for this particular task. Selecting the layout from which you started this process selected the table into which your data will be imported. (In  a scripted import, on the other hand, you can select the target table without first selecting a layout.)
                            6. Since you are importing data into a new copy of your solution, chances are good that all or most of your fields have the same name. You can thus save a lot of time by selecting the "matching fields" option from the drop down below the section where you can manually drag fields to align them for import. This will automatically align all fields with identical names. If there have been a few name changes, you can then, after selecting this option drag them into correct alignment for your import.
                            7. You can now click import. This opens a small unusual dialog where you might be asked if you want to split repeating fields into separate records and whether to enable auto-enter options. For solution updates like this, you usually do not want to select either of these options though there can be exceptions. Click Ok and your import will start.
                            1 of 1 people found this helpful
                            • 11. Re: Moving a database to an updated version of the program
                              BruceRobertson

                              "Thank you Bruce. I did have the OLD version on the left side as the source (that is, version 1, with all the records in it) On the right side I have my updated program with its revised layouts and labelling but containing no records."

                              If you got the "importing into same table" error then no, that is NOT what you actually had set up.

                              • 12. Re: Moving a database to an updated version of the program
                                philmodjunk

                                There are several ways that you can end up with that error--the message is correct and accurate, but the steps to how you go there can vary:

                                 

                                When you used the open file dialog to select a file from which to import data, you selected the wrong file--presumably the same file that you currently have open. If old and new versions of the solution have identical file names, this can be an easy mistake to make.

                                 

                                If you have used an external data source reference to refer to a table in another file, you might have selected a table occurrence from your source file that refers to exactly the same data source table as that specified for the current layout's table occurrence. Thus your "table" names in the dialog can be different, but actually refer to the same exact table.

                                1 of 1 people found this helpful
                                • 13. Re: Moving a database to an updated version of the program
                                  data578

                                  My thanks again. I tried again this morning with a fresh outlook and a fresh cup of coffee ... and it works!

                                   

                                  The 'old' file (containing all the records) I named 'old1.fmp12'.

                                   

                                  My 'new' file with layout changes made but with all records deleted, I named 'new1.fmp12'

                                   

                                  I 'Imported records' and selected the file 'old1.fmp12'. Its Table called 'People' appeared in the left hand 'Source' panel, with its source fields listed below. The Target was 'Current Table' ("People") and its fields all appeared in the right hand panel below it. The Target fields were mostly mis-matched. I moved them to match up, clicked 'Import' and the records from the source Table 'People' all came in to the same-named Target table 'People'. Perfect. Not sure what I was doing wrong but you steered me there. Thanks to you and to the others on this thread who took the time to help me. I have other apps I have used for many years and know inside out, but this is my first FMP project. It's a very powerful system and I haven't even started with Scripting.

                                   

                                  While I am here ... I wanted to use an 'OR' operator between two words in the Quick Search box. (e.g: Find occurrences of lion OR tiger). But it isn't there. Have I missed it? Or is it not possible?

                                  • 14. Re: Moving a database to an updated version of the program
                                    erolst

                                    data578 wrote:

                                     

                                    While I am here ... I wanted to use an 'OR' operator between two words in the Quick Search box. (e.g: Find occurrences of lion OR tiger). But it isn't there. Have I missed it? Or is it not possible?

                                    Not as such. But you can do it manually:

                                     

                                    - enter Find Mode by pressing cmd/ctrl-F

                                    - enter 'Lion' into the appropriate field (w/o the quotes)

                                    - create a new request by pressing cmd/ctrl-N (note the info in the toolbar)

                                    - enter 'Tiger' into the same field

                                    - press Return

                                     

                                    When you start with scripting, you'll discover vastly more possibilities to configure and automate such routines.

                                    2 of 2 people found this helpful
                                    1 2 Previous Next