13 Replies Latest reply on Oct 29, 2013 2:30 PM by peter415

    Academic Advising Question -- Checkbox/Value List

    peter415

      Title

      Academic Advising Question -- Checkbox/Value List

      Post

           I'm trying to find an automated way to show which competencies a certain student has not yet achieved based on the courses they have taken. The competencies are associated with courses and are set up in a checkbox value list.

           Courses may have more than one competency. Over the course of their academic career, students must meet each competency at least once.

           I set up a portal, and have three tables, student, courses, and a join table called enrollment.

           I want to create a field that shows which competencies have NOT been met, so that when I am advising a student, I can easily look at their record and say, "you still need to meet XYZ competency".

           I'm using FMP12. Thank you!

        • 1. Re: Academic Advising Question -- Checkbox/Value List
          davidanders

               Competencies are unique to a Course? or can two courses have the same competency?

          • 2. Re: Academic Advising Question -- Checkbox/Value List
            philmodjunk

                 Competencies|All>---X---<Students-----<Enrollment>------Courses-----<Course_Competency>-----Competencies

                 Looks like a better approach to me than your check boxes. I'm assuming that a given course can link to more than one record in competencies and that a given competencies record can be linked to more than one course. (Specifying competency links for a course can still look and feel like a check box format if you want to set that up.)

                 The two entities on the ends of that chain would be table occurrences with the same data source table. The relationship between students and Competencies|All would use the cartesian join operator (x) to match any student to all records in the table.

                 A filtered portal to Competencies|All using this portal filter expression:

                 IsEmpty ( FilterValues ( Compentencies|All::__pkCompetencyID ; Competencies::__pkCompetencyID ) )

                 Should then limit the list to only those Competency records that aren't linked to one of the student's enrollment records.

                 A calculation field using ExecuteSQL() could also be set up to list the Competencies not yet met by a given student.

            • 3. Re: Academic Advising Question -- Checkbox/Value List
              peter415

                   Hi  DavidAnders yes,  competencies can be shared by multiple courses.

                    

                   Hello PhilModJunk -- I've seen your well constructed answers on the forum in the past, and I was afraid yet hopeful that you'd tackle my question. I will try to implement your suggestions. Disclaimer: I anticipate coming back with more questions. 

                   As I was trying to work it out myself, it started occurring to me that checkboxes might be a bad idea.

                   Is it correct that "A calculation field using ExecuteSQL() could also be set up to list the Competencies not yet met by a given student." is a totally separate option from the one using the portal?

              • 4. Re: Academic Advising Question -- Checkbox/Value List
                philmodjunk

                     Yes, it's a totally separate option. It's not a good option unless you are prepared to wrestle with the needed SQL expression but it eliminates the need for the Competencies|All table occurrence and makes it possible to set up a list of values in one field of unmet competencies and this list can be formatted with check boxes if desired.

                • 5. Re: Academic Advising Question -- Checkbox/Value List
                  peter415

                       Starting with this:

                  Competencies|All>---X---<Students-----<Enrollment>------Courses-----<Course_Competency>-----Competencies

                  I think I'm going the right direction with my specific tables.

                       Is it correct that Course_Competency is the data source table for the Competencies|ALL and Competencies table occurrences? If so, what fields related between Course_Competency and Competencies?

                       Here's a screen shot of where I'm at.

                       Thanks so much.

                  • 6. Re: Academic Advising Question -- Checkbox/Value List
                    philmodjunk

                         Courses and Courses|All would have the same data source table. Course_Competency is a join table similar to your enrollment table.

                    • 7. Re: Academic Advising Question -- Checkbox/Value List
                      peter415

                           I've confused myself. What fields need to go into the table called Competencies?

                           pkCompetencyID

                           Competency Name

                           And then, what fields go in the join table Course_Competency?

                            

                           I'm sorry, I think I need a break. I feel like I am so close and now further blind tweaking may have set me back.

                      • 8. Re: Academic Advising Question -- Checkbox/Value List
                        philmodjunk

                             One record in Competencies represents one value in your original check box list of competencies. The table would have at least one field, more likely two:  At the very least, you'll need a text field for the name of the competency. You may also want a primary key field (such as an auto-entered serial number).

                             Course_competency can be patterned after how you set up enrollment. It need only have two fields, Each a foreign key field for matching to one of the other two table occurrences shown in the relationship.

                        • 9. Re: Academic Advising Question -- Checkbox/Value List
                          peter415

                               That makes sense to me.

                               Next question - what two fields do I link between the Competencies_Domains|ALL and Students, where I'm using the cartesian join op? (screen shot)

                                

                          • 10. Re: Academic Advising Question -- Checkbox/Value List
                            philmodjunk

                                 Any pair of fields may be used. It doesn't matter. It's even possible to define a pair of extra fields in both tables, use them as match fields in the relationship and then when you delete the match fields, the relationship continues to work.

                            • 11. Re: Academic Advising Question -- Checkbox/Value List
                              peter415

                                   Now I'm working on the filtered portal, using the it seems to only omit the very first competency the student has completed and show all others in the portal. Any ideas on this anomaly? (most likely operator error on my part)

                                    

                                   This is the formula I used, based on my best interpretation of the one you provided: 

                                   IsEmpty ( FilterValues ( Compentencies_Domains|ALL::CompentencyDomain_ID ; Compentencies_Domains::CompentencyDomain_ID ) )

                              • 12. Re: Academic Advising Question -- Checkbox/Value List
                                philmodjunk

                                     You are missing the use of the List function to get the needed list to be filtered by FilterValues. And you are missing it because I accidentally omitted it in my example.

                                     IsEmpty ( FilterValues ( Compentencies|All::__pkCompetencyID ; List ( Competencies::__pkCompetencyID ) ) )

                                • 13. Re: Academic Advising Question -- Checkbox/Value List
                                  peter415

                                       I am so excited. That worked perfectly. I cannot thank you enough for your patience and sound advice. THANK YOU.