8 Replies Latest reply on Aug 20, 2010 10:17 AM by philmodjunk

    Generating Class Lists for Student Enrollment

    RandyS_1

      Title

      Generating Class Lists for Student Enrollment

      Post

      Hi All,

      I'm working on a database for a dance studio and things are coming along nicely. I have a Students, Classes, Enrollments and Line Item tables set up each with their own primary keys. StudentID, ClassID, EnrollmentID and LineID.

      2 thinks I want to generate and maintain.

      1) A list of students enrolled in each class.

      -- I think I'm okay with doing this via a relationship and a portal.

      2) A tally of how many students are in each class.

      -- With registration coming up in September it would be nice to have a running tally of the how many students have registered for a class so my client can keep track of any over bookings for a particular class.ie, create a list view of all classes offered with a number showing how many students were in each class so far.

      I guess the question is, for example, for ClassID=312, how many students have enrolled in it? Or in the Line Items table, how many records have a ClassID=312. That would be the number of students in the class.

      I have a few ideas about how to loop through records and start doing a tally then putting the result in a field, but that would be like running a report. Which is fine, but I was wondering if there was a way I could do that tally on the fly so it is up to date.

      Should I be looking at a summary field for this?

      TIA,

      Randy

        • 1. Re: Generating Class Lists for Student Enrollment
          philmodjunk

          How have your related your tables? like this?

          Students----<Enrollments>----Classes

          In other words, is Enrollments a join table between Students and Classes?

          If so, you can use a portal to enrollments on a Classes layout or you can put together a report layout based on enrollments that either lists all the students in a given class or lists all the class enrollments with the records grouped by class so that you see the enrollment for all your classes.

          You can also define a calculation field in Classes that uses the count () function to count the number of enrolled students for a given class.

          • 2. Re: Generating Class Lists for Student Enrollment
            RandyS_1

            1) I have the class list working in a portal. I'm getting the hang of this. In the Classes layout I made a portal to the Students 2 table occurence and when students are enrolled in a class they end up in the class list.

            2) So if I'm getting the right number of names in the class list, ie 4 students enrolled in ballet and I see 4 names. I should be able to pull the count of 4 out of this somehow. I'm just not sure about how a calculation field using the count function will give me the result I want in the Classes table.

            The relationship I'm using is like this.

            Students----<Enrollments-------<Line Items>----Classes

            What, the heck, here's a screen shot ... as you can see I've picked up on the _pk and __fk nomenclature :)

            relationship diagram

            • 3. Re: Generating Class Lists for Student Enrollment
              RandyS_1

              Bingo, got it, I just had to choose the ClassID in the Line Items table ...

              count(TableOccurancePortal::__fk_ClassID)

              • 4. Re: Generating Class Lists for Student Enrollment
                philmodjunk

                Not sure why you have a line Items table here, but the graph suggests you might have more than one line Item record for a given enrollment record. If that's possible, your count will be off.

                • 5. Re: Generating Class Lists for Student Enrollment
                  RandyS_1

                  Recall that I began with the Invoices.fpy Start Solution in FM 11.

                  Customers => Students
                  Invoices => Enrollments
                  Products => Classes
                  Line Items => Line Items

                  In Invoices.fp7 file there is a line items table that adds a record for each product that is choosen. The workflow goes something like this.

                  1) Create a Student

                  2) Create an Enrollment for that student

                  3) Add Classes to that Enrollment via a Portal to Line Items in the Enrollment layout.

                  So 1 Enrollment can have many classes. ie 1 Student, 1 Enrollment, many classes. The resulting Line Items table just grows with each class added.

                  Does all that sound reasonable?

                  • 6. Re: Generating Class Lists for Student Enrollment
                    philmodjunk

                    OK, so "class" is like the product catalog. You have a class called "Beginning Ballet", but in line items, you have "Beginning Ballet, taught by teacher XYZ for session A"

                    That makes sense. If you put the count calculation in line items, you'll get a count for that specific class offering. Put it in Classes and you'll get the count of all the enrollments for all the students in all sessions of that class.

                    Different counts, but both could be useful.

                    • 7. Re: Generating Class Lists for Student Enrollment
                      RandyS_1

                      Yes, exactly. Lots of possibilities.

                      Right now I'm just trying to get the version 1.0 out for the client with the features they've asked for. They are already very happy with what they are seeing when compared to a version they had made for them in MS Access last year. I've been able to streamline their workflow with various layouts and reports.

                      Once they get used to it they'll probably start asking for a lot more features once they realize it doesn't take a lot of work ($$) for me to implement.

                      Speaking of delivering version 1.0, I have purchased the FM Pro Advanced version so I can compile their own runtime version of the database. I've done a test run of installing it on their office computer and that works great BUT I'm worried that I haven't left myself a strategy for updating functionality while leaving all their data intact.

                      Right now everything is in one .fp7 file. Maybe I should keep the tables themselves external in separate files? Or can I build a runtime solution such that it uses an .fpy file instead of a .usr one? Or can I just change the .usr to .fp7 and re-open in my authoring environment to make updates?

                      Sounds like I need to start a new thread.

                      Cheers,
                      Randy

                      • 8. Re: Generating Class Lists for Student Enrollment
                        philmodjunk

                        There are several stratgies different developers select from (and they aren't mutually exclusive either):

                        Separate the database into two files: File 1 has all scripts, layouts etc. but each table occurrence in the graph refers to an external data source in file 2. Since many updates do not require modifying the table definitions, you can deploy an update that just swaps out the the front end file (file 1) instead of having to import data from the old version into the new.

                        Deploy your database file with a script that steps through all your tables and does a show all records. When you deploy a new version, use a script that

                        a) asks the user to select the file with the data from which to import
                        b) runs the show all script in that file to get all set up with a found set of all records.
                        c) uses import records to import all the data from all the tables into the new copy
                        d) uses set next serial value to update all serial number fields

                        Some developers split their solution into more than two files so that they can use a simpler update process (Just replace the file or just import data from one table instead of all of them) and so that they can mix and match files to supply different module combinations to different customers. This complicates password management, scripting and other aspects of system integration but simplifies updates.