8 Replies Latest reply on May 8, 2017 4:56 AM by beverly

    I'm missing something??


      Thanks to those responding to my previous question.


      New problem:  I've obviously missed something as I get started in my FM adventure working with relationships.


      Want to create a new FM Pro file, i.e. no fields set up.

      Import the following from Excel (example simplified but essentially what I am doing):


      Header 1Header 2Header 3Header 4
      Company1New YorkPlantAMaine
      Company1New YorkPlantBNew Hampshire
      Company3OhioPlantINorth Dakota


      The first row (below what tables shows as Headers) are the column headers in Excel and will be field names in FM.

      How do I set up two tables:  One for Companies (with just 3 records) and one for Plants (with 9 records)?

      How do I relate them so I can have a layout that shows a company with its plants listed below AND a layout that shows company name when I select a particular plant.


      I've tried several things, read and watched a lot and still can't get this to work.


      Any help would be appreciated.

        • 1. Re: I'm missing something??

          You can import the same file more than once, specifying a different target table and selecting different columns to map to the fields of those different tables.


          If you set up the field definitions for your fields before you import (rather than doing an Import Records with the new table option), You can specify Unique Value, validate always for the company name.


          You can then import this data, and only map the fields that you want for that table.


          The validation rule will filter out the duplicates and produce your three records.


          The same method can be used to import into your Plants table.


          Ideally, you want to link plants to companies by a company ID that is auto-entered in the company table. But your only way to link this data immediately after import is by name. So I would import the company name into a field of the same in Plants and use it temporarily to link by name to the company table. A field in your company table can auto-enter a serial number or Get ( UUID ) if you enable auto-enter options during import. A field in Plants can auto-enter this value from the related (by name) company table if you enable the same option when importing into Plants.



          Define tables, fields and relationships first.

          Import into Company

          Import into Plants


          And you should have achieved what you want.

          • 2. Re: I'm missing something??

            I would import "as is" into a temporary table and go from there.

            • 3. Re: I'm missing something??

              +1 on Beverly's suggestion... that's what I do. 

              • 4. Re: I'm missing something??

                sblake, you may want to create your base tables fresh in Filemaker and use it as a first point of entry. In your example you highlighted a set of a flat data, a query style view that Microsoft products use alot. In Filemaker you will have several opinions for your related data sets, you could use (dropdown) lists for simple things like state codes. Using a combination of tables gives the greatest flexibility for reporting and data keeping. I suggest doing a Filemaker Essentials course on Lynda and you'll have all questions answered in less than 12 hours.

                • 5. Re: I'm missing something??

                  Definitely import into a scratch table, then ExecuteSQL ("Select Distinct CompanyName ....") and create a record for each one, or import, sort by companyName, export to Temporary folder summarized by company and reimport.

                  • 6. Re: I'm missing something??

                    There really is no need for ExportSQL nor export/import again. With the method I described in my first reply, you just import the data and let field validation filter out the duplicate values.

                    • 7. Re: I'm missing something??

                      philmodjunk wrote:


                      There really is no need for ExportSQL nor export/import again. With the method I described in my first reply, you just import the data and let field validation filter out the duplicate values.


                      ExportSQL is something I wish we had.

                      • 8. Re: I'm missing something??

                        There really is no need...

                        it can still be a good idea to import into a temp table, regardless of what you do from there.