1 2 Previous Next 21 Replies Latest reply on May 11, 2012 1:04 AM by dbeval

    Importing data into a join table

    dbeval

      I am evaluating FM Pro and have run into some issues.

       

      I created a table that maps students and guardians, but need to figure out how to populate it automatically.

      I already imported the students and guardians, but don't want to have to spend a long time mapping them

      manually.

       

      S-t-G.jpg

      Using SQL, I can create a join table that maps students to

      parents based on a unique value (email). However, in FM Pro,

      I can't find a way to use SQL to do the same.

       

      Something like:

       

      SELECT Students_t.ID, Parents_t.ID

      FROM Students_t

      INNER JOIN Parents_t

      ON Students_t.Email = Parents_t.Email

       

      Your insights are greatly appreciated.

        • 1. Re: Importing data into a join table
          Malcolm

          I am evaluating FM Pro and have run into some issues.

           

           

          I created a table that maps students and guardians, but need to figure out how to populate it automatically.

           

          I already imported the students and guardians, but don't want to have to spend a long time mapping them

           

          Go into "Define Database" and switch to the Relatioship Graph

           

          Connect the two tables by linking the ID (primary key) from guardians with the guardian (foreign key) in Students.

           

          Malcolm

          • 2. Re: Importing data into a join table
            dbeval

            Thank you Malcolm. The way I map students to guardians is using an intermediary table as shown in the screenshot above, instead of linking students to parents directly. I created a layout for the mapping table (Students_Guardians) and can do the mapping without a problem, but I have to do it manually, which is tedious and time-consuming. I am trying to see if there is a way to automate the process. If FileMaker Pro allowed to run the SQL statments mentioned above, the problem would be solved, but for whatever reason, I can only use SQL against an external ODBC source.

            • 3. Re: Importing data into a join table
              Malcolm

              Presuming that the relationship between students_t and students_guardians allows creation of new records.

              Presuming that the relationship is based on the student.id

               

              Create a temporary TO by duplicating parents_t (parents_t copy)

              link it to students_t by the Email field

              From the temporary parents table

              show all records

              go to related records in students_t  show only related based on entire set of records

              replace field students_guardians::Guardian_id with parents_t copy::ID

              clean up

              done.

              • 4. Re: Importing data into a join table
                dbeval

                That's a clever way of solving the problem. Thanks.

                 

                I am trying to follow you're instructions, but in the process, this is what I did.

                 

                In the relationships grph, I created a duplicate of the parent table and linked its Email field to the Student_t Email field.

                Then, I created a layout based on the Students_t table and added a portal listing the realted fields in Parent_t. It workd.

                I can now list the parents for every student.

                 

                Will continue working on it and report back tomorrow. Many thanks.

                • 5. Re: Importing data into a join table
                  rhlilienkamp

                  FMP12 has an Execute SQL which allows you to use SQL.  Prior to 12 you need a plug-in, a free one from Dracoventions callSQL Runner is excellent.

                  • 6. Re: Importing data into a join table
                    gsokolsky

                    You could use a common field in both the parents and students tables such as "Family ID" that links the parents to the students, then relate the parents and students tables via this field.  Then there would be no need for the join table.

                    • 7. Re: Importing data into a join table
                      dbeval

                      Thanks for the suggestion. I have tried that, but I have to specify an ODBC source.

                      It seems that it is designed to interact with external databses.

                       

                      I tried adding an FM DB as an external data source, but haven't been able to get it to work.

                      Execute SQL [No Dialog; ODBC: <datasource name>; <native SQL
                      or calculated SQL>]

                      • 8. Re: Importing data into a join table
                        dbeval

                        Good idea. Malcolm suggested something similar and I used the Email field to match records. I could have simplified life and used two IDs in the Students_t table, one for the father and the other for the mother, but I wanted to link students to guardians through a mapping table. A studengt may have one guardian or more than two guardians. The guardian may be a grandfather or an aunt. The mapping table does the job for me and it works. My only issue is finding a way to automatically populate the table with the information that I already have. I am trying to avoid having to do the mapping manually. Malcolm has provided a clever solution, and I am almost there. Will report back tonight.

                        • 9. Re: Importing data into a join table
                          rhlilienkamp

                          The Execute SQL script step is for external data sources.   The ExexuteSQL function in FMP 12 is for internal data as are the plug=ins that I suggested for per 12.

                          • 10. Re: Importing data into a join table
                            dbeval

                            Thank you for the clarification. When I searched FM online help, I didn't see the reference to ExecuteSQL.

                            Here's a screen shot of what appears in the Help search results:

                            SQL.jpg

                            Didn't notice the Show All link (bottom right), but the ExecuteSQL is mentioned almost at the end of the long list of serach results!

                             

                            Anyway, I created a calc field and was able to run simple SQL statements like these:

                             

                            ExecuteSQL ( "SELECT * FROM Students_t"  ;

                            "," ;

                            ";")

                             

                            ExecuteSQL ( "SELECT Parents_t.ID FROM Parents_t"  ;

                            "," ;

                            ";")

                             

                            ExecuteSQL ( "SELECT Students_t.Email FROM Students_t" ;

                            "," ;

                            ";")

                             

                             

                            However, when I try to do an inner join it doesn't work. I'll try to debug in case it's my error

                             

                            ExecuteSQL ( "SELECT Students_t.ID, Parents_t.ID

                                                        FROM Students_t S INNER JOIN Parents_t P

                                                               ON S.Email = P.Email"  ;

                            "," ;

                            ";")

                             

                            I tried other statements and it didn't work. Ijust can't understand what I am doing wrong:

                             

                            ExecuteSQL ( "SELECT Parents_t.Last FROM Parents_t" ;

                            "," ;

                            ";")

                             

                            ExecuteSQL ( "SELECT Students_t.First FROM Students_t" ;

                            "," ;

                            ";")

                             

                            Will keep trying. I want to rule out user error.

                            • 11. Re: Importing data into a join table
                              dbeval

                              I am sure I was able to follow your instructions correctly, but I got to a point where I can list in a layout where I had some type of a join, but there were no records with duplicate parent IDs (a parent can have more than one child). So I am not quite there. I think I should map records manually.

                              • 12. Re: Importing data into a join table
                                Malcolm

                                What about using a script trigger to generate a record in the join table. It could be attached to the Email field and be run onExitField. This example presumes that you're working in the parents table but you can use the same idea in the students table.

                                 

                                1. Allow Students_t and Parents_t to be able to create new records across the relationship with Students_Guardians.

                                2. Create a script along these lines:

                                 

                                Set Variable [$myID ; Parents_t::ID]

                                Set Variable [ $studentIDs ; ExecuteSQL ( "SELECT Students_t.ID FROM Students_t WHERE Students_t.Email = emailField"  ;   "," ;   ";") ]

                                Set Variable [ $currentJoin ; List (Students_Guardians::student_id) ]

                                Set Variable $n = valueCount ($studentIDs)

                                loop -- loop through $studentIDs

                                     exit loop if [ let ($i = $i + 1 ;  $i > $n)]

                                     if[ not valueCount(filterValues($currentJoin ; getvalue($studentIDs ; $i) ) ]

                                          go to layout [Students_Guardians]

                                          create new record

                                          set field[student_id ; getvalue($studentIDs ; $i)]

                                          set field[parent_id ; $myID

                                          go to layout [original layout]

                                ]

                                 

                                        

                                 

                                 

                                3. attach it to the email field and trigger it on field exit

                                • 13. Re: Importing data into a join table
                                  dbeval

                                  Another clever idea. Thank you. Will give it a try at some point if I can't get the ExecuteSQL to work. What's bothering me is that this is such a simple problem to solve, but in FM, it's taking forever. Will everyone posted.

                                  • 14. Re: Importing data into a join table
                                    Malcolm

                                    My instructions only work for the first instance in the set of related records. : (

                                     

                                    To generate data for multiple related records you'll need to (a) loop through all records in the set then (b) loop through the list of records related to the current record (c) creating a record in the join table for each one.

                                     

                                    The script (or a tested and functional version of it) that I suggested a moment ago could do that. You just need to wrap it in a loop to work its way through the entire record set.

                                    1 2 Previous Next