Displaying latest/earliest record from a linking table
I am fairly new to FileMaker. My database has three tables:
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?