1 2 3 Previous Next 34 Replies Latest reply on Mar 3, 2015 2:30 PM by disabled_jackrodgers

    How do I put together all Filemaker files into one?

    thefrog

      Hello!

       

      I have a problem.

      Have a database that keeps track of the different companies' products. The database consists of many tables. Each company has a unique Serial Number. The serial number is entered manually, so it's not something that happens automatically.

       

      Now the problem

      I have a user who has done something strange. Instead of adding new records to the database, he has created a new copy of the database for each new company. This means that I have many FileMaker files.

      How do I put together all Filemaker files into one?

       

      Grateful for help

      The frog

        • 1. Re: How do I put together all Filemaker files into one?
          Mike_Mitchell

          Pick one copy of the database to keep. Import the records from all the others into that one. Then validate that your "unique" key has remained unique (because it might not be after the merge).

          • 2. Re: How do I put together all Filemaker files into one?
            Mike_Mitchell

            Actually, let me modify that. If your extra tables have relationships to the serial number, it won't be safe to do a straight import in case there ARE duplicate serial numbers. So you should do this instead:

             

            1) Create a relationship between the new "master" file and each of the other files (or, if you'd rather, do it the other way around - create a relationship from each other file back to the master). Go from serial number to serial number in the Company table.

             

            2) Perform a Find on the serial number field in the related table to locate any records that have a related record in the "other" file. This will tell you if you have any duplicates.

             

            3) If you do find duplicates, change the serial number on those duplicates in the original file (probably the "other" file) and update not only the Company record, but any child / related records to match.

             

            4) Repeat for each file.

             

            5) Now it's safe to do your import and merge the files.

             

            HTH

             

            Mike

            • 3. Re: How do I put together all Filemaker files into one?
              erolst

              thefrog wrote:

              I have a user who has done something strange. Instead of adding new records to the database, he has created a new copy of the database for each new company. This means that I have many FileMaker files.

              I won't deny that users are detrimental to a quiet and peaceful life (second in that respect only to data), and they do do strange things – but only if the developer lets them.

               

              So you should ask how a user was able to create copies of the file in the first place, and take measures to prevent that from happening in the future.

              thefrog wrote:

              Each company has a unique Serial Number. The serial number is entered manually.

              In light of the second statement, how can you enforce that the first is correct? And why don't you use the provided auto-enter options?

              • 4. Re: How do I put together all Filemaker files into one?
                thefrog

                Thanks for all the replies.

                 

                 

                Will try to explain in more detail

                 

                The reason for all the files is that the user misunderstood. Every time he would bring in a new company and their products, he started over with a new database file, which had no company listed.

                Unfortunately, because of all files and the database consists of 14 tables, this creates a problem.

                You can import each table one at a time but it will take too long.

                We're talking about 100 companies.

                 

                There must be some script that can help me merge all files. That transmit the information to the right place, in to the right table.

                 

                 

                PS
                I am a beginner on Filemaker and my english is not the best, so try to explain in simple terms, how I should go about it.

                 

                Grateful for the help I can get

                The frog

                • 5. Re: How do I put together all Filemaker files into one?
                  Mike_Mitchell

                  Using the basic process I laid out, you can write a script that will automate the process. Just go to each table in turn, import the records, and move on. You should use "matching names" on the import to make it easier.

                   

                  After that is done, do what erolst told you: Do not give end users the ability to duplicate the file. Host it on FileMaker Server, either using your own server or a hosting service.

                  • 6. Re: How do I put together all Filemaker files into one?
                    thefrog

                    I apologize if I do not understand how I should proceed.

                     

                    Unless I misunderstand you, I need to manually import each table, one at a time, for each database file. I have 100 files. Each file consists of 14 identical tables.

                    When I import, I can only bring in one table at a time, not all.

                     

                    Is there no approach that allows you to import all the tables at once, from a Filmaker file?
                    Is there no approach that allows you to import all the tables at once, from multiple FileMaker files?

                     

                    The frog

                    • 7. Re: How do I put together all Filemaker files into one?
                      Mike_Mitchell

                      The short answer is no, there is no process that allows you to import all the tables at once from a FileMaker file. An import is a single-table operation.

                       

                      If you have many of them to do, then you can write a script to automate the process. The script will perform the import many times. It will look something like this:

                       

                      Go to Layout [ {layout for table 1} ]

                      Import Records [ {filename} ; Matching field names ]

                      Go to Layout [ {layout for table 2} ]

                      Import Records [ {filename} ; Matching field names ]

                       

                      and so forth. To make it easier, you can set {filename} using a variable. You'll have to do that either manually for each run (which would be painful, or you can use a Custom Dialog and a global field, like this:

                       

                      Show Custom Dialog [ "Enter file name" ; gText ]

                      Set Variable [ $filename ; gText ]

                       

                      It's easiest if you have all the files in the same directory (folder); that way, you don't have to worry with path names.

                       

                      You'll also want to have a similar script in each of your 100 source files that goes to each layout and uses Show All Records to make sure your import captures all the records. You can import the script from file to file once you write it in one file. But make sure you run it in each file before you run the import.

                       

                      And again, make sure you don't have any duplicate keys before you run the import. If you do, you'll have a giant mess on your hands trying to sort out which child records belong to which parents after the import.

                       

                      HTH

                       

                      Mike

                      • 8. Re: How do I put together all Filemaker files into one?
                        erolst

                        Mike_Mitchell wrote:

                         

                        The short answer is no, there is no process that allows you to import all the tables at once from a FileMaker file. An import is a single-table operation.:

                         

                        Under the assumption that at least the primary keys used in the 100 new files are all different from each other, one could automate this on a larger scale by …

                         

                        • using one of the "erroneous" files as master file …

                        • write an import script that uses a specified External Data Source (path/name)

                        • write a “master” script that …

                        • calls a system level script (AppleScript, one shell or other) to copy/rename the other database files one by one to that location

                        [ launches a script in the current export file to show all records in the export/import TOs]

                        • launches the import script …

                        (alternatively, find a strategy to compare primary and foreign keys after each import, and adapt them if necessary …)

                        • starts over

                         

                        After that, all the new data is in one file, with non-duplicated primary keys, and can be compared with the data in the “master” master database.

                         

                        The big snag in this scenario is of course that none of those files does yet have a Go to Layout / Show all Records script to prepare the import/export.

                        • 9. Re: How do I put together all Filemaker files into one?

                          This is not a simple project and any answer that doesn't acknowledge that fact is a potential problem.

                           

                          First, the unique company serial numbers must be validated for the 100 files.

                           

                          Next attention must be given to the serialized records such as serial numbers.  Did each file begin with the same serial number, such as 1000 or did your user make a clone which would create two files with serial numbers beginning with 1254.

                           

                          Invoice Items are always fun in this situation since they can appear in as many as 100 invoices 100 times if you aren't careful, or lucky. This can cause various problems such as paid invoices not be paid in full and clients being rebilled for 300 or so items they did not receive.

                           

                          A very careful analysis of your file structure is needed before any import routine is suggested or attempted.

                           

                          Your files may have to be normalized for related ids, invoice numbers, etc.

                           

                          There's more involved than scripting an import of records from the 100 files and if care is not taken the final results may be totaly useless regardless of how impressive the import script is.

                          • 10. Re: How do I put together all Filemaker files into one?
                            Mike_Mitchell

                            While caution is certainly advisable, the OP already stated the serial numbers are manually added; there is no auto-enter being utilized.

                             

                            Additionally, there is no invoicing referenced. It's stated that the database is a product / company tool. Given the OP's self-characterization as a "beginner", I doubt there's a complex invoicing system involved (though I could be wrong).

                             

                            And we've already (repeatedly) cautioned the OP about verifying the serial numbers to avoid duplicates.

                             

                            If, however, the task proves too daunting, then consulting a qualified developer might be worthwhile.

                            • 11. Re: How do I put together all Filemaker files into one?
                              erolst

                              Mike_Mitchell wrote:

                               

                              […] consulting a qualified developer might be worthwhile.

                              Instead of an unqualified one whose fortes are buzzwords and generalities …

                              • 13. Re: How do I put together all Filemaker files into one?
                                erolst

                                jackrodgers wrote:

                                A very careful analysis of your file structure is needed before any import routine is suggested or attempted.

                                 

                                You mean: analyse the data model, then optimise ~100 + 1 individual files according to your findings, then write the script(s)?

                                 

                                Certainly a way to kill time, or increase your billing hours.

                                 

                                Seriously: we can make suggestions and recommend approaches in a broad sense, trying to take into account whatever the OP tells us, and point out (as best we understand the situation) potential risks – but when all is said, this is a forum where you don't have developer/client relationships, but rather discussions among colleagues with different levels of expertise.

                                 

                                From a certain point on, they have to understand how this works and do their homework (and, if necessary, keep asking questions), or bite the bullet and get professional assistance.

                                • 14. Re: How do I put together all Filemaker files into one?

                                  Of course just writing scripts without considering anything is easier...

                                   

                                  After analysing the files and understanding what is needed, the script would do more than just import records. It would create a new linking ID if needed and push that into the fields of any related records after the import of each file. Not much can be done about things such as invoice numbers already sent but the links can be updated to prevent the invoice item problem.

                                   

                                  Sometimes just providing a quick answer to a question does more harm than good.

                                  1 2 3 Previous Next