5 Replies Latest reply on Jun 21, 2013 3:12 PM by philmodjunk

    Return most recent date is a list

    AndrewHarms

      Title

      Return most recent date is a list

      Post

           Hello,

           We have a number of employees in a list that I woudl like to return the most recent date they worked. 

           Forexample, we upload into a big table all of the hours. Then have them breakdown to separate stores so we know who is working where and how much etc...

           What i Would liek to do is figure out a way to insert a columb that pulls the last day each employee worked. THis would help find out who quit without telling us etc...

            

           Almost like max or min but have it match an employee ID number.

        • 1. Re: Return most recent date is a list
          philmodjunk

               And how is this data structured? What tables and relationships do you have in place?

          • 2. Re: Return most recent date is a list
            AndrewHarms

                 Hello Phil,

                 There is a main table with fields like: Store number, address , phone #,  store manager etc...

                   
            1.           Separate personnel record table has fields like: Employee Name, Employee Phone #, Store Number assigned to, etc...
            2.      
            3.           Separate hours import table has fields like: Employee ID #, Date, Store number, Start time, Finnish time, Shift Total
            4.      
            5.           Relationships: The hours import links store number (hours import table) with store number (main table) and places all found records into corresponding stores via a portal within each stores (main table) record. The portal also looks at matching employee numbers (Found in Personnel Record Table) and places their name in the portal as well. 
            6.      
            7.           The employee list lists all employees in a separate list. This list shows: Store # they are assigned to, Their Status, ID#, Name, Position, Manager.

                 What I am trying to do is find a way for File Maker to calculate or find the most recent date a specific employee worked. I would like to insert a new column named "Last Day Worked" so we can easily sort through the list to find active employees who haven't worked for a while. We could then separate those employees from us and keep records up to date.

                 The "Last Day Worked" field would have to match employee ID# 888 in the Personnel Table to Employee ID # 888 in the Hours Import Table and calculate or return  the most recent work date.

                 Example: Emp ID# 888 worked:

                   
            •           05/05/13
            •      
            •           05/08/13
            •      
            •           05/09/13

                 I want it to return 05/09/13.

                 Sorry for the long winded explanation... Hope it helps everyone. Thanks for your input.

                  

                  

            • 3. Re: Return most recent date is a list
              philmodjunk

                   Presumably you have a relationship that matches the employees table to Hours by Emp ID#. If you specify a sort order in this relationship that sorts Hours by date in descending order, then a reference to Hours::Date from the context of a specific Employees record will return the most recent date that employee worked.

                   If, instead, the relationship is not sorted and the records in hours are created/imported in the order that the employee worked (the last related record then is the most recent date they worked), then Last ( Hours::Date ) would return the most recent date that the employee worked.

              • 4. Re: Return most recent date is a list
                ninja

                     Outside of the need to sort...the MAX() function may serve you as well

                • 5. Re: Return most recent date is a list
                  philmodjunk

                       Good point. The key difference is that Max just returns the date--and that might be all you need. The sorted relationship gives you access to all the data in that specific hours record.