Related record AND search

[RWX]

Hello everyone.  I'm getting back into FileMaker development after a long hiatus.  I'm currently developing the Search screens for my solution and am scratching my head over something I think should be obvious.  Either I'm missing something obvious or what I want can't be done.

 

I have a Projects table, a Users table, and a User Roles table.  User Roles is a join table.  I have a portal on my Project view that shows User Roles.  After the user creates a related User Roles record for the project, he or she picks a User from a pop-up menu and a Role from another pop-up menu.  Simple enough.  There can be an unlimited number of roles and users can be assigned multiple roles.  The portal might look like this:

 

Bob        Project Manager

John       Sales

Sara       Coordinator

John       Tech Support

 

I enter Find mode and want to search for the following:

 

Bob        Project Manager

John      Sales

 

How do I do this?  I'm only given a single portal row and can't seem to add a portal request here.  Is this not possible?  Can I not perform an AND search on related records like this?

 

I feel like this is rather obvious functionality, so am I missing something???  If it can't be done, can anyone offer some thoughts (or forum links) on how best to accomplish the functionality I want.  It's pretty essential that we be able to search for more than a single User/Project Role when searching projects.  We often want to pull up all records where Bob is the PM and John is the Sales guy.  Or we might want to find all projects where John is both Sales and Tech Support, etc.

 

Grateful for all help and advice!

[Ocean West]

enter find mode...

 

Type:  Bob     Project Manager

 

Create New Find Request

 

Type:  John    Sales

 

Perform Find

[RWX]

Thanks for the response.  Maybe I wasn't clear in my question.  In this case, I want to find only project records where Bob is Project Manager AND John is Sales.  If I do as you suggest, I end up with all records where Bob is Project Manager OR John is Sales.

[Lee Smith]

Put all four criteria on one request.

[RWX]

But that's my question, Lee.  If the User Roles are a portal, how do I add a second (or third, or fourth...) portal row to the find request so that I can put all of the criteria on one request?

[Ocean West]

Enter Find Mode

Bob + Project Manager > Perform Find

 

Enter Find Mode

John + Sales >  Constrain Find From Requests Menu

 

FMF_Search#88368.fmp12.zip

 

 

Edited April 25, 2013 by Ocean West
new file

[RWX]

Thanks Ocean.  I figured out that this could be accomplished with a constrained find, but was really hoping that wasn't necessary.  That means going in and out of find mode multiple times to perform what should be a simple search, but at least it works.  Thanks to all of you for your help and advice!

[MartieH]

if you dont' want to go in and out of find mode, you might create a new little window just for the find - which uses a layout based on the portal TO - then, you can enter all the find requests at one time - find them and close the window.

 

Martie