12 Replies Latest reply on Jul 14, 2013 11:49 AM by PeterMontague

    Band and Orchestra Attendance Help

    builder

      Title

      Band and Orchestra Attendance Help

      Post

      Hello,   

          I am fairly new to Filemaker (using version 10). I am trying to make a attendance list for music students enrolled in band and orchestra. So far I have the following.....

       

      Student Record (studentID, name, instrument, grade, ensemble, etc.

       

      Band Attendance (Date, bandIDfk)

       

      Orchestra Attendance (Date, orchestraIDfk)

       

      I have the tables related between the ensemble field and the repective bandIDfk and orchestraIDFK. The ensemble field is not a unique id (though each student has a unique id), it is a check box list of Orchestra, Concert Band, and Jazz Band. 

       

      I then have a portal in each attendance layout that lists all the students' that have that ensemble selected in their student record. 

       

      It displays the information exactly the way I want it too, But I thought I could add a "Present\Absent" field to each portal and have a checkbox display to take attendance, unfortunately they all get set to the same value which makes sense.... 

       

      How can I added a field in the attendance layout that will give me the option of selecting a present\absent for each student record?

       

      I hope I am explaining myself clearly.

       

      Thanks so much

       

        • 1. Re: Band and Orchestra Attendance Help
          philmodjunk
            

          Builder,

           

          You need separate records for each student, for each date. Your Attendance table should have:

           

          Attendance:

          Date:

          StudentID

          Present (a text field you can format as a check box.)

          bandIDfk

           

          You need a new table, let's call it Ensemble:

          Date:

          bandIDpk

           

          Set up the following relationships

          Ensemble:: (BandIDpk, Date) ---- = ---- Attendance(BandIDfk, Date)    (You're matching two pairs of fields here, date to date and ensemble type to ensemble type)

          Ensemble::BandIDpk --- = --- Student Record::Ensemble

           

          One record in Ensemble will represent one rehearsal date for one ensemble. Every time you create a new record in Ensemble you'll need to create a matching record in Attendance for each student that is a member of that ensemble. This can be done with a script and since you are using FMP 10, you can set up a script trigger to perform this script every time you create a new ensemble record.

           

          This is just a first step to point you in the right direction. There's no way for me to know if I've given you enough help that you can finish things from here or if this will just trigger the next round of questions. Take a look at my suggestions and let me know.

           

          I don't see any reason for you to have one table for orchestra and a different table for orchestra. If you can store data from all ensembles in a single table, it will simplify the process. Otherwise you'll have to apply the above steps twice.

           

          PS. I used to play trombone myself :smileywink:

           

           

           

           

          • 2. Re: Band and Orchestra Attendance Help
            builder
              

            Thanks for the quick response... I am trying to set it up the way you describe it but I am not having much luck...

             

            The Student ID you mention in the Attendance.. does that come from the student record?

             

            Where should my portal be? In the Attendance or the ensemble.... I am trying it in the ensemble layout and adding the present box to the portal but I still only get on record....

             

            Sorry....  

             

             

            • 3. Re: Band and Orchestra Attendance Help
              builder
                 Also, how do I populate the related date field and ensemble fields when a new record is created.. I seem to not have any luck.
              • 4. Re: Band and Orchestra Attendance Help
                davidanders
                  

                There are references on-line about database design and creation, this is one...

                 

                White Paper for FMP Novices

                http://www.foundationdbs.com/Downloads/WhitePaperForFMPNovices.pdf

                http://www.foundationdbs.com/downloads.html

                 

                It is sometimes useful to work on a test dummy database, rather than one that one has emotional attachment to.... 

                • 5. Re: Band and Orchestra Attendance Help
                  philmodjunk
                      

                  builder wrote:

                  Thanks for the quick response... I am trying to set it up the way you describe it but I am not having much luck...

                   

                  The Student ID you mention in the Attendance.. does that come from the student record?

                   

                  Yes, this value matches the value you have in Student record.

                   

                  Where should my portal be? In the Attendance or the ensemble.... I am trying it in the ensemble layout and adding the present box to the portal but I still only get one record....

                   

                  Yes, you want the portal in a layout that specifies Ensemble as its table. The portal should refer to Attendance.

                   

                  Sorry....  

                   

                  Don't be sorry. You're right on track. One thing I can't tell from a thread is how much ability the other person has when it comes to setting things up. Other forum posters wouldn't have gotten this far without stopping and asking for help.

                   

                  You won't see the records you need in the attendance portal until we set up a script to initialize the portal with one matching record for every student in the ensemble.

                   

                  If you've set up your relationships right, You should see two Table Occurrences of Student Record. One links student record with attendance and a second occurrence links student records to Ensemble. If you don't see this when you open up the relationships graph, and don't know how to do this, post back here for directions.

                   

                  I am going to call the TO for Student Record that links it to Ensemble by BandIDfk, "StudentRecordbyEns"

                   

                  Now we can set up a script to populate our attendance portal.

                   

                  Initialize Attendance:

                  #Create a found set in Student Record of all the students enrolled in the current ensemble

                  Set Variable[$Ens,Ensemble::BandIDfk]

                  Set Variable[$Date, Ensemble:: Date]

                  If [count(StudentRecordbyEns::StudentID) = 0 /* No students are assigned to this ensemble */]

                      Show Custom Dialog ["No student records for this ensemble"]

                      Exit Script []

                  End If

                  Go to Related Record[Show only related records, from table: StudentRecordbyEns, Using layout: "LayoutName"(Student Record)]

                  Set Variable [$IDlist,""]

                  Loop

                    Set variable [$IDlist, $IDlist & Student Record::StudentID & "<Paragraph Symbol>"]

                    Go To Record [Next, Exit after last]

                  End Loop

                  Go To Layout ["layoutName"(attendance)]

                  Set Variable [$i,1]

                  Loop

                    New Record

                    Set Field [attendance::StudentID,MiddleValues($IDlist,$i,1)]

                    Set Field [attendance:: Date, $Date]

                    Set Field [attendance::Ensemble, $Ens]

                    Set Variable [$i, $i + 1]

                    Exit Loop if [MiddleValues($IDlist, $i, 1) = ""]

                  end Loop

                   

                  In the above script example, you will need to replace "Layout Name" with the name of a layout in your database with the same underlying table as that shown in parenthesis. Likewise, there is a paragraph symbol in the Specify Calclation Dialog box that looks a little like a backwards "P". Put that in place of <Paragraph Symbol>.

                  Save this script.

                  Create a record in Ensemble. Enter a date and a valid number into Ensemble::BandIDfk. Perform the script and see if you can populate an attendance portal with all the right students.

                   

                  These steps won't finish your project, just move you to the next step on the way...

                   

                  Please note that I am assuming a certain level of expertise here. If you haven't ever created a script, then reading the white paper another poster has recommended would be a good idea.

                   

                  • 6. Re: Band and Orchestra Attendance Help
                    builder
                      

                    Thanks so much... that should keep me busy for the next couple hours. 

                     

                    One other thing.. Do any of the fields need to be set to auto lookup? How does the student Id number go from the record the attendance? I have StudentID in my Student Record related to the Student ID in the attendance but I am not sure if I need to have it automatically populate itself...

                     

                    Thanks again

                    You = the man 

                    • 7. Re: Band and Orchestra Attendance Help
                      builder
                        

                      Once again, thanks so much.. It works just as I would hope... 

                       

                      I have a fair amount of Flash actionscript under my belt, to the scripting isn't so bad...

                       

                      I appreciate all the time you put into this....

                       

                      Again You = The Man! 

                      • 8. Re: Band and Orchestra Attendance Help
                        philmodjunk
                          

                        Do any of the fields need to be set to auto lookup?

                        In my particular example no. As you develop further, the "create related records" option in your relationship definitions is often a useful tool for auto loading the key field of a new related record.

                         

                        How does the student Id number go from the record the attendance? I have StudentID in my Student Record related to the Student ID in the attendance but I am not sure if I need to have it automatically populate itself... 

                        In my example:

                        The script builds a list of student IDs in a variable and then uses Set Field [] to store these values one at a time in the new records as they are created.

                         

                        • 9. Re: Band and Orchestra Attendance Help
                          builder
                            

                          One more if I may....

                           

                          I adjusted the script a little in order to attach it to the a field in order to trigger it and it works great..

                           

                          To only problem is that if you click into the field again and then click out, it will duplicate all the records..... I originally had it attached to a button and realized if I click it twice it duplicated the records... 

                           

                          So, is there a way to execute the script only once?

                           

                          I added a Attendance ID and set it to auto serial number which in turn adds that number to each of the records created so I can deleted all of them in case I want to delete a record.

                           

                          Thanks

                           

                          • 10. Re: Band and Orchestra Attendance Help
                            builder
                              

                            So I made a field called Marked and have the script entering a "X" into the field at the end and then testing if there is an "X" in the beginning of the script and dropping out if it tested correctly...

                             

                            Thanks again 

                            • 11. Re: Band and Orchestra Attendance Help
                              philmodjunk
                                

                              You can also use the Count function to check for the existance of any related records. If the records exist, you've already created the related attendance records and the script should then exit without creating them a second time.

                               

                              If [count(AttendanceTableOccurrence::StudentID) = 0]

                                 Exit Script[]

                              End If

                              • 12. Re: Band and Orchestra Attendance Help
                                PeterMontague

                                     Im setting something similar up. I want to create a portal that puts students into a class group. I have it matched to a TO of student records by the name field. In this way i want to type in the students name and have the fk_student if field to look up the id from personnel records I also have this class group portal linked by student id to a TO of itself. The idea of this is to allow me to enter lots of students' names.

                                     Anyway I cant get it to work. When I enter a students name it looks up the student id and makes all of the portal records have the same name and same student id.