10 Replies Latest reply on Aug 13, 2012 10:23 AM by philmodjunk

    Need help only selecting one related record for a report

    DianeJohnson

      Title

      Need help only selecting one related record for a report

      Post

      Hello,

      I need help figuring out a way to select only the most current related record for each individual listed in a report. 

      I have a table listing each employee. I have a related table that lists their job title. In that table there is an effectie date of the job title. They may change job titles over time. 

      I need to create a report listing each employee, list only their most current job title, and sort by job title. 

      Currently my report lists each employee and each of their job titles. 

      I don't want to force the user to enter an end date for the previous job title, nor do I want to force them to check a box indicating which job title is their current one as it would require them to uncheck previous records.

      Anyone body have any ideas on a good way to approach this?

      Philmodjunk.... you are always a good idea person. Thoughts?

       

      Diane

        • 1. Re: Need help only selecting one related record for a report
          GuyStevens

          Is there a join table between the employe table and the job title table?

          Or where do you store the effective date of the job title?

          Or does your second table just contain a persons ID, a job title and a date

          You can sort over a relationship, so that could be an option. But that might only work if you are using a join table.

          My logic would be:

          - An emplyee table that contains all employees.

          - A "Job Title" table that contains all possible job titles.

          - A join table with:
          - ID
          - Employee IdFk
          - JobTitleIdFk
          - EffectiveJobDate

          I'm assuming you only enter a job title when someone actually get's the job.

          Then you can sort the relationship so that the ID is descending. That way you always have the most recent job on top.

          Then you create your report based on the Employee table and just show 1 field for the job title from the JobTitles table and you will see the most recent job title.

          • 2. Re: Need help only selecting one related record for a report
            Sorbsbuster

            DaSaint's idea will work even if you don't use a join table, but create a new Position record every time a person is moved to a new job.  Simply sort the relationship between the two tables, and show only the one record as DaSaint says.

            • 3. Re: Need help only selecting one related record for a report
              DianeJohnson

              Yes, I do have a join table. I have a table that contains the all possible job titles. The join table is called Career journey. When I add a record to that table, the user can select the job title, and enters the effective date. In the employee layout, I have a portal that lists each of the job titles and effective dates that employee held that job title. 

              In each of your respsonses, in the very last paragraph you state, Show only one record. That is the part I can't figure out. How do I create a report (list) that only shows the most current record? How do you show in a report, just one related record?

              • 4. Re: Need help only selecting one related record for a report
                Sorbsbuster

                You run the report from the Employees Table.  This will give you one record per employee (of your choice).  Include any employee detail you want from the Employee Table on the report.  Then grab the Job Title field from the Join Table relationship.  It will only be able to represent one value, and becusse you sorted the relationship by descending date it will show you the most recent.

                (Although if this is what you have:

                Employees ---<Career Journey

                then you do not have a Join Table.  I don't really see why you would need one.)

                • 5. Re: Need help only selecting one related record for a report
                  DianeJohnson

                  So, there is one more layer to this whole problem.

                  Yes, if I run the report from the Employee Table, I do in fact only get the most current related record - their current job title. 

                  Each job title is associated with a specific work zone. And their are only about 6 work zones. ie, manager, marketing, human resources, etc. I have a drop down filter that runs a script that filters by work zone. When it tries to filter, if an employee had a job title in another work zone, when that work zone is selected, than that job title is displayed, even though it is not the most current job title.

                  See where it gets tricky?

                  Initially, I was thinking there may be a way to force an end date in a previous job title record, that would indicate that it is not the current position and then filtering based on "current" status. That seems even more challenging for me to understand how to do. If I add a job title record for an employee, is there a way to go to a previous record and calculate an end date based on the start date of the record I am adding. That ultimately would solve my problem I think. But I don't know how to do that either.

                  Ultimately, I suppose I could ask the user to ensure they select with a check box which job title is current, but that is something that could get easily overlooked and a royal pain to maintain. 

                  I have spent too much time thinking about and now just can't seem to figure out a solution.

                  • 6. Re: Need help only selecting one related record for a report
                    philmodjunk

                    I have a drop down filter that runs a script that filters by work zone.

                    It might help to share that script.

                    WHere is the "work zone" recorded? If it's in the same table as the job title, then you can get your script to filter correctly by work zone by adding a calculation field to the employee table that simply refers to the work zone field in the related table. Then put your find criteria in this calculation field instead of the field from the related table.

                    When you specify find criteria in the field of a related table, you tell fileMaker to: "Find all records in the layout's table that have at least one record in the related table matching this criteria".--which is why I suspect you are specfiying find criteria in the related table. Using the calc field in employees will be telling fileMaker: "Find all records in employees where this calc field returns the specified value."--and that should only be the most recent work zone.

                    • 7. Re: Need help only selecting one related record for a report
                      DianeJohnson

                      Phil,

                      Thank you so much for the clarity, yes, I got it work now. 

                      My solution. In the Relationship, I force a descending sort based on effective date in the Career Journey table. In the Career journey table, I have a foreign key for the work zone that has a calculation using the lookup function to pull the work zone from the work zone table based on the job title in the career journey table.

                      Make sense so far?

                      In the employee table, I created a "current work zone" field that uses a calculation to display that kf_Workzone from the related career journey table. In every case, it seems to return effectively only the most current job title. When the script runs in my report to filter based on work zone, it is filtering based on the Current work zone field in the employee table. Wahlaa!

                      Now one more question.... is there any draw back to using the Lookup function in the career journey table for the kf_workzone field?

                      • 8. Re: Need help only selecting one related record for a report
                        philmodjunk

                        I think in terms of "dynamic" and "static" links when it comes to choosing whether to use a looked up value setting. The link is "static" if you use that option--any changes in the work zone table will not automatically update data in the Career Journey table, you'd have to trap for such changes and use a script to update the Career Journey table. A calculation field that references the Work Zone table is a "dynamic" link (So is pulling a field from a related table on to a layout), changes to the workzone table will automatically update in the calculation field--but you pay a price in that now this field is unstored and this may limit it's utility in relationships and can result in slower sorts and searches on this field. Thus, you have to look at your data model, your business procedures and pick the best balance between the pros and cons.

                         

                        • 9. Re: Need help only selecting one related record for a report
                          DianeJohnson

                          That is a great point. Now that i have it working, I went back and changed it from a Lookup and it still works. Whew! Moving on to the next challenge..... creating an email, a LONG email, using a script. Exciting.

                          Phil... thank you again. I can always count on you!

                          • 10. Re: Need help only selecting one related record for a report
                            philmodjunk

                            You might find this method useful for certain types of long "form letter" type emails: https://www.dropbox.com/s/6xw8buafjuohncu/MessageTemplateBuilder.fp7