1 Reply Latest reply on Jan 21, 2011 2:58 PM by philmodjunk

    Help on scripting for importing a flat file into a simple relational database

    ThomasPaterson

      Title

      Help on scripting for importing a flat file into a simple relational database

      Post

      Hi... I'm a new FileMaker user finding my way here and I'd greatly appreciate some help on what (I hope) to be a simple scripting problem.  See the attached JPG.  In this example, I have an Excel table listing products made by different companies for different markets.  I want to import this into a relational database, also shown in the image.  I can import the file into the "Product" table easily enough (additional fields would be hand-entered later), but I want to create records for the "Company" and "Market" tables in the process.  Specifically, I want to create a new record in the "Company" table only if that company doesn't already have a record... and regardless, I want to link from the "Product" table to the "Company" table after the import.  Likewise for the "Market" table.

      So, for this example, following importing the file into the "Product" table, I should end up with:

      - 4 records in the "Product" table as in the file

      - 2 records in the "Company" table (ACME, WidgetCo)

      - 3 records in the "Market" table (automotive, aerospace, marine)

      - All fields in all records not specified in the import file would, of course, be left blank

      If someone could teach me the basics here, I would really appreciate the help.... Thanks in advance!

      example.jpg

        • 1. Re: Help on scripting for importing a flat file into a simple relational database
          philmodjunk

          You can only import into one table at a time, so you are looking at three specific imports from the same file to import your data.

          Before you do, open field options in Manage | Database | Fields for Company::CompanyName and Market::MarketName. On the validation tab select Unique values and validate always. Now, when you import into the company and market tables, records with duplicate company and market names will not be created in these tble.

          Now for some unsolicited advice.

          Using name fields as primary keys as you have with Company::CompanyName and market::MarketName is not the best design approach here. It's a useful way to get your data imported and linked, but names are not unique, subject to change and are vulnerable to data entry errors where correcting them after the fact can "break" the links to other related records.

          I suggest you define a CompanyID and possibly a MarketID field and create a new set of relationships linking by those fields instead of names to use with your layouts. Use the above relationships for importing your data and then use Replace Field Contents to update related records with the matching ID numbers after import--using the ID based relationships.

          You can keep both sets of relationships in place by creating additional table occurrences. If that's a new concept, read this link to learn more:  

          Tutorial: What are Table Occurrences?