12 Replies Latest reply on Apr 19, 2010 6:34 AM by jhed

    4 Tables from 1?



      4 Tables from 1?


      I hope I can express this clearly, as this is an issue which has begun to hurt my head.


      I am using FileMaker Pro 10.0v3 on a MacBook Pro, Mac OS X 10.4.11


      I am creating a database of alumni records, and I need to set it up so that I can import records from our current student database.


      In the current student database, student records can be linked to up to 4 different families, stored in the tables FAM1, FAM2, FAM3, FAM4.


      It seems as though these tables are populated from one table: Families.


      Each Family record contains a FamilyID.  Each student record contains a StudentID, FAM1ID, FAM2ID, FAM3ID, and FAM4ID.


      I've managed to set up a neat little import script that will import all family records into my database's Family1 table and link them to the imported student records in the Alumni table.


      The script looks like this:


      Freeze Window

      Set Error Capture [On]

      Go to Layout ["Alumni" (Alumni)]

      Import Records [No dialog; "CurrentDatabase"; Add; Mac Roman]

      Go to Layout ["Family1" (Family1)]

      Import Records [No dialog; "CurrentDatabase"; Add; Mac Roman]

      Go to Layout ["Alumni" (Alumni)]

      Perform Script ["LinkFam1Imports"]


      The script triggered by this script looks like this:



           Set Field [join.Families::fk.AlumID; Alumni::AlumniID]

           Set Field [join.Families::fk.FamilyID; Alumni::Family1ID]

           Go to Record/Request/Page [Next; Exit after last]

      End Loop


      Now, it seems to me that I would want to expand the latter script to include similar steps to place in the join table the IDs for Fam2, 3 & 4.


      This is where my internal logic gets loopy (no pun intended).


      Ideally, I would be able to grab the records from the existing database, and populate the 4 separate family tables, so that when I'm in the Alumni layout, I can view the related fields in Tab Controls labeled Fam1, Fam2, etc.


      However, the imported Family records have no indication of which family they belong to; that information seems to be stored only in the student record.


      I've managed to resolve most of my issues with this databse setup so far (with a lot of help from the forum... thanks again, all!), but once again, I'm stymied.

        • 1. Re: 4 Tables from 1?

          Seems very limiting. Why not have 2 tables? 1 student, 1 family. Student_ID and Family_ID are unique.

          • 2. Re: 4 Tables from 1?

            If I only use two tables, how will I display records from multiple families in one alumni record/layout?


            The way my Alumni Layout is currently set up is that I have several tab controls:


            Alumni | Family 1 | Family 2 | Family 3 | Family 4 | Other


            Each of the family tabs looks much the same, set up in the following manner (simplified):


            Family 1








            The tab control for Family 2 would then look like this:








            How will the database know to display the proper family records in the proper place unless they are on separate tables?


            I'm still a relative novice, so please forgive me if I'm being unclear or obtuse. :)

            • 3. Re: 4 Tables from 1?

              Instead of separate tables, use one table and place a filtered portal on each tab that filters out all records that aren't part of the specified family.


              You might even dispense with the tabs and just place a global field with a drop down or popup that selects the desired family.

              • 4. Re: 4 Tables from 1?

                Unfortunately, I'm such a beginner-  I'm not sure I know how to do either of those things.  I've tried to figure it out to the best of my ability, but I'm still struggling.


                Also, I'm trying to figure out the whole global field thing and how/why they are used at all.

                • 5. Re: 4 Tables from 1?

                  Here's a filtered portal demo you can download and study: http://www.4shared.com/file/ecruPBO2/FilteredPortal.html

                  "Type" is a global variable notice how you can select a value in it and it stays the same as you flip from record to record.


                  Note how selecting a different type in the pop up pulls up a different group of records in the portal.


                  Try entering data in the portal and notice which fields automatically receive values without your having to type in data.

                  • 6. Re: 4 Tables from 1?

                    Alright.  I think I follow so far, but the way my database is set up, the Family table contains no data about which family it is (Family 1, 2, 3 or 4).  That data is stored in the related student record (unfortunately, I have no control over this, because that's how the old database system is set up).  Should I perhaps add a script trigger to the global field?


                    Also, the relationship from Alumni to Family is handled by a join table in between them.  Does this complicate the matter?


                    EDITED TO ADD:


                    One more thing, and maybe I'm just being ridiculous and thinking of too many possibilities, but if all the Family records are stored in one table, how would I handle a situation where one family is Family 1 for one student and Family 2 for another (I'm not sure how likely this is to occur, so it may not even be worth figuring out).

                    • 7. Re: 4 Tables from 1?

                      The join table actually simplifies what you have here.


                      You should add a field to your join table that identifies family membership.

                      You then filter the join table to see a particular family.


                      The join table will enable a family record to be "family 1" for one student and "family 2" for another.

                      • 8. Re: 4 Tables from 1?

                        I must have done something wrong.  I thought I was being so clever when I created a calculated field in join.Families.


                        The field FamilyType calculates with the Case function whether the associated FamilyID is Fam1, 2, 3 or 4 in the related student/alumni record.


                        Unfortunately, the moment I link the global field gFamily to FamilyType, all the related family field return <Index Missing>


                        I've placed my relationship graph below in the hopes that this may clarify things.  You can ignore the unrelated TO on the right, that's just an import of the entire Families table from the old database that I was using for reference.


                        Relationship Graph

                        • 9. Re: 4 Tables from 1?

                          I don't see why you have Family 1, Family 2, ... Family 4 tables or Table occurrences.


                          I am suggesting that you need only one here.

                          • 10. Re: 4 Tables from 1?

                            I just haven't gotten around to removing the Family 2, 3 and 4 tables yet, and the extra TO of the Alumni table is to match sibling records.


                            The only relevant tables on the graph to the current problem are the Alumni, Family1, and join.Families table.


                            Even after removing the Family 2, 3 and 4 tables completely, the global field thing still isn't working for me.  I know I must be doing something wrong, but I can't quite figure out what.

                            • 11. Re: 4 Tables from 1?

                              I should have taken a closer look at your earlier post. Here's the problem:


                              "The field FamilyType calculates with the Case function whether the associated FamilyID is Fam1, 2, 3 or 4 in the related student/alumni record."

                              This is an unstored calculation and thus won't work in your relationship. You'll need to figure a different way to identify the family in each join table record that is a stored, indexed value.

                              • 12. Re: 4 Tables from 1?

                                Huzzahs are in order!


                                It worked!  I only had to hit my head against my desk a few dozen times before I realized that I had disabled entering data in Browse mode when I last edited my "DateCreated" and "DateEdited" fields, and that stuck when I created my global field.  Took me a while to figure out why I couldn't change what was selected in the pop up, but now it's all set.


                                Thanks again, and tahnks you especially for your patience with me.  It's much appreciated!


                                I think this database is just about ready to go!