2 Replies Latest reply on Dec 19, 2010 11:40 AM by JohnHall7407

    How to do a find to show all classes a student is NOT enrolled in?

    JohnHall7407

      Title

      How to do a find to show all classes a student is NOT enrolled in?

      Post

      Only 3 tables. Students, Classes and a join table called Enrollment

      Students table has an id StudentID and Classes table has an is ClassesID. These two ids are entries in the join table to show which classes a student IS enrolled in.

      We'd like to show a list of the classes that a student is NOT enrolled in also so they can add in a class that they are not currently enrolled in.

      How to do a find that shows all classes a student is NOT enrolled in.

      StudentsClassesTables.jpg

        • 1. Re: How to do a find to show all classes a student is NOT enrolled in?
          FentonJones

          Create a calculation field, in Students; result Text.

          List ( Enrollment::ClassID )

          This will return a return-separated list of the student's ClassIDs. Duplicate the Classes table occurrence. Tie it directly to the Students table occurrence, with the above calculation pointed to the new "student_Classes~Not Taken". The operator would be "≠" (not equal).

          There is a "gotcha" however. The relationship as is will not work for the 1st Class choice. It needs at least 1 value to "not equal" (it cannot "not equal" from nothing). So you need to give it a default value. It can be anything which does not match any ClassID. Then it will show all the Classes for that 1st choice.

          If the ClassID is a Number field, you'd could use 0; if ClassID is a Text field, you can use "zzz". So we need to edit that calculation:

          Let (
          ls_classes = List ( Enrollment::ClassID );
          Case ( IsEmpty (ls_classes); "zzz"; ls_classes )
          )

          The checkbox [ ] "Do not evaluate if all referenced fields are empty" needs to NOT be checked; since we want a result even if the List() is empty.

          You can use this table occurrence (relationship) to filter a Value List, or for a portal. Because of the unstored relational nature of the setup, however you choose a new class from it will require a Commit Records and a Refresh Window [ Flush cache join results ], in order to refresh the list, removing the value just chosen. This technique is known generally as "dwindling".

          • 2. Re: How to do a find to show all classes a student is NOT enrolled in?
            JohnHall7407

            Fenton,

            Wow! It works. Thanks. I never would have found this method.

            Thanks again,

            John