5 Replies Latest reply on Dec 26, 2011 3:44 PM by philmodjunk

    Calculating a portal column



      Calculating a portal column



      I have a data base I use to track student progress.  Originally, I created a field for everything;  HW ass 1, HW ass2 HW ass3, etc., Att wk1, att wk2, att wk3, etc.. I could go on.  This worked fine, since everything was essentially built on a "one-to-one" relationship.  That is, one particular hw grade was meant for one particular student.  


      This system became cumbersome when I went to a team format.  The grades for one team now affected more than one student.  Rather than entering all the information into each student layout, I explored related fields.  And this will work for the final grades.  Now I am wodering how i can eliminate the need for all of the custom homework and attendence fields.

      My thought is that I can create a new table called "Attendence."  Instead of creating a series of fields, one for each class session, I think I can create a set of "generic" fields that can be used over and over again.  For instance, instead of creating layout with a series of fields like this: att wk1, att wk2, att wk3, etc., I could create a series of layouts. one per class session,  that has the following fields: Student Name, Att.. Each layout would have a one record oer student.  I could then create a layout per student to show their attendence for the semester.  I could then link this table to the "summary Sheet" through a portal that would show each students attendence record.

      Here is the problem.  I cannot total the attendence in the "per student" record of the attendence table.  And becuase, of that I can not show the tally in the portal.  This means that I have to manually count the fields, and then manually enter the data into the total field.  This seems so arduous, that I am sure there must be an easier way.

      if the attendence image is not working for you, consider this.  I am a manager who want to track my salespeoples sales for teh year.  I then want to tally the amount they soldso that I can determine their commissions.  I would not want to creat a new field for every sale, so I would set up a "sales" table, and then link it to the individual salesperson's summary sheet.  I could then create a portal to the sales table in order to see all the sales.  How do I get a total of the sales?

      Any help you can give would be greatly appreciated.  I can also continue with what I am doing, but I am on the cusp of seeing databases in a new light.


        • 1. Re: Calculating a portal column

          instead of creating layout with a series of fields like this: att wk1, att wk2, att wk3, etc., I could create a series of layouts. one per class session,

          Actually, you can do this with just one layout if you set it to bring up the needed records for a specified class session.

           I could then create a layout per student to show their attendence for the semester.

          Not a good idea if you really mean separate layouts for each student. A class of 30 students would need 30 layouts--and you only need one layout to show the attendance records for any given student just by perfroming a find to the Student Record for a specified student.

          We still need to look at the structure of your tables and their relationships before getting too deeply into how to set up the layout to do what you want here. It can depend on the way your school is organized.

          Do you teach multiple subjects with a different subject for each class session? Can a student attend more than one class taught by you? (Maybe they take Biology from you in the morning and Algebra from you in the afternoon, or they take a lecture class and then also the lab session from you...)

          The answer to that qustions tells me which of the following should be set up for recording student attendance:



          This in turn sets up the foundation for how you'd link in an attendance table as it would either link to Class_Sessions or to Students.

          Either way, there are two ways to count the number of records where a given field is not blank:

          Count ( relatedTable::Field )

          If defined in the parent (layout) table will count all related records where the field is not empty.

          So will defining a "count of" summary field in the attendance table if you then place that field either directly on your layout or inside a one row portal (can be needed if you use portal filtering).


          • 2. Re: Calculating a portal column

            Wow.  Great answers, but I am stuck on a few points.  Let me start by explaining how my calsses are set up, and then I will ask the questions related to your suggestions.


            Currently, I teach three classes.  Two are section of the same subject, so all the assigned work is the same.  However, the dates are different.  In fact, one section meets once a week, while the other meets twice a week.  Each student is in one section, and essentially, there is no cross over.

            The third class in an advanced class of the same subject.  This means that everything is different, including the way the final project is set up.  In the first two classes, each student completes the final project independently.  In the advanced class, the students work in teams, so much of the final grade is shared.

            I will upload an image of this, but as I mentioned in the original post, I currently have a data base that  is based off of indivisually defined fields.  This is fine, until you get to the final project for the advanced class.  This means that the each student has a record within the database, which is made up a series of layouts based off of one table.  This is fine, but it does lead to a lot of fields.  Attendance for instance, is a huge pain.  Att wk 1, Att wk1b, Att wk2, Att wk 2b (i need two per week because one section meets twice a week.), etc..

            When I said layout is in the original post, I think what I meant to say was that in the new version, each area of grade would get it's own layout; Homework, Quizzes, Labs, Final, etc., with each student having a record associated with each layout.  Each student would then have a summary sheet that brings in the information from the various layouts.   I will upload a series of pdf's that shows this configuration.  

            Sounds cumbersome, I know, but I seem to be stuck between two paradigms.  One is made up of lot's of fields and relatively few records.  The other is made up of lots of records, with few fields.

            If I understand what you are saying about the "Count" tool is that I can have multiple instances of a field, say "attendance," in a report/layout, and then use the count tool to calculate all the instances of that field.  In the old version it was much easier because each field was disticnt.  So I could create a calculation that summed att wk 1, att wk. 2, att wk. 3, etc..  When I create an attendance summary per student using the new format, the calculations only "sees" one field, att sub total.  I will explore the count tool as it seems like what I am loking for.

            Thanks for the help.  I am a newbie pushing the limits of my knowledge base, so I appreciate your help and your patience.  Take a look at the uploads, they may help explain my vision.  

            • 3. Re: Calculating a portal column

              The pdf didn't load so here it is again.

              • 4. Re: Calculating a portal column

                Count works.  


                I cannot seem to upload my images.  Not sure why.

                • 5. Re: Calculating a portal column

                  You cannot upload PDF's only graphic format files: jpg, Gif, or PNG. Try a screen capture.

                  Theres an important conceptual point that needs to be made: layouts and tables are two different things. When you try to use the terms interchangeably, it gets confusing to figure out what you have. A table is a set of records each of which represents a set of fields. A layout is a means of making the data in those fields--whether from one record or many records, from one table or many tables accessible to the user so that they can see, analzye, edit the data in those records.

                  A table occurrence, one of the boxes found in Manage | Database | Relationships is the means for linking a layout to a specific table. If you open layout setup, for a given layout, you'll find a drop down list of names in "Show Records From". This list of names is the list of table occurrence boxes on the relationships tab in Manage | Database. You can, in fact, have multiple table occurrences that all refer to the same data source table.

                  To learn more about table occurrences, see this tutorial:  Tutorial: What are Table Occurrences?

                  Individual fields for each assignment, as I haver recommended elsehwere, is not a good structure for a database for managing student achievement. Each assignment should be a separate record in a related table. You can, in fact, put all such student achievement scores, Homework, tests, quizzes, finals, projects, etc in the same table. The same layout or a group of layouts--each customized to a different type of student achievement may be used to view and record student achievement.

                  Such a table is typically linked to an assigments table--where a record documents a specific assignment issued to a class or group of classes and to a students table in order to identify the student receiving that specific score. Calculation fields, and summary fields can then be defined to use one student grading algorithm or another to produce a student grade for the grading period.


                  Students::StudentID = StudentScores::StudentID
                  Assignments::AssignmentID = StudentScores::AssignmentID

                  Assignments will have fields such as Date assigned, date due, Description, AssignmentType, Chapter, etc. And then also an auto-entered serial number for AssignmentID.

                  StudentScores might not have more than three fields: AssigmentID (to link to assignment fields), StudentID (to link to a student record) and Score, the raw number of points awarded to the student for that one assignment. There might also be summary fields defined in this table that compute total, average and other aggregate values to be used in determining a student's grade.