And how is this data structured? What tables and relationships do you have in place?
There is a main table with fields like: Store number, address , phone #, store manager etc...
- Separate personnel record table has fields like: Employee Name, Employee Phone #, Store Number assigned to, etc...
- Separate hours import table has fields like: Employee ID #, Date, Store number, Start time, Finnish time, Shift Total
- 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.
- 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:
I want it to return 05/09/13.
Sorry for the long winded explanation... Hope it helps everyone. Thanks for your input.
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.
Outside of the need to sort...the MAX() function may serve you as well
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.