Competencies are unique to a Course? or can two courses have the same competency?
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.
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?
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.
Starting with this:
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.
Courses and Courses|All would have the same data source table. Course_Competency is a join table similar to your enrollment table.
I've confused myself. What fields need to go into the table called Competencies?
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.
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.
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)
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.
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 ) )
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 ) ) )
I am so excited. That worked perfectly. I cannot thank you enough for your patience and sound advice. THANK YOU.