[schwa]

FileMaker gurus-

I've got a FileMaker db that uses a portal to another db to show the salary history of an employee. If I do a Find using the portal fields (e.g. anyone with a salary over X, or anyone who got a raise in 2002), it returns matching records in the master db, but it returns *all* related portal rows for that master record, not just the relevant ones.

For example, if my record has three salary history entries: $50k on 1/1/01, $60k on 1/1/02, and $70k on 1/1/03, and I run a search to see what everyone was earning in 2002, I only want to see the $60k record. FileMaker will return my master record (since I have a matching portal row), but show all three portal rows.

Any advice?

[kennedy]

take your question to fmforums.com ... you'll get a good answer there.

[chadbert]

That's expected and desirable behavior; technically, you're searching for employees, not salary histories, and once the matching employees are found you get all of their data, including all related records.

There's no way to do what you want as part of a search, exactly, but you can create filtering portals. You'll need to create a global field in your parent (employee) file to indicate the year you want to see, and calculated keys in both files (in the employee file, concatenate the global and the employee id, in the salary history file do the same for the salary year and the employee id) and base your relationship on those calculated fields. There's additional steps to make it possible to still see multiple years in the portal. Try databasepros.com for downloadable examples.