1 2 Previous Next 15 Replies Latest reply on Jan 12, 2011 9:03 AM by philmodjunk

    a design question



      a design question


      I am just starting to storyboard a new solution for my wife's yoga classes, and need some direction on how things are set up. Here is where I think I am headed:

      Table 1 would be student records including amounts paid and number of classes purchased

      Table 2 would be attendance. The classes never have more than 20 students or so. I thought each record could be dated, and then all current students displayed by way of a check box value list. This way, she can quickly check off all of the students attending a particular class.

      Where I am having problems is in getting the data out of the value list above. I need to know for each student what date he attended and then use that data to calculate how many classes are remaining. I don't know how to do that.

      I also need to know how many students in a class. To my way of thinking this means counting the number of times that the check box value list has been populated with an X. I don't know how to do this, either.


        • 1. Re: a design question

          A different table structure would make this easier for you.


          This then is a typical Many to Many data relationship.

          Students::StudentID = Attendance::StudentID

          ClassMeetings::ClassID = Attendance::ClassID

          A portal to attendance can be placed on the ClassMeetings layout to track attendance. To mark a student present, you'd select them in the Attendance::StudentID field. Since each time a student attends is recorded in a separate record, you can create reports based on the attendance table to show attended each time the class met.

          • 2. Re: a design question


            I am thinking that both of us are spending too much time on the computer. Your answer was mighty fast…

            I should add that Suzie will use this little DB on her iPad. We'll do the IWP thing here; the DB will live on her computer. I haven't looked at GO yet but for something this simple not sure she needs it.

            I'll work on this on the next few days and undoubtedly be back with more questions…

            • 3. Re: a design question

              Hi Phil,

              Every few years I need to dive into FM inards to either update our solution, which has been running for more than 10 years, or to create a new one. And since this happens sporadically, my working knowledge of FM appears quite basic, once again, until I get going on the project.

              So, this morning  I am setting off to do this as you describe it. Here's a translation from FM talk to English, which is helpful for me to actually understand things better:

              The solution has two tables: one for students, and one for class meetings.

              The key field linking these would be StudentID.

              Are you saying that ClassID is also a key field? This is where I get a bit lost so perhaps you wouldn't mind explaining again? I do know how to work with portals, and the concept seems clear, but visualizing the form is not clear to me.

              For example, say we are taking attendance for class 0001. She'd have a form in front of her identifying the class, and showing all of the current students. She'd mark each on present as the case may be. Is this how you are seeing this work? 

              The form would be based on the attendance table. The portal would come from the students table, linked by studentID/ Yes?This would have the same appearance as the one I attached a few days ago, yes.

              This will be perfect as it will also give me easy access to student data and summaries.


              • 4. Re: a design question

                First of all, you'd have at least three tables, not two. One to list your students. One with a single record for each class session and one for each student's attendance status for a given session. If your wife offers different kinds of classes (Beginning, Intermediate, advanced, etc.) , you'll want a fourth table with one such record for each kind of class. And possibly a fifth table to document which student is enrolled in a given type of class. (Needed if it's possible for a student to enroll in more than one type of class.)

                ClassID might be better labeled as "meetingID" and might even be just a date or a time stamp (Timestamps record dates and Times) if your wife offers only one type of class. And yes, it is a key field to link a given attendance record to a given ClassMeeting record.

                You have two options for recording attendance.

                Option 1: Use a portal and a drop down to create a new record for each student attending a given class session. If a record for that student and session exists, the student was present. If no record, the student is absent.

                Option 2: Use a script to create one new record in Attendance for each student enrolled for that class. Use checkbox field with a single value to record who is present.

                • 5. Re: a design question

                  "You have two options for recording attendance.

                  Option 1: Use a portal and a drop down to create a new record for each student attending a given class session. If a record for that student and session exists, the student was present. If no record, the student is absent.

                  Option 2: Use a script to create one new record in Attendance for each student enrolled for that class. Use checkbox field with a single value to record who is present."

                  The reason I laid out the attendance form like I did is because there is a lot of activity at the time she is taking attendance, and because she will be using an ipad. By having a form with all of her current students in a field displaying a value list with all current fields, she merely touches each box to check off a student. There is far too much activity for her to do anything more complex.

                  I like the purity of your direction and it's superior to my original approach. I'm playing with it tonight to see how it goes.

                  • 6. Re: a design question

                    What you describe is option 2.

                    • 7. Re: a design question

                      This is totally completely wrong, isn't it?



                      That would explain the head banging I did last night trying to make things work. Which I didn't.

                      • 8. Re: a design question

                        Looks correct to me. Attendance is serving as a join table linking students to Class Sessions--which is what I recommended.

                        If you describe what's not working here, we can debug the details.

                        • 9. Re: a design question

                          OK then. But what is the purpose of the join table? I can already link all records by student ID?

                          I am not sure what the next step would be, partly because I am not sure what the join table is doing for me. My thinking is this: in the class sessions table, create a layout that represents a single class session: 12.27.2010 at 9AM, for example.

                          Then, I need to indicate which students are present, and I do this by showing student first/last name via a value list with check boxes with data from Student Records because at some point I will need to know how many classes a student has attended in the current semester and compare that with the number of classes purchased.

                          I tried this, but I don't think I am headed in the right direction. I do like the way the sheet looks as it will be simple for the teacher to take attendance at a time when there are many distractions and students milling about. Perhaps, though, that this is where you mentioned using a script to create new records in Attendance?

                          Here's conceptually how that teacher's attendance form would look:  ftp://msadesign.com/teacher.attendance.form.jpg

                          • 10. Re: a design question

                            Each table has a different purpose:

                            In Students, you have one record for each student.

                            In Class Sessions, you have one record for each class session.

                            In Attendance, one record records whether a specific student was present or absent for a specific session.

                            A portal to Attendance on a Student based layout will list that student's attendance record for all class sessions.

                            A porla to Attendance on a Class Sessions layout will list the attendance records for all students for that specific class session.

                            • 11. Re: a design question

                              I know the classes form will be  where I will take my attendance but I cannot figure out how to show the  student body in a portal and then indicate if the student was present  or not for that particular class.

                              Maybe displaying and using the data the way I want isn't possible? I've been trying this every which way…


                              • 12. Re: a design question

                                Place a portal to Attendance on your Class Sessions layout. Define a field in Attendance for recording attendance and format it to be a checkbox with a single value check box. You can either click teh check box to mark students present or to mark them absent--your choice.

                                You'll need a script to generate the attendance records for each class session so that they are in the portal when you need them. A script can be devised that loops through all your student records and creates one record for each in your attendance record for the current Class Session Record.

                                Run this script from a Class Session Record:

                                Set Variable [$SessionID ; ClassSession::SessionID]
                                Freeze Window
                                Go To Layout [Students]
                                Show All Records
                                Go To Record [First]
                                   Set Variable [$StudentID ; Students::StudentID]
                                   Go To Layout [Attendance]
                                   New Record/Request
                                   Set FIeld [Attendance::StudentID ; $StudentID ]
                                   Set Field [Attendance::SessionID ; $SessionID ]
                                   Go To Layout [Students]
                                   Go To Record [Next; exit after last]
                                End Loop
                                Go To Layout [original layout]

                                • 13. Re: a design question

                                  Phil: working with it now. Thanks for the step by step.

                                  Update 12.31: I see that records are being created but there is something missing and I do not know what. I am also not completely sure that I have the correct form for Set Variable. Perhaps you would have a look at what I have done?


                                  Update 1.1: appears to be working but while it creates records the records are blank and in fact there are no fields in the layout

                                  Update 1.3 Working! except don't know how to create a new class session record? all i do is create new portal records?


                                  • 14. Re: a design question

                                    I have gotten excellent and thorough help with my project on this board (thanks, Phil, for your incredible help) but still need assistance. I know how to kludge this together, but want a better and more robust solution.

                                    I hope this isn't out of line for this board and forgive me if it is. I'm just hitting a blank wall and need the guidance of a professional. 

                                    Anyone want to spend a few hour$ on this little project please let me know…I don't see a PM function here so email me is fine (email removed). Goals of the project would be simply to get the infrastructure for attendance/ fees set up. 

                                    The work would largely include answering questions and helping with ideas and approaches and should proceed quickly. I am thinking literally a few hours helping over some of the related table and script stuff.

                                    1 2 Previous Next