1 2 Previous Next 15 Replies Latest reply on Mar 31, 2010 11:27 AM by jhed

    Sunday School Family Registration



      Sunday School Family Registration


      I am a beginner and I am working on Mac 10.6.1 and have just downloaded Filemaker Pro 11. I have a database that I created in Filemaker Pro 6. I am using the database locally on my MacBook.


      I wonder if I can link multiple children in one family in my Student Record. 


      Is there a way to have a family file and have sub files with the children in that sub file. I would want to access the children's information individually at times. 


      The smith family has 3 children in the program

      John 2nd grade

      Joan 4th grade

      Betty 7th grade


      I need to have John's info appear in the family file as well as the 2nd grade file when I sort the records. Some of the family info would also populate John's record when I sort by grade.


      Thanks for any help





        • 1. Re: Sunday School Family Registration

          Think tables not files.


          Two Tables: Families, Children


          In Families, define an auto-entered serial number: FamilyID.

          Add fields as needed in this table to record data on the entire family such as mailing address, emergency phone numbers, etc.


          In Children, define a number field: FamilyID

          Add fields such as name, birthday, etc that are specific to that one child.


          Link the two tables by FamilyID.


          Note 1: A portal to Children on the Families layout can be very useful here. If you don't know about portals, look the term up in Filemaker Help to learn more.

          Note 2: You can make a layout that refers to the children records, but also shows fields from the Families table.

          • 2. Re: Sunday School Family Registration

            Thank You


            I will give it a try.

            • 3. Re: Sunday School Family Registration

              I'm having a similar problem.


              I'm using FileMaker 10 on a MacBook Pro (10.4.11).


              I'm creating an alumni database for a post-secondary academic program, and I'd like the records to relate smoothly and populate from one layout (preferably the student's record).


              I've gotten it to work to the point where I can enter in the student's record (using a field from the Family table) and this will create a record for the family.


              To use a practical example, let's say I have a student, John Smith Jr., and I start a record for him which contains an ID number, his name, a FamilyID (matched to the Family table), and then enter in his father's name, John Smith Sr.


              The way this is set up right now, this will automatically create a record in the Families table and populate the father's name in both.


              If I start a new student record for John Jr.'s brother, Jim Smith, and then enter the father's name, it will create a 2nd family record.


              How can I set this up so that I can easily enter data for siblings without creating duplicate records.


              Please let me know if I could make this clearer, and thank you in advance.


              EDITED TO ADD: 

              I am a new user to FileMaker Pro 10 (Mild Newbie... perhaps "Intermediate Beginner?")... I have used earlier versions (whatever was current in 2001 or thereabouts) so it's been some time.  I have built simple databases in FileMaker before, but nothing with complex relations.


              The database I am currently building will be either just local or via a local network, not published to web.

              • 4. Re: Sunday School Family Registration

                If I am following your description of your tables correctly...


                Use a portal on the families table to display records from the students table. Enable "allow creation of records via this relationship" for the Students records in the relationship.


                Now add new student records for a sibling simply by entering the data in the bottom line of the portal.

                • 5. Re: Sunday School Family Registration

                  That might work, but unfortunately, the way the database works it would be most useful for me to be able to enter this information from the Students/Alumni layout.


                  Perhaps I oversimplified my explanation a bit, but ultimately, this databse is intended to have a structure approaching the following:


                  List of Tables:




                  Family 1


                  Family 2


                  Family 3


                  Family 4


                  The reason for there to be multiple family records is that some of the students may have parents that are divorced... and remarried.  That means at least 2 complete family records.Ultimately, the problem is really that I need the database to be student/alumni-centric.


                  I'm wondering (and I have little experience with scripting) if what I need is some kind of "If/Then" logic here...  Something where I enter a parent's name, for example, and if it matches an existing family record then I would have the option to link the two records?  Or am I just making this too complicated for myself?

                  • 6. Re: Sunday School Family Registration

                    As a separated parent myself, I understand the issue. It's not necessary, however, to have mutliple tables for this. You can set up a single table for families and use a join table to assign students to families (And this allows you to assign one student record to more than one family records all in the same table.) On the other hand, usually one parent is the custodial parent and this is often the parent record you need to link the student to.


                    To link to student records to more than one "family" record:


                    3 tables: Students ----< Student_Family>-----Family



                    Student::StudentID = Student_Family::StudentID

                    Family::FamilyID= Student_Family::FamilyID


                    A portal to students on the Family layout will show all students that are a member of that family. Switch to the family record for the separated parent, and you'll see the same student listed and there can be other students listed who are not part of the first family.

                    • 7. Re: Sunday School Family Registration

                      I keep getting one step closer to the solution I'm looking for, but not quite.


                      After browsing the forums, I did discover the whole join table concept so that I can have the "many-to-many" relationship.  However, the way I would like it to function, practically, is to have one layout for data entry, and another to view the info.  In the data entry layout, I would like it to function in the following manner (I'm going to try to simplify this so that it only includes the most basic information, and not every field I intend to use):


                      AlumID (serial number assigned on commit)

                      AlumName (we will pretend this is one field for now)



                      :: Parent1aName

                      :: Parent1bName

                      :: Parent2aName
                      :: Parent2bName



                      Upon entering data into this layout, I would like the Parent/Family records to automatically populate.  I've managed to set this up in such a way that it works if there is a 1:1 relationship between students and families, or even 1:many relationship, but if there is a many:1 (multiple students per family) then the way I have it set up now, I wind up creating duplicate family records.


                      I know there must be a way to do this, because I've seen other database solutions that work in a similar way, I just have no idea how and I keep going round in circles in my head trying to figure out the logic of it.


                      On a mostly unrelated note, since I'm a pretty novice-level user, I am wondering if it makes the most sense to have separate tables for all kinds of data (i.e. tables for Addresses, Phone Numbers, Emails, Photos, all of which consist of just an ID key for matching and the related info).  Is that just ridiculous, or is that actually the way I should be doing it (most of the documentation I've read seems to imply the latter).


                      And thanks for all the help!


                      EDIT:  Removed smileys (::P)

                      • 8. Re: Sunday School Family Registration

                        Question: When do you set up the Parent/Family records? Before or after you enter the student data?


                        This appears to be the student layout. If you've already entered the family records, You should be able to enter that data in the portal simply by selecting parents from a drop down field in a Portal to Student_Family. This action should create a new record in Student_Family, but will not create a new record in Family.


                        In the portal to Student_Family, put the field for Student_Family::FamilyID in the portal row.

                        Format it as a drop down with a two column value list of Family ID numbers and names from the families table. (You may need to define a calculation field in families that combines first, last names for this).

                        Place the name fields from Family next to this field in the same row.

                        When you select a family in this portal, you should see other fields in the row populate with data from the Family Record.


                        Once you get that to work, you might want to implement a technique for finding Family records without having to scroll through such a long list of ID numbers:

                        Tutorial: How to use an auto-complete drop down list when selecting records related by ID number

                        • 9. Re: Sunday School Family Registration

                          I will give this a try, but I still feel like I'm slightly off of the solution I'm looking for.


                          To answer your question (I hope), the Parent/Family records are set up "after" the student data.


                          In the student layout, I would create a record (at this point, there are no records at all in any of the tables).  This will create the student record with the ID number 1, for the sake of argument.  Once the parent data is entered this creates Family ID number 101, a new record.  So, at first, there is no family data to select, only if a sibling's data has already been entered.  if we continue with this example, that would be the following scenario:  create student record (ID number 2), enter student data, upon entering parent/family data, the current setup creates Family ID number 102, instead of allowing me to link student 2 to Family 101.


                          The portal solution may work, I'll have to try it out.

                          • 10. Re: Sunday School Family Registration

                            In order to link the student to a family record, you'll need to be able to first create that family record. One approach is to add a button to your layout that creates a new family record for you. You would click this button, and a script would first create a new family record, then create a Student_Family record and enter the FamilyID value from the new Family record into FamilyID in your new Student_Family record so this new record will appear in your portal and you can enter the parent names for the new family record in the portal.

                            • 11. Re: Sunday School Family Registration

                              So, I've tried several different permutations, and I'm still not getting this to work quite right.  I figured the most natural way to do this in the UI would be for the search script to be triggered by leaving the alumni last name field.  The script this triggers is the following:


                              Freeze Window

                              New Window [ Name: "Family 1 Search/Link"; Height: 400; Width:400; Top: 200; Left 200 ]

                              Go to Layout [ "Family 1 Search/Link" (Family1) ]

                              Enter Find Mode [ ]

                              Set Field [ Family1::P1aLast; Alumni::LastName ]

                              Set Error Capture [ On ]

                              Perform Find [ ]

                              If [ Get (FoundCount) > 1 ]

                                   Exit Script [ ]

                              Else If [ Get (FoundCount) = 1 ]

                                   Set Variable [ $Family1; Value:Family1::FamID ]

                                   Go to Layout [ "Alumni" (Alumni) ]

                                   Set Field [ Alumni::Fam1ID; $Family1 ]

                              Else If [ Get (FoundCount) = 0 ]

                                   Show Custom Dialog [ Message: "No Family 1 contacts match the name you entered."; Buttons: "OK" ]

                                   Go to Layout [ "Alumni" (Alumni) ]

                              End If

                              Close Window [ Current Window ]


                              The script that is triggered by the button (placed in my "Family 1 Search Link" layout) is the following


                              Set Variable [ $Family1; Value:Family1::FamID ]

                              Go to Layout [ "Alumni" (Alumni) ]

                              Set Field [ Alumni::Fam1ID; $Family1 ]

                              Close Window [ Current Window ]


                              I haven't actually tested the functionality of the 2nd script in this context yet.  I did try constructing the Fred/Fanny/Filmore example and I got that to work just fine, so I must be doing something wrong.


                              For example, if I enter "Smith" into the LastName field of my Alumni table/layout, the script is triggered.  Presuming I have no records to begin with, it brings up the Custom Dialog (no records match...).


                              So, I go ahead and enter it into the Family1::P1aLast field in the Alumni layout.  This creates a record in the Family1 table with P1aLast = Smith.


                              If I go into the Family1 layout, I can confirm this record has been created and assigned a FamID.


                              Now, when i create a 2nd record back in the Alumni layout and enter "Smith" again into the LastName field, I can see that the 1st script is triggered again.  It seems to run through the script, as no dialog comes up, but neither the P1aLast nor the Family1ID fields are populated with the data from the Family1 table.


                              I also get no result if I create multiple family records with the last name "Smith" and search.


                              Would the relationship graph be helpful?


                              • 12. Re: Sunday School Family Registration

                                So, I've altered the script and now it works to a certain extent, but it's not actually returning the results in find mode.


                                The script in its current form is:

                                (extra spaces have been added to avoid smileys a la: :P)


                                Freeze Window

                                Go to Layout [ "Family 1 Search/Link" (Family1) ]

                                Enter Find Mode [ ]

                                Set Field [ Family1 :: P1aLast; Alumni :: LastName ]

                                Set Field [ Family1 :: P1bLast; Alumni :: LastName ]

                                Set Error Capture [ On ]

                                Perform Find [ ]

                                If [ Get ( FoundCount ) > 1 ]

                                     Halt Script

                                Else If [ Get ( FoundCount ) = 1 ]

                                     Set Variable [ $Family1; Value : Family1 :: FamID ]

                                     Set Variable [ $Alumni; Value : Alumni :: AlumniID ]

                                     Go to Layout [ "Alumni" (Alumni) ]

                                     Set Field [ Alumni :: Fam1ID; $Family1 ]

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

                                     Set Field [ join.Families :: fk.FamilyID; $Family1 ]

                                Else If [Get ( FoundCount ) = 0 ]

                                     Go to Layout [ "Alumni" (Alumni) ]

                                End If


                                The way I figure this should work is when the Alumni layout "LastName" field is exited, the script is triggered.

                                The script opens a special layout of the Family1 table designed to view in list mode only.

                                Script triggers find mode.

                                Enters the Alumni::LastName entry into both P1a and P1bLastName search fields (Parent 1 and Parent 2).

                                Performs search.

                                At this point, if there are multiple matching records, shouldn't this script be halted in Find Mode with only the search results showing?  I would think that would be the case, but it always returns all records.


                                I was hoping to expand this script so that it would perform the same search across Family1, 2, 3 & 4 tables, but if I can't even get it to work for one table... well, you get the idea.  That's not even the major problem though, because I figure I could in theory just store all family info in one table, I suppose, and add a field to indicate which family (1, 2, 3 or 4) it should be assigned to be.


                                But I need to get the script working for that fancy footwork to take place.

                                • 13. Re: Sunday School Family Registration

                                  You need to capture your search criteria in either variables or global fields before performing the find:


                                  Freeze Window

                                  Go to Layout [ "Family 1 Search/Link" (Family1) ]

                                  Enter Find Mode [ ]

                                  #at this point Alumni :: LastName will be blank because you are in find mode.

                                  Set Field [ Family1 :: P1aLast; P1bLast; Alumni :: LastName]

                                  Set Field [ Family1 :: P1bLast; Alumni :: LastName ]


                                  Change it this way:

                                  Set Variable [$Last ; Value: Alumni :: LastName

                                  Go to Layout [ "Family 1 Search/Link" (Family1) ]

                                  Enter Find Mode [ ]

                                  Set Field [ Family1 :: P1aLast; P1bLast; $Last ]

                                  Set Field [ Family1 :: P1bLast; $Last ]

                                  Set Error Capture [ On ]

                                  Perform Find [ ]


                                  "At this point, if there are multiple matching records, shouldn't this script be halted in Find Mode with only the search results showing?"

                                  No, because perform find will return you to browse mode. This would seem to be the desired result as you can now make this window a picklist where clicking a name selects that record from the list of matching names.


                                  Something to think about: What if you are entering data for a new "Smith" family and this is the second Smith family ? Your planned design will automatically select and enter the first smith family even though that's not the correct family record as you haven't yet entered this new family record.


                                  • 14. Re: Sunday School Family Registration

                                    Oh, that's a **bleep** good point!


                                    I suppose I should include a way that if the results are > or = 1 then there is an additional option to create a new family record.  I guess an extra button?

                                    1 2 Previous Next