I would appreciate advice regarding portal searching. I am providing a simplified example to facilitate discussion below. I have built several related tables with primary and foreign keys. I also have a primary data entry layout that contains portals.
For example, my database has a record for John, Mike, and Ed. The portal contains colors of shirts. John has red, blue, and orange, Mike has red, green, and blue, and Ed has blue, green, and yellow (no red). I search in the portal for "red." I get the records for John and Mike only, which is good. However, within the records for John and Mike, I get all of the colors for John and Mike in the portal: red, blue, orange, etc. I only want to see for John and Mike the portal results for "red."
Is this possible? If so how?
Your suggestions would be greatly appreciated. Please be as specific as possible.