8 Replies Latest reply on Jul 30, 2014 8:24 AM by philmodjunk

    Merging/Importing Records from Multiple copies of the same database

    NickLawrence

      Title

      Merging/Importing Records from Multiple copies of the same database

      Post

           Hi all,

            

           I am still in the design/setting up process of FMPro 12, not got it installed on our server and as such gave standalone copies of it to various colleagues, trouble is I now have three copies of the same database with relationships, portals etc., I've tried doing a straightforward import but a lot of the fields are left blank, mainly the ones in portals or as part of a relationship, in fact only fields specific to that layout gets imported nothing else. Am I doing the import wrong? how do I get the three files consolidated in to one with all the correct information in the correct place?

            

           Many thanks for any help, it is greatly appreciated.

            

           Regards

            

           Nick

        • 1. Re: Merging/Importing Records from Multiple copies of the same database
          philmodjunk
               

                    only fields specific to that layout gets imported nothing else.

               That's not quite the case. Only fields that are defined in the layout's table--whether they are present on the current layout or not, get data from the import.

               You need to specifically import records from each table in the source file into corresponding tables of your target file. For manual imports, this means that you need one layout based on each table defined in your database. You then go to each of these layouts in turn and use Import Records to import the records from your source file.

               But there could be a major problem when you do so if you have not designed your database ahead of time to handle this issue.

               If you are using auto-entered serial numbers as the primary keys in your relationships, your file can end up with multiple parent records with the same serial number in the primary key field and they then match to not just the child records that orginally came from the same source file, but also to the child records from other source files that happen to have the same ID number.

               This will not be an issue if you set up these fields to auto-enter Get ( UUID ) or if you set up the auto-entered serial number settings on each copy of this database to enter values that will be unique to each copy of the file.

          • 2. Re: Merging/Importing Records from Multiple copies of the same database
            NickLawrence

                 Hi Phil,

                  

                 Many thanks for taking the time in replying, I don't fully understand the answer but that's neither here nor there. Unfortunately this project was supposed to save us time and money but definitely is not doing either of those. I'm not overly happy that I can't simply import data from two databases that are exactly the same regardless of the way the databases are setup, this is something that the software developers should have anticipated. I've had two people inputting data in to the different databases for several weeks now and from the bit's I have got from your answer best case scenario is I have to spend considerable time tweaking everything to fudge it and worse case scenario, all the work done to date is useless. I think this is the death knell for the FMPro project. Apologies for ranting but something that I would consider to be a simple task has just killed everything.

                  

                 Again, many thanks for your time and help,

                  

                 Kind regards

                  

                 Nick

            • 3. Re: Merging/Importing Records from Multiple copies of the same database
              philmodjunk

                   They are simple tasks, and correcting the issues that I mentioned are not all that terribly difficult nor do they take a lot of time to correct. But it was one that would have been very easily avoided had you understood how relational databases function and taken appropriate steps in your initial design of your database.

              • 4. Re: Merging/Importing Records from Multiple copies of the same database
                NickLawrence

                     Thanks again Phil for your time and reply, unfortunately I'm a bit bogged down at this precise moment to have a look at getting it to work, so will have to revisit it at a later date.

                      

                     I will be back :)

                      

                     Many thanks

                      

                     Nick

                • 5. Re: Merging/Importing Records from Multiple copies of the same database
                  NickLawrence

                        

                       Hi Phil,

                        

                       Just revisiting this as I have more time now to try and put it to bed and correct any screw ups I may have made previously J

                        

                       Thought I would start with trying to give a bit of background on the layout and design of the database:

                        

                       I currently have 12 tables included in the database they are for purposes of this forum as below:

                        

                        

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                                      

                  Table

                                 
                                      

                  Database One

                                 
                                      

                  Database Two

                                 
                                      

                  Auto Enter

                                 
                                      

                                           Database

                                 
                                      

                                           1field, 1 records

                                 
                                      

                                           1 field, 1 records

                                 
                                      

                                            

                                 
                                      

                                           Consultants

                                 
                                      

                                           5 fields, 31 records

                                 
                                      

                                           5 field, 30 records

                                 
                                      

                                            

                                 
                                      

                                           Deliverables

                                 
                                      

                                           2 fields, 27 records

                                 
                                      

                                           2 field, 14 records

                                 
                                      

                                            

                                 
                                      

                                           Roles

                                 
                                      

                                           6 field, 12 records

                                 
                                      

                                           6 field, 12 records

                                 
                                      

                                            

                                 
                                      

                                           Purchase Orders

                                 
                                      

                                           35 field, 508 records

                                 
                                      

                                           35 field, 35 records

                                 
                                      

                                           PO Number: Indexed, Auto Enter Serial

                                 
                                      

                                           Products

                                 
                                      

                                           2 field, 11 records

                                 
                                      

                                           3 field, 12 records

                                 
                                      

                                            

                                 
                                      

                                           PO Details

                                 
                                      

                                           17 field, 1174 records

                                 
                                      

                                           17 field, 108 records

                                 
                                      

                                           PO Number: Indexed, Auto-enter Calculation replaces existing value.

                                 
                                      

                                           Companies

                                 
                                      

                                           2 field, 2 records

                                 
                                      

                                           2 field, 2 records

                                 
                                      

                                            

                                 
                                      

                                           Expenses

                                 
                                      

                                           63 field, 139 records

                                 
                                      

                                           63 field, 6 records

                                 
                                      

                                            

                                 
                                      

                                           Contract Numbers

                                 
                                      

                                           7 field, 115 records

                                 
                                      

                                           7 field, 136 records

                                 
                                      

                                            

                                 
                                      

                                           Expenses to Invoice

                                 
                                      

                                           5 field, 3 records

                                 
                                      

                                           5 field, 3 records

                                 
                                      

                                            

                                 
                                      

                                           Project Sheets

                                 
                                      

                                           35 field, 2 records

                                 
                                      

                                           35 field, 2 records

                                 
                                      

                                            

                                 

                        

                       I do have a few fields looking values up from other databases too.

                        

                       The problem I’m having is that the Purchase orders has data in it from the PO Detail database, but when I import they are not linked so most of the data is missing, this is what Im looking to correct.

                        

                       Any help/guidance greatly appreciated.

                        

                       Many thanks

                        

                        

                        

                       Nick

                  • 6. Re: Merging/Importing Records from Multiple copies of the same database
                    philmodjunk

                         The problem I’m having is that the Purchase orders has data in it from the PO Detail database,

                         Can you describe the "PO Detail database"? How are records in (what I assume) that table linked to records inthe Purchase Orders table?

                         That values of the match fields should import along with the rest of the data.

                    • 7. Re: Merging/Importing Records from Multiple copies of the same database
                      NickLawrence

                           Hi Phil,

                            

                           I think I may have found what the problem is, there is a check box that is linked to auto enter fields and it was checked, taking this out seems to allow all the correct data to be imported. But for information, the Purchase Orders and PO Details are linked by the PO Number, the Purchase Orders have two containers that get when selected  create new records in the PO Details database. Hope this is what you meant?

                            

                           Many thanks

                            

                           Nick

                      • 8. Re: Merging/Importing Records from Multiple copies of the same database
                        philmodjunk

                             That would explain why you were losing the link between the records in the two tables.