5 Replies Latest reply on Aug 24, 2011 1:51 PM by SebastianConti

    Importing data from an old flat database

    SebastianConti

      Title

      Importing data from an old flat database

      Post

      I have a file created a long time ago when Filemaker was a flat database. I'm looking for an efficient way to import the data into a current relational database. The flat database with about 12,000 records has customers identified with first name, last name, date of first encounter and a unique ID number consisting or 4 digit number and a letter.  

      For example:
      First Record (encounter)
      Date         8/15/1992
      Name:    John Doe
      ID        6754 a

      Second Record (encounter)
      Date         4/11/1993
      Name:    John Doe
      ID        6754 b

      Third Record (encounter)
      Date         12/11/1995
      Name:    John Doe
      ID        6754 c

      And so on ...

      Some customers have only one record, others as many as 30 or more. Each customer record also contains a several text, date, graphic and number fields unique to that customer encounter.

      Is there an easy was to import this information into a new file so that there is one record for each customer related to another file with the different encounters? Hope this is clear.

        • 1. Re: Importing data from an old flat database
          philmodjunk

          Assuming that the ID field is 100% consistent in format, Import the data first into your encounters table. Before doing so, add an extra text field to recieve the above ID field plus a second field defined with an auto-entered calculation like this:

          Leftwords ( OriginalID ; 1 )

          This field will strip off the a, b etc. so that you just have what should be a unique ID for each customer. Import the records into this table and enable the auto-enter option so that this calculation will produce the desired unique ID.

          Now switch to a layout for the customer table. Open manage | database | Fields and specify unique values, validate always for the ID field in this table.

          Use Import records to import this data from your encounters table and import the contents of the calculation field into your ID field. The validation rule will block repeated imports of the same ID and this will filter your values down to one entry per record in this table.

          You can now define a relationship between the ID field in customers with the ID field where we added the auto-enter calculation in Encounters. After you are certain that this all worked, you can remove the auto-enter calculation and update the ID field in customer to be a serial number field with a next serial value setting larger than the largest imported ID number.

          • 2. Re: Importing data from an old flat database
            SebastianConti

            Thank you so much. I willl try it and let your know.

            sc

            • 3. Re: Importing data from an old flat database
              SebastianConti

              I used a variation of your solution and it worked. Thank you. Another question, is there a way to search for records that have empty container fields only?

              • 4. Re: Importing data from an old flat database
                philmodjunk

                Define a calculation field that references the contents of the container field.

                It might just be the expression: ContainerFieldName with text selected as the result type. Then search for records where this calculation field is empty by specifying a lone = operator in the field.

                • 5. Re: Importing data from an old flat database
                  SebastianConti

                  After posting my container question, I searched in the forum and found this solution:

                   

                  Case ( IsEmpty ( Graphic) ; 0 ; 1 )

                   

                  Your's woks too.  Thank you