4 Replies Latest reply on May 2, 2012 11:11 AM by slipstock

    Displaying latest/earliest record from a linking table

    slipstock

      Title

      Displaying latest/earliest record from a linking table

      Post

      I am fairly new to FileMaker. My database has three tables:

      Computers
      Users
      ComputerUsers

      The ComputerUsers table is a linking table that contains a foreign key to both Computers and Users and it also has an assignment date. A computer can be assigned to a user for a while and then re-assigned to someone else, so there will be multiple records in this table. I would like to display the name of the last user that the a computer was assigned to on a report.

      I have done the following:

      • Created a new table occurrence for Computers (Computers_LastUser)
      • Added a new occurrence of ComputerUsers and joined it to Computers_LastUser
      • Specified that the records in this new ComputerUsers occurrence should be sorted by assigned date in descending order in the relationship properties
      • Created a new layout based on the Computers_LastUser table occurrence

      All is well with this. If I drop the assignment date from my new ComputerUsers occurrence onto my layout then I do indeed see the last assignment date. However, if I do the following:

      • Create a new occurrence of Users and join it to my new ComputerUsers occurrence
      • Drop the username field from this new Users occurrence onto my layout so that I can see the user's name

      Then the results do not come out right. The name that appears does not match the key or assignment date from the linking table. Any idea where I might be going wrong?

       

        • 1. Re: Displaying latest/earliest record from a linking table
          philmodjunk

          Are these your relationships?

          Computers::ComputerID = ComputerUsers::ComputerID
          Users::UserID = ComputerUsers::UserID

          Computers_LastUser::ComputerID = ComputerUsers 2::ComputerID (ComputerUsers 2 is sorted by assigned date in descending order.)
          Users 2::UserID = ComputerUsers 2::User ID

          Try using a one row portal to ComputerUsers 2 and place the name from Users 2 into this portal row and see if that displays the expected name.

          • 2. Re: Displaying latest/earliest record from a linking table
            slipstock

            Yes, that correctly describes the relationships.

            The one row portal idea does work. Users 2::Username is correct in the one row portal, but the same filed dropped directly onto the layout is incorrect, even though ComputerUsers 2::AssignmentDate is correct when dropped directly onto the layout.

            So is my solution to simply use a one row portal in this case? It looks like the sort specified in the relationship between Computers_LastUser and ComputerUsers 2 does not carry through to the Users 2 occurance the way one might expect when fields from Users 2 are dropped directly onto the layout.

            • 3. Re: Displaying latest/earliest record from a linking table

              Why make it so complicated to just show the last user on a report.

              I see two possibilities:

              The first is that you are printing a one page report about one computer. In this case you only need to linke the table making the report to your computer table by any field such as computer to computer and use the X link in the popup. Drag a portal into its place and make it one line. Sort the portal by date in the order you want and there is your record.

              The second is a real report made in the user table with the link to the loaner computer ids. Create a summary report using the computer id as a summary field for computer id and one for user id and then sort by computer id and date and user id.

              This hasn't been debugged...  :)

              • 4. Re: Displaying latest/earliest record from a linking table
                slipstock

                Actually, the report is an asset list report that should show all of the computer systems that we have as well as the last known user. Some computers will not have a user at all (servers, for example).