5 Replies Latest reply on Mar 7, 2016 5:57 AM by jb301

    How to import into a "linked" ID field?

    jb301

      Hello,

       

      I'm somewhat new to FileMaker so please bear with me. I'm more familiar with SQL relational databases so the differences in relationships within FileMaker confuse me.

       

      I have a database with a table of students with a unique, auto incremented ID. I have a file of 125 students with information that needs to be imported into the students table and other related tables. This file has the unique ID that is used for the matching in the relationships with students across the database. I've already imported the relevant data into the students table successfully. I'm now looking to import their degree information (separate table with a link on the student ID) from that same file.

       

      From what I've read about imports in FileMaker, there is no way to automatically create a related record based on the ID, even with a portal set up on the student record. It seems I essentially need to import the file again into the degrees table, matching the fields that I want (meaning the columns that are relevant to the degree information). I've done this, but because the student ID field of the degree table is simply "pointing" to the student table (a foreign key, no really sure what this is called in FileMaker), I cannot import into this field. It technically belongs to the student table.

       

      How do I fill in the student ID to establish the link/relationship between the new student and degree records?

       

      Thank you in advance!

        • 1. Re: How to import into a "linked" ID field?
          MaxEh

          I think you need a StudentID foreign key in the Degree table. Each degree will also need to have its own id. You can use field options to set auto enter serial numbers unless you want to take another approach. Best practice suggests using the Get (UUID) for setting keys. Then you create a relationship where the StudentID in your Student table equals the StudentID foreign key field in the Degree table. This is where you build your relationships in FMP you can have multiple Table Occurrences (TO) to help build your solution.

           

          You will need to import your degree records as you say by matching but be sure to add the Student ID to the StudentID_fk to make the relationship.

           

          When you are in the Student table and have a portal to Degree you can automatically create related Degree records as indicated by the check box in the attachment. You would need to create your other table relationships in the same way.  Have a look at some of the FMP basic training resources available here: Quick Start Training: Learn

           

          Given your background you should be able to move through it fairly quickly.

           

          HTH

           

          Screen Shot 1.jpg

          • 2. Re: How to import into a "linked" ID field?
            Mike_Mitchell

            jb301 wrote:

             

            From what I've read about imports in FileMaker, there is no way to automatically create a related record based on the ID, even with a portal set up on the student record.

             

            Not true. You can script the insertion of new records through a portal. Obviously, you would need the foreign key to be present in the child table and available for FileMaker to edit (i.e., must be indexable and not a calculation or summary).

             

            But if you want to use imports, you can loop over the parent records and then use Replace Field Contents to establish the foreign keys.

            • 3. Re: How to import into a "linked" ID field?
              keywords

              I assume from your description that you are importing from a flat file into a multi-table file. In that case, each record in the flat file contains all the fields concerning a student, and you need to perform a series of imports to split the data between tables. So:

              1.     First import:  data to the student table, presumably personal data (name and so forth). Make sure this table has a studentID field, which will be your unique student identifier for relationship purposes. Import all the relevant data to their matching fields and the existing ID into the studentID field—but don't set this field to auto-enter until importing is completed.

              2.     Second import:  data to the degrees table. Make sure each record in this table has its own uniqueID, set to auto-enter, but also a studentID_FK field which you will use as a foreign key to match each degree record to a student. Import all the specific degree data into the matching fields AND ALSO the existing ID which you will import into the studentID_FK field.

              3.     Repeat step 2 for any further tables.

              Once you have completed this, any relationships you have between StudentTable::studentID and DegreeTable::studentID_FK (and others) will work. If you then intend to use the studentID field as an on-going unique identifier, you will need to set it to auto-enter from then on.

              • 4. Re: How to import into a "linked" ID field?

                I do a similar set of imports for applications to a university program. keywords above has it right.  If you have problems getting it to work jb301 I can provide specific examples of tables and how I import into them sequentially.

                • 5. Re: How to import into a "linked" ID field?
                  jb301

                  Hi guys,

                   

                  Thanks for all of the responses. You all seem to be right, and attempting to import a second time it DID fill in the student ID field when I wanted it to. I'm thinking that the person who was attempting to show me did something wrong. I definitely need to make a point of reading through the basic relationships in FileMaker.

                   

                  Thanks again!