Help! Calculation Field to show required courses have been completed

[ACAM]

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

 

Edited November 3, 2015 by ACAM

[Lee Smith]

Hi ACAM, and welcome to the FM Forums,

Congratulations on your FIRST post, it appears that you covered your situation well.

Do you have Admin privileges?

Before you make any chances, be sure and BACK UP your file. 

You might want to attach a copy of your file here (no records), as it can speed things up, and it make it easier for you to follow along with the suggested help. This way, will be using your naming conventions for things. such as  field names, layouts, relationships, Table Occurrences, etc.

Lee
 

[comment]

Let me start by saying that this is a difficult question. If you consider yourself "a total beginner", you could have picked something easier to tackle. It is difficult, because the status of a specific Contact with regard to a specific Competency depends on the presence and status of multiple joining records (siblings of each other). You don't find that often in a relational database.

Now:

1 hour ago, ACAM said:

Am I on the right track?

Not really. The basic flaw with your approach is that it would require a calculation field for each Competency. That's not acceptable, because staff, courses and competencies are data. Users should be able to add as many as required, without having the developer add a calculation field every time.

There are of course several ways to approach this - I believe I would do something like the attached. It actually shows two ways to display the status of a competency: one is a calculation field in Enrollments, the other uses conditional formatting in a portal to Competencies.

StaffCompetencies.fp7

Note that this is based on the assumption that each Course serves one Competence only. If this is incorrect, then this gets (even) more interesting...

 

 

StaffCompetencies.fp7

Edited November 4, 2015 by comment

[LaRetta]

That conditional format calculation is unexpected!  Thank you for sharing it!  :-)

BTW, you have two files attached which ( appear to be/are ) identical?

[comment]

7 hours ago, LaRetta said:

you have two files attached

There is only one. The forum software displays it twice, because I chose to add it to post. The second link is supposed to be in a footer, I guess.

[ACAM]

Thank you so much everyone. I have not started plugging away at your replies yet, but just the fact I received replies is amazing. I appreciate you all.

I am attaching a copy of the database. 

Thank you, again!

Training DB_11.2015.fmp12

[bcooney]

Tip: Do not include periods in a file name. Breaks stuff (like value lists).