6 Replies Latest reply on Feb 15, 2014 5:46 PM by kentanderson

    Importing data from old database



      Importing data from old database


           In my old Access Database We only had one table.  So each record contained all of the client data Name address etc. My new better FM database has a client table and a jobs table.  My jobs table contains all of the same fields as my old with one exception.  My new data base does not store all to the repeating client data, instead I just have a Client ID field.  

           How can I import my old data, and somehow link the old data jobs to the new client Id in my new database?

           I typically copy my old database from Access and paste it into a excel spreadsheet.  FYI

           Thank You

        • 1. Re: Importing data from old database

               Open the excel spreedsheet in Filemaker. Importing from a FMP database is much easier.

               Two imports will be necessary (working with test copies to get it right before actually importing.

               There has to be a field in the destination for each field in the source.

          Multiple Table Import

          Importing Data into an Existing FileMaker Pro File | FileMaker

          About creating a new table for imported data

          Setting up recurring imports in FileMaker Pro | FileMaker

          Exchanging data with FileMaker Pro | FileMaker

          Importing a folder of files all at once

          Setting the import action and mapping fields during import

          • 2. Re: Importing data from old database

                 Im not sure if I'm following you.  I know how to import records, my problem is I need all my old data to link up with the current Client Id.  Thank You

            • 3. Re: Importing data from old database

                   You need a field in both tables that uniquely identifies each client, but only using data from your original file. This may be simple or very hard to set up as it depends on the consistency of the data in your old file. You might match by name fields, for example, but then two different clients can have the same exact name. They can even have the same name and address....

                   But if you can set up such a field, you can import the data twice, once into each table. Use a unique values validation on this "temporary match" field in the client table to automatically filter out duplicates. The Client table should have a unique ID field--ideally an auto-entered serial number. Once the data is imported, you use a temporary relationship based on this "temporary match field" to copy over the ID from clients to matching records in projects. Once that's done you can remove the temporary relationship and set up an ID field based relationship.

              • 4. Re: Importing data from old database

                     This may be the best method for what i am trying to accomplish.  But once again im not able to follow.  not your fault its mine.  Can you please walk me through this one.  I have another post as well that is trying to accomplish the same thing, just in a different manor.

                • 5. Re: Importing data from old database

                       You'll need to examine your data carefully to see if the following will work:

                       For simplicity, let's assume that you have a FullName field that uniquely identifies each client. (You might actually need to combine several fields such as first name, last name and an address or phone number.)

                       You can use a Query in Access to produce such a value before exporting the data or you can import all your data into a FileMaker table where a calculation field combines the data to create the identifier. Any combination of the above might work as long as you get a field/column where the text in it identifies each client.

                       Once you have that, you can start importing data.

                       Client Data:

                       Include a text field for the above described identifier field. Set a field validation on this field: Unique Values, Validate always. When you import into this table, the duplicates will be omitted. When mapping fields specify the identifier field plus any fields that hold client data. Do not map any fields to project data. Also define an auto-entered serial number field and when you import, be sure to select the check box that enables auto-enter operations.

                       Projects Data:

                       Import from the same source again, but map only to projects fields and include that same identifier field. Do not set up any validations on the identifer field in this table. (Another serial number field to identify each project would not be a bad idea.) And finally, add a number field to serve as the client ID field for linking to the Clients table.

                       Now define a relationship matching the identifier field in clients to the identifier field in projects. After all data has been imported, go to the Projects layout, click in the blank Projects::ClientID field and select Replace Field contents. Select the calculation option and set it up to refer to the clientID field in Clients.

                       Now that you have the ID number copied over, you can remove the original relationship that matches records by the calculated identifier and replace it with one that matches by ClientID.

                  • 6. Re: Importing data from old database

                         Your Sir are brilliant.  I have spent 20Hrs watching videos on lydia.com and you have solved this problem like it was no big deal.  Thank You So So So Much.  I owe you big time.  

                         I wish by brain worked like yours