3 Replies Latest reply on Aug 29, 2011 10:35 AM by philmodjunk

    Help with Changing Flat File to Relational

    KenClose

      Title

      Help with Changing Flat File to Relational

      Post

      Hello: I began with the Medical Records starter template, imported a bunch of records (from Excel), even customized some layouts.  Now I would like to begin conversion to a Relational File.  So for first example, I have provider information in most all records in flat file.  I would like to create a Provider Table and relate the provider entries to the records in the flatfile, obviously deleting the duplicate fields from the main (flat) table.  I have searched and tried to follow the various messages on importing data back into a file from the same file, even into a newly defined table.  The problem I can not get around is on the Mapping dialog box, the Source side contains my newly named table (which is empty) but the Target side only has the original Flat File definitions.  I am stuck.  Can someone give me some steps to get me past this, or suggest a different approach?

      Thanks,

      Ken

      PS: Newbie using FP11Adv.

        • 1. Re: Help with Changing Flat File to Relational
          philmodjunk

          The Source table is the table from which you will import. The Target Table is the table into which you will import.

          If you want to import into a newly created table, first select a layout based on that table. One such layout was automatically created for you when you defined the new table. From this layout, the new table will be listed as the target table and you can then select a source table for it.

          • 2. Re: Help with Changing Flat File to Relational
            KenClose

            PMJ: thanks for the simple, accurate direction....I just missed that detail in the various turtorials I have read and tried. 

            But it does not produce the result I want or need.  It is not obvious on how to convert from a flat file, in which certain entities are repeated in all records (such as Medical Provider being visited numerous times), to a relational table in which each provider has its own record which is then related to the existing records.  I have searched (and searched again) for steps to move data from flat file format to relational table format without having to manually reenter everything.  I can not find it, and I am too new to intuitively know what to do.  I have even (ahem) searched Access articles looking for this problem to be discussed but have not found it.

            I was able to import into the newly defined Provider table I made (as per your suggestion to use the new Provider table layout).  However, I got all the duplicates from the original flat file.  Example: flat file has say 76 records, in which there are about 40 unique providers. My new Provider table now has 76 records.   I would like to a) wind up with a Provider Table with 40 unique records; b) distribute 40 unique primary key numbers to those provider records, and c) add the appropriate foreign key to each record in the flat file table corresponding to the provider for that record.

            Seems so simple but I am unable to get there and have resorted to manually entering data...yuk...seemingly contradictory to all the advice I read about use the data already in the records.  It almost seems like I need to do all my manipulations in Excel tables (of which I am very familiar) and then import all that back into FMP.

            Do you (or anyone) know of any instructions or guidance anywhere for doing what I want?

            Thanks,

            Ken

            • 3. Re: Help with Changing Flat File to Relational
              philmodjunk

              You can set up a validation rule on a field so that duplicate values in this field filters out additional records with this value during import.

              Define a serial ID field to identify your providers. On the provider name field, use validation tab in field options for this field to specify Unique Values and Validate always.

              Now, when importing, enable the auto-entries check box that pops up just before you start the import. Your new records will be automatically assigned serial numbers for unique IDs and the validation rule on the name field will filter out duplicate names. Just be aware that you can still get duplicate provider records if the provider name is spelled exactly the same in every case--even an added space will keep this method from working with 100% success.