Can you show your report layout in layout mode, or provide a sample file?
Are there two or three tables involved: Employee and Training; or Employee, TrainingCategory, and TrainingEvent? If there are three tables you could have the relationship between TrainingCategory and TrainingEvent sorted by Expiration date in descending order. In this way, the first record (which is the default record shown by FileMaker when there are multiple related records) would be the most recent expiration date.
Karen, you should probably design your data structure to take full advantage of FileMaker Pro's relational capabilities. What you need are separate tables for "Employees" and "Classes", with a merge table (also called a join table or link table) in between them to keep track of each legitimate combination of an employee and a training class. Here's what it would look like in your Relationship diagram:
You'll notice that off to the left is a "Class Picker" table occurrence (TO) of the main "Classes" table. That's intended to enable you to easily assign an employee to a class. (Be sure that you define that relationship so that "Class Picker" is sorted alphabetically.)
Within "Employees" there's a Global field called "Initial Plug". It's formatted as radio buttons using the value list "Alphabet", which is exactly what it sounds like. Clicking any letter in that field will establish a link to all the classes that start with that initial. (For instance, clicking on "C" would bring up "CPR", "College Prep", and "Chemistry".)
Why does it do that? Because on the other end of the relationship, in the "Classes" table, you've defined the "Initial Socket" field thus:
Then place a portal into "Class Picker" on your main "Employees" data-entry screen. Display the class details (name, date, time, place) in that portal along with a button that's formatted to create a new "ECLinx" record for that combination of employee and class whenever you click on it.
Also on the main "Employees" data-entry screen, create a portal into "ECLinx", so you can see what classes the employee has signed up for.
On the main "Classes" data-entry screen, create a portal into "ECLinx", so you can see what employees have signed up for that class.
Good luck, Karen. I'm sure you'll enjoy all the fun you can have with FileMaker Pro.
Thank you for all your responses, I really appreciate it. To reply to some questions, below is how the relationship structure is set up:
And below is how the report is set up:
The layout for the report is based on the TrainingRecord table. - So there are basically 2 main tables involved, The employee table and the training record. The way the report prints now, It will sort alphabetically and group the records by Training Description, then by Expiration date, which sorts by most current first. What I don't want to see is the old record for the same person/training that has expired, since they already had the updated class. - unless of course they didn't. The main data entry table is based on the Employee record, which lists all the training that they've had.
So looks like a join table is needed? (per Richard's response)
Thanks again, everyone!
I'll get this yet.. LOL
What's in your "SOP" table, one record for each type of training course?
Yes. That's a table they can add to for the common Standard Operating Procedures we use, just so they have a drop down to select one, or they can just type in a training description into the employee record if one doesn't exit. The SOP table also contains a PDF link to the actual SOP if there is one.
Then consider this additional set of Table Occurrences and relationships:
Employees::anyField X SOPs|All::anyField (Literally, pick any field you want for match fields here)
SOPs|All::TRTrainingItemSOP = TrainingRecord|bySOP::SOP Number AND
SOPs|All::gEmpID = TrainingRecord|bySOP::TREmpID
gEmpID is a global field of the same type as Employees::EmpID. Us OnRecordLoad to perform this script to update this field so that only Training Records for the current employee are linked via these relationships:
Set Field [SOPs|All::gEmpID ; Employees::EmpID ]
Now you can set up a portal to SOPs|All and include the TrainingRecord|bySOP::TRTrainingDate field in the portal row. If you specify that the TrainingRecord|bySOP table occurrence be sorted by TRTrainingDate in descending order, the date to appear in the portal row will be the most recent training date for that SOP for a given employee. SOPs for which they have not received training will be listed, but the date field will be empty--which may be a useful way to see what training is lacking for a given employee.
Karen, I've put together a small demo file that incorporates the approach I've described above, and several other useful techniques as well. I'd be pleased to send a copy of it to you — and, really, anybody else who'd like to look at it as well. Just e-mail me at RichardSRussell@tds.net and ask.
Thank you, Phil. Hopefully I'll have some time tomorrow to take a look at all this. Appreciate your response in the call for help. Will let the post know how it works out.. Thanks again. Karen