3 Replies Latest reply on Apr 25, 2014 10:17 AM by philmodjunk

    Searching only most recent related records

    FileMakerNovice

      Title

      Searching only most recent related records

      Post

           I've been pecking at this for a while.  I have a table of employees with their schedule, location, name, email etc.  Additionally, I have a table related by an id that has their current projects.  This table is historic so there is a one to many relationship of Employees to History.

           I am wanting to search for only the most recent project.  As it stands, when I do a search, it returns all the people who have ever done that project rather than just ones who are currently on it.

           In the layout it has a popover (FM 13 btw) that shows the history.  So, I do want to have the history viewable in this layout but I want to be able to search for a list of employees currently on a project.

        • 1. Re: Searching only most recent related records
          philmodjunk

               What changes in your data to show that an employee, while still linked to that project record is no longer "on that project"?

          • 2. Re: Searching only most recent related records
            FileMakerNovice

                 Man, you just hit a the nail on the head and I think you got something there.  At the moment, there is not a distinguishing value that tells that the employee is no longer on the project.

                 I do however have a start and end timestamp.  If it is the most recent project, the end timestamp is blank.

                 So... should I create another relationship to relate the history to the employee through the ID and maybe a global value.  The global value would be related to something like History::isNewest = "if enddate="", 1".  Or am I taking this the wrong route?
                  

            • 3. Re: Searching only most recent related records
              philmodjunk
                   

                        I do however have a start and end timestamp.  If it is the most recent project, the end timestamp is blank.

                   And in one table do those fields exist? It doesn't seem like this would work for either the Employees nor the Projects table, so I must assume that you have a third related table, your "history" table, that is linked to both.

                   You could set up a calculation field in History as:

                   IsEmpty ( EndTimeStamp )

                   with number selected as the result type. (And clear the "do not evaluate if all referenced fields are empty" check box.)

                   Then, you can set up a field in Employees that is always= 1. This can be a stored calculation field where a lone 1 is the sole term.

                   And then a relationship to a new table occurrence of  history can be:

                   Employees::EmployeeID = History|Current::EmployeeID AND
                   Employees::constOne = History|Current::cCurrentProjectFlag

                   constOne can be your field that is always 1 and cCurrentProjectFlag can be your IsEmpty calculation.

                   You could also use this relationship:

                   Employees::EmployeeID = History::EmployeeID

                   And use a filtered portal with this expression:
                   1 = History::cCurrentProjectFlag

                   To show the same record(s).