4 Replies Latest reply on Mar 15, 2011 9:45 AM by MatBou

    Checkboxes inside portal

    MatBou

      Title

      Checkboxes inside portal

      Post

      Hi, first post here :)

      Goal: basic attendance database for a martial art school

      tables:

      - students (contacts infos, 1 record per students)

      - class (1 record per class, date, time, students group, etc)

      - attendance (1 record per student attendance per class)

      On my Class layout, i select which group this class is for and it populates my portal, showing students from this group.

      So far so good. 

      Problem appears when I want a way to select if each student is present or absent.

      I've set this up with a value list (from the Class table) that is displayed inside each portal rows (each students from this group).

      Once I change this value for a student, it changes it for all student in the portal.

      I've understood that this happens because of my relationship between Students and Class (which is based on GroupID) and the fact that the "attendance" value list is from the Class table.

      My goal was to trigger a script that would create an Attendance record for that student (for this class, on that date, etc).

      I'm not sure how to solve this thing (having all the students attendance changing when I change only one in my portal).

      Any pointers ?

      PS: im just starting this database, I can screw everything and start over if my structure is wrong, which probably is...

        • 1. Re: Checkboxes inside portal
          kilbot

          Think you might need to create a Jointable between CLASS and STUDENTS:

          Set a field into your CLASS table as _kp_class

          double click the field go to AUTO_ENT tab and select serial number

          Then make a table JT_01_CLASS_STUDENTS

          make a field called _kf_class_students

          and another field groups

          Now join the two tables by    

                                                   CLASS T.O.        JT_01_CLAS..            STUDENTS

                                                  _kp_class -----> _kf_students

                                                                               groups <-------------group

          Hope this helps

          • 2. Re: Checkboxes inside portal
            MatBou

            Thx for the reply kilbot.

            I'm having a real hard time understanding self joined tables...

            By the time you answered, I partly solved my previous problem and changed my DB structure.

            New structure:

            same 3 tables

            relationships:

            - students::groupID >----- class::groupID (allow creation of records in Attendance)

            - students::studentID ------< attendance::studentID

            inside Class layout, portal showing students depending on which groupID is selected in this particular class.

            On each row, there is a Attendance::status (absent or present). when i select one, it creates an attendance record for this class/student/date (via lookup value inside Attendance).

            This looks much cleaner than scripting it and it solves the previous checboxes problem since the attendance status is not related to the groupID (like my previous problem).

            New problem shows up: if I create a new class for the same group (let's say 1 week later), it finds the attendance status from the previous week, it does not create new attendance record...

            So sorry for the noobism, but I gotta start somewhere ;)

            Thx for the help !

            • 3. Re: Checkboxes inside portal
              philmodjunk

              ---< means "one to many"

              Students----<Attendance>------Class

              In detail:

              Students::StudentID = Attendance::StudentID
              Class::ClassID = Attendance::ClassID

              Students::StudentID and Class::ClassID are auto-entered serial number fields. The matching fields in attendance are number fields. "Allow creation of records via this relationship" should be enabled for attendance in the Class--<Attendance relationship.

              A text field, AttendanceStatus can be defined as a text field and formatted on the layout with a value list of "Present" and "Absent". A date field, SessionDate, can be defined in Attendance to record the date of the class session.

              Now, to record attendance, you can put a portal to attendance on the Class layout. Include the StudentID, AttendanceStatus and SessionDate from the attendance table in this portal. You can also include fields from students such as a name field from the Students table as needed. Format the Attendance::StudentID field as a drop down list or pop up menu of StudentID's from the Students table with student names listed as a second column in this value list. To Mark a student as present, you go to the bottom blank row of the portal and select them in the studentID field's drop down. Then enter the date (this field can also be set to auto-enter today's date). Then select "Present" as their attendance status. Do the same for absent students, but select "Absent" or you may be able to just do nothing. (Depends on your report needs).

              Once you have those basics working. We can talk about a script that sets up the entire roster of students for each class for the current day's session and a portal filter that can be used to limit the attendance records so that you only see one day's attendance record at a time.

              PS. You've mentioned "groups" in your post, but haven't elaborated. Depending on whether a single class has more than one group and/or whether each "class" is a specific meeting on a specific date or a specific subject/skill level that meets on a series of dates, we may need to further modify this structure.

              • 4. Re: Checkboxes inside portal
                MatBou

                All good ! thank you so much.

                I got a script running that creates the attendance records based upon which group (hence the groupID field) a class is created for, so that my portal gets filled with the correct students.

                Everything makes much more sense suddenly; thank you so much for your fast replies guys !