4 Replies Latest reply on Nov 18, 2014 10:34 AM by LAPaiva

    Show all Classes with Classes chosen marked.

    LAPaiva

      The following example from FileMaker Help explains the set up of my question perfectly. I just need help taking it one step further: I would like to have a layout based on the Students Table and with a portal that shows all the classes available (in the Classes Table) with the classes that the student has chosen marked with an x. In other words, in the layout, I would like the student to see all the class options as well as see the classes they have marked/chosen all in one portal. What is the cleanest way to accomplish this? Help finding an answer will be greatly appreciated!

       

      Example:

      A student can take many classes and a class can have many students. Relating these two tables together would be difficult without the use of a join table. The join table will contain the primary keys from the student table and the class table. The join table can be called anything you want. For this example we will call the join table “signups.”

      1. Create a new table in FileMaker Pro and call it "Signups"
      2. In the “SignUps” table, create a primary key field called “SignupsID.” This step is not necessary for the jointable to work properly but it is good relational database practice for every table to have a primary key.
      3. In the "SignUps” table, create, at least, a “StudentID” field and a “ClassID” field. Since these fields will be populated when records in either the “Students” or “Classes” tables are created, no auto-enter field options are needed.
      4. Additional fields can be added to the “Signups” table as necessary. Some examples of other fields that might be appropriate for the “Signups” table would be a “date” field to keep track of when someone started a class or a “cost” field to keep track of how much someone paid to take a class. Join tables typically hold fields that might not make sense to have in any other table.
      5. Relate the tables together based on the primary and foreign keys

      ImageIf a student signed up for three classes, a student would have one record in the Students table but would have three records in the “Signups” table – one for each class he or she signed up for.

      A by-product of utilizing a join table is that you are able to access fields and data across tables without having to create a separate relationship. For example, if you wanted to display a list of all the classes that a student was signed up for you could do the following:

      1. On a layout based on the Students table, create a portal. The portal should be configured to show related records from "Classes"
      2. Add the appropriate fields from the Classes table into the portal

      Now, as you browse through records in the Students layout, the portal will display all the classes a particular student is signed up for.

        • 1. Re: Show all Classes with Classes chosen marked.
          erolst

          lpaiva wrote:

          a portal that shows all the classes available (in the Classes Table)

           

          Create a new TO of the Classes table – say, Classes_Cartesian – and connect it to the Students TO, connecting any two fields via the Cartesian operator (x). Put a portal into Classes_Cartesian on the Students layout.

           

          lpaiva wrote:

          with the classes that the student has chosen marked with an x.

           

          Either apply Conditional Formatting (CF) to a field in the portal (e.g. a background fill to the class name), or apply Conditional Hide (CH, FM13) to an object that you put into the portal.

           

          For CF use

           

          not IsEmpty ( Filtervalues ( Classes_Cartesian::id ; List ( Signup::id_class ) ) )

           

          to apply the formatting to Classes that are taken, and for CH use

           

          IsEmpty ( Filtervalues ( Classes_Cartesian::id ; List ( Signup::id_class ) ) )

           

          to apply the Hide effect to your object within portal rows of Classes that are not taken.

          • 2. Re: Show all Classes with Classes chosen marked.
            LAPaiva

            Thank you for your response. I have tried your suggestion but it just marks all the classes that have been marked across all the students and not just the classes that have been marked for a specific student... it seems to me that the student ID would need to come into play here since the cartesian table relates all the classes to all the students.

            • 3. Re: Show all Classes with Classes chosen marked.
              erolst

              lpaiva wrote:

              it seems to me that the student ID would need to come into play here since the cartesian table relates all the classes to all the students.

               

              Correct – but that happens when collecting the list of class_id from the related Signups records, which are related to each Student via their ID.

               

              There must be a reference error in your calculation. See the attached file for a working example.

              1 of 1 people found this helpful
              • 4. Re: Show all Classes with Classes chosen marked.
                LAPaiva

                Thank you so much for the example file. It was very helpful and I have it working now. I thought for sure it could be done with a calculation in a field so I had not even considered conditional formatting. Thanks again!