9 Replies Latest reply on Aug 22, 2016 3:25 PM by Karend440

    Finding most recent date of training item by employee and training type

    Karend440

      Hello.. not sure about how to go about this.

      I have a set of training records for employees.  The file is based on the Employee record (name, position, etc) and related to a table that contains their training records. The training records table has different types of training, some with expiration dates.

      What I need to do is to be able to run a report that shows the most current date for that particular training, grouped by training name with the employees name on it. For example:

       

      Training on :           Name:                Expiration date:

      CPR                        PersonA           01/14/2013

      CPR                        PersonA           01/14/2011

      CPR                        PersonB           02/13/2013

      Driving                    PersonB           02/14/2015

      CPR                        PersonB           03/15/2016

      Report would end up finding the most recent date,  by Training type for each person, and grouped by training type. So, the end result based on the above would look like:

       

      Training on :           Name:                Expiration date:

      CPR                        PersonA           01/14/2013

      CPR                        PersonB           03/15/2016

      Driving                    PersonB           02/14/2015

       

      I've tried using "Last" and "Max" functions but not getting the results, it still displays everything. I am not sure, do I need to accomplish this through another relationship type?

       

      Thanks, for any help, I appreciate it.

      Karen

        • 1. Re: Finding most recent date of training item by employee and training type
          SteveMartino

          Can you show your report layout in layout mode, or provide a sample file?

          • 2. Re: Finding most recent date of training item by employee and training type
            philipHPG

            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.

            • 3. Re: Finding most recent date of training item by employee and training type
              richardsrussell

              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:

               

              ER Diagram for Training.jpeg

               

              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:

               

              Initial Socket.jpeg

               

              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.

              • 4. Re: Finding most recent date of training item by employee and training type
                Karend440

                Thank you for all your responses, I really appreciate it. To reply to some questions, below is how the relationship structure is set up:

                Screen Shot 2016-08-19 at 1.28.34 PM.png

                 

                And below is how the report is set up:

                Screen Shot 2016-08-19 at 1.27.07 PM.png

                 

                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
                Karen

                • 5. Re: Finding most recent date of training item by employee and training type
                  philmodjunk

                  What's in your "SOP" table, one record for each type of training course?

                  • 6. Re: Finding most recent date of training item by employee and training type
                    Karend440

                    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.

                    • 7. Re: Finding most recent date of training item by employee and training type
                      philmodjunk

                      Then consider this additional set of Table Occurrences and relationships:

                       

                      Employees--x--<SOPs|All--[x]---<TrainingRecord|bySOP

                       

                      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.

                      • 8. Re: Finding most recent date of training item by employee and training type
                        richardsrussell

                        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.

                        • 9. Re: Finding most recent date of training item by employee and training type
                          Karend440

                          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