13 Replies Latest reply on Jun 14, 2013 5:28 AM by JimMac

    Find greatest value in related table

    skineedog

      Title

      Find greatest value in related table

      Post

            

           Fairly inexperienced with FM & databases in general so please bear with me...

           I have 2 tables for tracking employee data. Primary table (Employees) for the employee's general info and secondary table (Status) for tracking the employee's dates that he/she was active/inactive with the company. 

           These tables are related based on primary & foriegn keys.

           In the secondary table are a status field and an date field. The employee is hired the status "Hired" is entered in the status field and the date hired is entered in the date field. When the employee is terminated a new record in the Status table is created that reflects the "Terminated" status. This allows a history to be created so that an employee's total time working for the company can be calculated.  

           One of the things I need to to is:

           Search the Status table and find the most recent date for an employee's status and return that status. So, if the employee has come and gone several times I need to know what his current status is and the date of that status. 

           When I created the relationship between the tables I sorted the status table by the status date in decending order. This retuns the most recent status when callled from a form but I need to create a report which will return the most recent status for each employee. This dosen't seem to work the way I expect.

           Another thing I'd like to do is:

           Calculate all of the time the employee has been active. This would entail counting the time between each period of hired to terminated, hired again to terminated again, to hired to still active. This employee may have worked a total of 2 years over a period of 5 years and has recently begun working for us again say 3 months ago. This should add up to a total of 2 years & 3 months since he was first hired 5 years ago.

            

           I hope this makes sense and that someone can provide some insight & guidance.

        • 1. Re: Find greatest value in related table
          JimMac

               Frirt Question...

               Create a Portal on a Layout using Employee table as reference.  In creating the portal, it wil ask you a Sort order and create just one row.  The most recent Status will appear in the portal.

               Second...

               Create a Calculations Field in the Status Table  as, say DaysHired.   This will get you started...

               DayHired = EndDate - StartDate.

               Create a Summary Field in Status Table as, Say TotalDayWorked.....

               Point that new Field to Total Days Hired.

               Remember... if Status is Hired... Current Date is EndDate.wink

               JIm...

          • 2. Re: Find greatest value in related table
            philmodjunk

                 First Question:

                 Since the relationship is already sorted, you don't need a portal. If you can set up a form to show the most recent status info, the same method can be used for a report. I think you need to spell out more detail about what you tried to do that didn't work so we can spot what went wrong for you.

                 2nd Question

                 Since each status record marks a change in status (hired or terminated), You need a relationship that matches to all status records for the same employee that have a date > greater than the date of the current record. This will allow you to set up a calculation that references the date of the next status record for that employee.

                 Status::_fkEmployeeID = Status|Next::_fkEmployeeID AND
                 Status::Date < Status|Next::Date

                 And this would also be sorted in descending order by Status|Next::Date

                 Then this calculation could be set up:

                 Case ( Status = "Hired" And Not IsEmpty ( Status|Next::Date ) ; Status|Next::Date - Date ;
                             Status = "Hired" ; Get ( CurrentDate ) - Date )

                 Then a Sum function in a calculation field defined in Employees can sum this field from Status to get the total days worked. Additional work would be needed on that calc to convert days worked into Years, but this should give you a basic idea how it might work.

            • 3. Re: Find greatest value in related table
              JimMac

                   @Phil  I read this as a report needing...

                   

              but I need to create a report which will return the most recent status for each employee.

              They have the relationship.  Pick the pkEmployeeID and the portal shows "The most recent" since is was sorted chronlogically, Status.

              Then in the Status table, do the Summary and Calc's and wah lahhhh all is ready to print all they askwink

              DaysHired = If[ IsEmpty(EndDate); Get(CurrentDate) ; EndDate] - BeginDate  to be clearer.

              TotalDayWorked = Total of ( DaysHired )   since the portal will contain all Status Entries for that EmployeeID.

              If they needed a List of All Employees... do that portal and TotalDaysWorkded on the List Report lineyes

              Jim...

              • 4. Re: Find greatest value in related table
                skineedog

                     First Question; What went wrong:

                     In a form layout based on the employee table I have a calculated field "c_status" which when searched upon for a status of "Hired" returns the correct number of active employees, 89 of 457. 

                     In a table layout based on the status table if I search for the same term in the same field the search returns 106 active of 457 employees. 

                     Not sure why this is but I think one issue may be that I have been trying to create the report from the status table and maybe I should create the report from the employees layout.

                     Is there a way to upload my db? I'm afraid that I am not clearly relaying what I'm after and I don't quite follow either of your directions. 

                     I've attached an image of the tables in question and the relationsonship.

                • 5. Re: Find greatest value in related table
                  philmodjunk

                       What I see in your screen shot matches my understanding of your design from your very first post.

                       

                            In a table layout based on the status table if I search for the same term in the same field the search returns 106 active of 457 employees.

                       When you search on that layout, you are finding status records, not employee records and you have multiple status records for each employee.

                       

                            maybe I should create the report from the employees layout.

                       If you want to list each employee once with data from their most recent status record, use a layout based on Employees instead of status.

                  • 6. Re: Find greatest value in related table
                    JimMac

                         @Phil  good pick up that I missed.

                         @Skineedog  To greatly simplify your DB I would add two date fields to the Status Record.  EndDate and BeginDate 

                         Then your Status is Automatic by Calculation like...

                         Status =  If[ IsEmpty(EndDate) ; "Hired" ; "Separated" ]

                         Then...

                         I would do what I suggested ...

                          

                    DaysHired = If[ IsEmpty(EndDate); Get(CurrentDate) ; EndDate] - BeginDate  to be clearer.

                    TotalDayWorked = Total of ( DaysHired )   since the portal will contain all Status Entries for that EmployeeID.

                    All done without complex relationships...wink

                    Jim...

                    • 7. Re: Find greatest value in related table
                      skineedog

                           I want to thank you both for assisting me with this...

                           I've managed with your help to create the list of employees and thier corresponding status. From this I understand how to generate the report of current employees.

                           Second question:

                           I think Phil understands what I am attempting to do and Jim may also. The issue is that I understand Jim's solution less than I do Phil's which is not very well...

                           Phil, I think I have created the relationship as you suggested. I've attacehd a screenshot. The problem I am having is that I'm not sure where or how to setup the calculation "Case ( Status = "Hired" And Not IsEmpty ( Status|Next::Date ) ; Status|Next::Date - Date ;
                                                                   Status = "Hired" ; Get ( CurrentDate ) - Date )" 


                      I'm thinking custom function? If I am correct in this assumption, how would I set this up and from where would I call it? A new field? Which table should this new field be placed?

                      Please excuse my ignorance... 

                      Jim: An employee could be Active (previously referred to as Hired), Terminated, LOA, or FMLA. 

                      If I followed your guidance, how would I write that calculation?

                            

                      Thanks again to you both!!

                            

                      • 8. Re: Find greatest value in related table
                        JimMac

                             LOL, when I step on Phil's toes, he kicks me in the Shins.  Phil is much better than I am, but every once in a while I even impress him.surprise

                             My first thought was, you were creating a new EmployeeID record each time they were "Rehired" which made the DaysHired a simple matter.

                             As to a calculation of status  for Leave of Absense or Family LOA, my Date fields won't separate them.sad. I don't think Phil's Case statement will either.  The dates can still work for "Active" versus "Separated" but not why they left.  If you keep it that simple, no problems.  Then you would have a field for "Reason Separated" with a radio button control of Value List.... Fired   LOA   FMLA.

                        Status =  If[ IsEmpty(EndDate) ; "Active" ; "Separated" ]

                        ReasonSeparated = O Fired  O LOA  FMLA  as radio buttons.  Pick one.

                             Phil, is using the "Masters" method which is really cool way to do AutoFinds.yes  We both understand what you are doing.

                             All I did was combine Status|Next and Status into one "Hiring Event" record.  Thus each record was an "open and closed" book

                             Jim...

                             PS: i only stepped on Phil's little toe this time.angel

                              

                        • 9. Re: Find greatest value in related table
                          philmodjunk

                               I had indeed assumed that there were only two status values possible, hired or terminated, but the case function should still work because it assumes this pattern to the status records.

                               Hired
                               Terminated, LOA, or FMLA
                               Hired
                               Terminated, LOA, or FMLA
                               HIred

                               As long as you have that pattern to the related records in your status table, the "hired" records "start the clock running" and the next related record stops it. Then a sum function in employees can total up the computed intervals in the "hired" records.

                               But maybe you have a 4th status as a person returning from "Leave" isn't normally recorded as "Hired". If you have some kind of "Returned" status, then the case function will need to be modified to compute an inteval when the staus is either hired or returned.

                               And this case function is not a custom function, just a standard calculation field.

                               Hey Jim! I'd never deliberately kick you in the shins! surprise But I might kick dirt on your shoes a time or two... wink

                               And Jim's method can be set up to work--I went with the approach that I did as it did not appear to require any major design changes to your existing tables/relationships/layouts.

                          • 10. Re: Find greatest value in related table
                            skineedog

                                 I was able to get Jim's method to work but not Phil's. Honestly like Phil's idea a bit better because it seems cleaner... But, what do I know? 

                                 So... I've realized there are a few problems with the direction I have been heading in... time to sudy up a bit on database design & Filemaker.

                                 What woud be your recomendation when designing this database to enable me to track the employees combined days worked?

                            • 11. Re: Find greatest value in related table
                              JimMac

                                   I like to think about the "paper" method that most commonly used, because that method was developed over time.  Then encode a similar FMP system.

                                   I would think what details you need first.  Your last was a broad detail look at total days employed versus a fine detail of actual work days.  In a year of 365 days, acutual work days may be only 240 days of 8 hours.  Did you need Time cards?  Weekly schedules, overtime, Sick days, holidays... etc...

                                   Phll's was a record for each event of hire, fire, loa... etc then sort and match records.  Mine was one record for each "in and out". No need to match them up, but Phil directly answered what you asked in the first post.

                                   So...

                                   Define a little more detail of your desired Output and then we can better help.

                                   Jim...

                                    

                              • 12. Re: Find greatest value in related table
                                skineedog

                                     Each position an employee may hold has a base pay rate & a differential is added to that base pay rate according to the number of years the employee has worked for the company: 5 years=hourly increase of base hourly, 10 years=another increase. Days off, vacation, etc doesn't count against this time. As long as he is employed by the company the time is counted.

                                • 13. Re: Find greatest value in related table
                                  JimMac

                                        A way might be...

                                       1) Employee Info Table   Primary key EmployeeID...example fields, Address, Phone, civil status, children, Emergency Contact, gender... etc

                                       2)Table of Separations Events    Foreign Key EmployeeID  ... Separtions records as we discussed before

                                       3) Table of Pay Events   Foreign Key EmployeeID  .... Promotional events, Title, Time in Service bonus , Base Pay Rate, total pay, year to date pay.

                                       This is not a Payroll,but a Human Resourse Info type approach.

                                       A new record would be issued for each EVENT type.  Then working with the records will produce, summaries and history.

                                       Jim....