1 2 Previous Next 19 Replies Latest reply on Aug 2, 2012 1:14 PM by MarcMcCall

    Starting a School Database - help with setup and relationships

    AmyKraunz

      Title

      Starting a School Database - help with setup and relationships

      Post

      Hello. I am a parent for a small charter school and even though new to FM have agreed to help setup a database. I feel like I have a very basic structure, but can already see how it will fall apart quickly. The main components will be a Student Page - with tabs for Info, Attendance, Teacher, Medical Issues. A Parent Page - Info, Volunteer Hours, Employment Info and MOST importantly Donations. This is where I am starting to fall apart. Right now for each student I have "Parent 1" with phone number and addresses and "Parent 2" with hone number and addresses. Have a drop down box for "parents share home", "single parent" and "parents separate homes". Works great for info page, but when I say want to do a mail merge of all parents - have the ones for the couples being addressed to them as a couple and then a page for just single parents and separate parents, not even sure how to set up this possibility. Also, one of the main points of this database will be to track donations, how can I credit say "parent 1" and "parent 2"? also how can i credit friends and neighbors that donate - should track through student's name? Also want to include table with staff info.

      ANY help anyone can provide is much appreciated. This doesn't have to be a super complicated database, just want it to perform  useful function. Any free student databases out there that people know of? Did a search and couldn't find any.

      Thank You!

        • 1. Re: Starting a School Database - help with setup and relationships
          GuyStevens

          If you are going to create something like this, a starter solution won't do the trick. You will have to start from scratch and do the whole thing your self.

          You will have to learn about filemaker and adopt some good practices.

          A good starting point might be following along an exercise like this one:

          http://forums.filemaker.com/posts/404122a451?commentId=201161#201161

          That will teach you a lot about creating a database from scratch. And once you have done an exercise like that you will be ready to start your own project.

          If you have any more specific questions, make sure to let us know.

          The first thing to do when starting a database is thinking about your structure.

          As an exercise, try and write down the tables and fields you might need. Like you can see in this post:

          http://forums.filemaker.com/posts/404122a451?commentId=201807#201807

          • 2. Re: Starting a School Database - help with setup and relationships
            AmyKraunz

            Thank you so much! I will take a look at all of this info after work tonight. I really like the structure that I've come up with, it's just a matter of figuring out al the relationships - this seems to be the troublesome part for me. AND how to join certain fields and not show other fields when combining. If all the students only had one parent the relationships would be easier. I'm assuming I need to create more table, but I am already working of an excel spreadsheet with 200 students names, info, parent info x 2, class info, etc. Thanks for your help! Might be back for more questions.

            • 3. Re: Starting a School Database - help with setup and relationships
              AmyKraunz

              Hi there. I'm going to repost in general area, but wanted to ask you one last question if I may. So, thanks to you I restructured the whole database - all relationship now, and seems to be working really well. I'm only having one small hiccup that is driving me crazy. So..... I have a student info table, I have a parent info table. the tables are related, so if you re on a student page you can see who their parent(s) are. On the parent page you can see who their student(s) are along with connecting via a relationship page to their volunteer hours and to a separate donation table. What I would REALLY like though is to be able to connect to parents that are married/partners, but they are on the same table. For example, if John Smith #0001 is married to Mary Smith #00002 I'd like to be able to put a field/button/portal on John's page that says partner/spouse ID, I can input that ID and it connects to Mary's page. Is there anyway to do this. I'm driving myself crazy trying to make it work. Thanks for your help!

              • 4. Re: Starting a School Database - help with setup and relationships
                MarcMcCall

                What you need to do is go to the Graph area under manage database and make another instance of your table of parents. If you already have your 2 fields the auto enter serial Id filed for the record, and the spouse Id, make those 2 fields = each other in the graph. then when you enter the autioenter serial for the spouse in the spouse Id field they will be related and you will be able toi show data from the 2 record on each other.

                • 5. Re: Starting a School Database - help with setup and relationships
                  GuyStevens

                  Looks like Marc McCall's idea is not such a bad one.

                  On a student's layout you would select a first parent. And if that parent is married you would select the spouse.

                  You might also add a field with the marital status (married, divorced, ...)

                  Or call it "Type" That way you can also add friends and neighbours ass well as married parent, divorced parent, ...  that are related to a specific student.

                  What do you do when you enter a donation?

                  So you select the student first? Or do you select the parent immediately?

                  If you select a parent on a donation, then do you also show what student is related to this parent?

                  • 6. Re: Starting a School Database - help with setup and relationships
                    GuyStevens

                    Let me know if you can't figure it out and I'll try and find some time to make a little test file.

                    • 7. Re: Starting a School Database - help with setup and relationships
                      AmyKraunz

                      Thank you both SO much. OK, so I got the records to link - perfect. Now.... here is my next question. Say for example I have 100 records and I am on record #46 = John Smith, on it you now see the lovely spouse name "Mary Smith", her record is #47. I attempted to make the spouse name field on John's record a "button", thinking when you click on Mary's name it will take you to her record (#47), but it doesn't take you anywhere because it is on the same layout. I've successfully done the button thing with say the parents names on the student's records, click on it and brings you to parent record. Is this just impossible if staying on the same layout?

                      DaSaint - to answer your questions best I can... I do have a married/divorced/single parent type pull down. Though I've done it all a little differently. I have the student as my main record. There you can enter the parents names and 2 main phone numbers into the parent portal. Then you can click on that field to get to the parent page to enter more specific info and then on the parent page their are individual tabs - volunteer hours, donations, event attendance. 

                      I'm not sure how to send you a file, but would love to send you a copy of where I'm at and see what you think. Not sure how you all have the time and the kindness to help people like me on this forum, but as a filemaker and forum novice, can't tell you how much your assistance is worth.

                      • 8. Re: Starting a School Database - help with setup and relationships
                        GuyStevens

                        Hey Amy,

                        I was just looking at your file.

                        First thing I noticed is that you might be missing a join table.

                        Right now you have a student table and a parent table and you only have the ability to relate one parent to one student.

                        What if a parent has three children?

                        Now, when you have a student, you just enter the data for the parent, but if you have a brother or sister of this student, you have no way to select the same parent you already entered.

                        So you have several parents that exist multiple times in your parent table.

                        The idea is that every parent exists only once, and every student is linked to that single parent.

                        Do this as a test: Create a new layout in Table view based on the Parents table. Check out and you will see you have multiple parent records with the same name. There are three records for Sarah Barksdale.

                        That's not really the idea in a relational data base.

                        Strangely enough they all have the same ID so I assume you have been cheating a little here :)

                        I ave been looking at your file and the fact that you have this doubling is very annoying.

                        No matter how you do it you keep running into problems later on.

                        An idea I'm having now is to just add the spouse information in the parents table.

                        Because when you have a married couple they have the same adress and home number. So maybe no need to make a seperate record.

                        Only in case of divorced parents would you create two records.

                        Maybe make a calculated fullname field that in case of married couple creates: "Bradd & Angelina Pitt" (or something similar)

                        You might also create some extra fields for spouse cell phone, occupation and spouse email etc.

                        • 9. Re: Starting a School Database - help with setup and relationships
                          AmyKraunz

                          Thanks so much DaSaint for looking. Your comments are always super clear so really help me. I originally started with shared parent information, but then split it for many reasons. The first reason, a good friend is a database manager (on system already set up, not filemaker) and said it is very important to have each parent as individuals as he has seen so many people divorce, re-marry, etc etc. Also, the school wants them as individuals as really want to track volunteer hours and event attendance for each person.

                          And yes, I did cheat the same IS for the multiple parent record. Since the student page is my main page, I put their name in first and then enter the parents name in the portal, this creates a record for the parents that i then fill out. For the few parents that have multiple children, I went to the table and changed their ID #. this worked as all the address and contact info also updated, but know is not correct way. What would be?

                          And you are right. I probably should have a family join table, but again, would have to hand enter the ID into the family ID tag for multiple siblings and parents and seemed like one more ID tag to confuse things. Any thoughts?

                          If can send image like marc did that is the best way for me to understand things as am still confused about foreign key field you had in your one exercise.

                          • 10. Re: Starting a School Database - help with setup and relationships
                            GuyStevens

                            And you are right. I probably should have a family join table, but again, would have to hand enter the ID into the family ID tag for multiple siblings and parents and seemed like one more ID tag to confuse things. Any thoughts?

                            ID's should never even be seen by a user.

                            You should never be enerting ID numbers manually, you should be using dropdown lists.

                            If you want to keep parents seperate you might need to make some compromises.

                            I'll think about it a little more and I'll let you know if I find a better way to do this.

                            The problem is that if you connect a father to the join table and this father has a spouse, the spouse will not be related to the student table, because the father is.

                            That will mean you always have to add the two parents separately to the join table.

                            • 11. Re: Starting a School Database - help with setup and relationships
                              GuyStevens

                              Another option would be to create two fields on the student table.

                              One for the father IdFk and one for the mother IdFk. And one record for home status. (Single home, seperated, ...)

                              And you keep the portal for other people related to the student.

                              Might be something worth trying.

                              That way, if the student has he home status as "Single Home" you can automatically get the other parent ID.

                              And your parents stay in different parent records.

                              I'll see if I can try this out.

                              • 12. Re: Starting a School Database - help with setup and relationships
                                AmyKraunz

                                hmm, interesting idea, though be aware we have a very diverse community. for example we have one student that lives with 2 mom's part of the week and 2 dad's the other part of the week = 4 parents. also, would this be able to show the siblings?

                                it's tricky as some students split their homes/main parent - so that's why i did all the home info on the parent page.

                                this is a whole new world for me!

                                • 13. Re: Starting a School Database - help with setup and relationships
                                  GuyStevens

                                  OK,

                                  Maybe we can go back to making it simpler.

                                  Let's just add parents one by one in the portal.

                                  One parent per line. And maybe some sort of "Sort of Parent" to show what is what.

                                  You could have mother, father, mothers partner, fathers partner, etc.

                                  Then you could relate spouses in the parent record. By selecting another parent from a dropdown.

                                  • 14. Re: Starting a School Database - help with setup and relationships
                                    AmyKraunz

                                    excellent - so that seems kind of what i did, adding parents through portal, except for the "definition" 

                                    in one version i did so some kind of join table to relate spouses - not sure if one sent to you - wondering if maybe 

                                    instead of relating the spouses  - just do a join table to relate the whole family, would solve problem of siblings and

                                    cut down on one less join table

                                    1 2 Previous Next