8 Replies Latest reply on Jul 5, 2017 10:20 AM by dianadev

    When are records committed during an import from a file?

    dianadev

      Hello,

      I'm working on a simple student database that tracks students, their siblings and parents. I need to import the data from Excel. I have a students table, a parents table, and a student_parent table that accommodates the many to many relationship. I'm starting with importing the students first, then the parents. (Also still working with RI in my model, so things are a little loose there right now.) I'm struggling with when I can push the student ID, parent ID and a couple other fields into the student_parent table. Right now I'm still in development, learning about and refining the import process, and manually linking students and parents through a portal on the Parent Maintenance layout after the imports are complete. Fortunately, this is a very small database, probably only adding about 50 students records per year, so doing this manually isn't really a problem. Would prefer something a little more elegant. Just not sure where to hang a script to accommodate this. Any feedback on this would be helpful!

       

       

      Thank you!

        • 1. Re: When are records committed during an import from a file?
          philmodjunk

          You shouldn't need both parent and family IDs in the relationship between Student parent and parent.

           

          I don't think that when the records are committed has any bearing on the problem that you are trying to resolve.

           

          Are the ID's imported from Excel or generated by FileMaker?

           

          From the question, I would assume that they are generated in FileMaker. Both Student and Parent ID's should be generated automatically as part of the import if you enable auto-enter options during import, but that won't magically put those IDs into the Student_Parent table. You'd need to match by name temporarily in order to get the IDs correctly in place. That assumes that you have name fields in Student_Parent and that those names are unique--Uniqueness shouldn't be too big of a problem given the small number of records involved, but you will need to watch out for that possible complication here.

          • 2. Re: When are records committed during an import from a file?
            dianadev

            I've been going back on forth on the value of family_ID in anything other than the Parent table, so that might go away from both student and student_parent, so thank you for that.

             

            The family_ID comes from excel, and the student, parent and student_parent ID fields are generated from FM.

            Student and student_parent have the same specs. That's why I think the family_ID field actually works in this case; sometimes spouses have different names.

            • 3. Re: When are records committed during an import from a file?
              philmodjunk

              If you have a single parent and a single student with that Family ID, then yes, you could use it to set up your Student and Parent IDs, But linking by parent ID AND Family ID in the parent to Student_Parent relationship makes no sense. And it won't work with multiple students and multiple parents.

               

              Student and student_parent have the same specs.

               

              What do you mean by "specs"? If you mean that they have exactly the same fields, then why do you need both tables?

               

              What you show is a "join" table that is normally used in a many to many relationship. It would allow you to link a student to more than one parent record and a parent to more than one student record. To me, that looks exactly what you need, but you woudn't normally just have all the same data in both tables for that, you'd normally only need the ID fields plus any that are needed to document details about that parent to student link such as noting the familial relationship (mother, father, guardian, etc).

              • 4. Re: When are records committed during an import from a file?
                dianadev

                So a clarification - family_ID isn't in that relationship. It participates in a different relationship that isn't relevant here. By specs I only meant that the student and parent ID fields in their respective tables have the same validation characteristics. Hope this helps clarify my wording.

                 

                My confusion is how to insert records into student_parent during the import rather than doing it manually. I was thinking script triggers could help with that, but I'm not familiar enough with FM to determine which field/event to use to execute a script that would insert the records into student_parent.

                • 5. Re: When are records committed during an import from a file?
                  philmodjunk

                  So a clarification - family_ID isn't in that relationship.

                   

                  Yet I can clearly see Family_ID being used in the Parent to Student_Parent relationship and that does not seem correct. 

                   

                  What you have is a "can't get there from here" situation. You need to set ID values for student and parent in the Student_Parent table and yet you need matching IDs before you can do that. It's sort of like calling the phone company on your land line to tell them that the land line isn't working. ;-)

                   

                  So you'd need to match records by data imported from your spreadsheet. Names can be used, but be aware that they aren't perfect as you can get names misspelled and you can get two people with the same name.

                   

                  So if you defined name fields in the Student_Parent table just for the purpose of setting up these records, You could import the names into the Student_Parent record along with the other data, then use relationships that match records by name in order to copy over the the needed IDs. You'd then rely on ID based relationships once you've finished importing and linking the data.

                  • 6. Re: When are records committed during an import from a file?
                    dianadev

                    Sorry, I was looking at an older hard copy version of the model, and at some point updated the relationship to include family_ID! Totally missed that until I looked at the relationships in the db file!!  I've updated the relationship between parent and student_parent to parent_ID only. Seems like I can use the family_ID for matching criteria then?

                    • 7. Re: When are records committed during an import from a file?
                      philmodjunk

                      That would depend on your data. Might you have two or more Parent records with the same Family ID? If it's always one and only one parent record per family ID, then yes, but if two are possible, no.

                      • 8. Re: When are records committed during an import from a file?
                        dianadev

                        Thank you for all your help - I've given this a lot of thought.  Realizing that due to the small number of students to relate to parents, linking them can remain a manual process. Family_ID is still helpful info, but clearly not in this particular case. Thanks again!