Hello There!
I work in a healthcare HR training department. We provide courses for our hospital staff. I inherited a FM database that is used to track all enrollments, attendance, test scores, etc. I have never attended any FM training myself and have not been in a position to make too many changes to the already existing database, so please be kind.
Our database has 3 tables: Courses, Contacts, Registration. We have a portal linked to the Registration table which links Courses and Contacts. Courses are added to a students record and once they complete a class, the "Attendance Status" field is updated to "Attended".
I need to develop some way to identify when a staff member completed a core set of courses -- not all courses fall into these core competencies. For arguments sake, let's say there are 4 courses that are required before someone is considered to have completed our basic core competencies: CourseA, CourseB, CourseC, CourseD
I tried to create a calculation field which would live in each row of the Registration portal which read: Attended_CourseA -- If (CourseName = "CourseA" and Attendance Status = "Attended"; 1). I did this for each of the courses and hid the field in the portal rows. To test this, I created a small table in the participants record with Sum fields for each of the Courses: Sum (Attended_CourseA). My intention was to then create another calculation field which would read "Core Courses Completed" (or something like that) if all 4 courses returned a 1.
Unfortunately, I cycled through several records and I could only get the table to return a "1" for records matching in the last portal row. For example if CourseA was in the last portal row, that field return a 1. In the next record, if CourseD was the course in the last portal row, then THAT field returned a 1, etc, etc. I could not find a record which contained a 1 for more than 1 of the core courses in my calc fields. .
Obviously, I am doing something wrong. Please help!
Am I on the right track?
Is an IF calculation the best way to do this?
Would a script be better?
Please help...and please speak to me as if I am a total FM beginner. This is not my database, I am self taught with what I know (which I realize are completely beginner level work-arounds) and I 100% appreciate any help you can provide. Let me know if I need to provide additional information or if the way I described the problem is not sufficient. I don't know where else to get help.
Thank you!
ACAM
