Best way to give an example is to use the actual database instead of trying to simplify. It may be harder to spell out the key details in your post, but the danger here is that I may suggest a solution that works perfectly with your example that fails miserably when applied to your actual project.
If I understand correctly, when you select a "color" in gcolor, all records where even one record of a given "fruit" is of that specified color should be omitted from the portal...
Your basic portal would appear to require this relationship, but I could be wrong:
FruitColor::anyField X AllFruitColor::anyField
Either that or you have:
FruitColor::Fruit = SameFruitFruitColor::Fruit
Either has the same issue with specifying a color and omitting all fruit if even one FruitColor record for that fruit matches the color.
FruitColor would be the layout's table occurrence and the second table occurrence would be the portal's TO of the same table to get your self join as specified.
Working from first possibility, you can set up this set of relationships all on the same table:
FruitColor::gColor X AllFruitColor::AnyField
ALLFruitColor::Fruit = SameFruitFruitColor::Fruit
You can define a calculation field, cColorList as List ( SameFruitColor::Color ) and set it to evaluate from the context of AllFruitColor. (Drop down at top of specify calculation dialog.)
Then this filter expression will take the value in gColor and filter out all fruitColor records were at leaste one record of that fruit type is the specified color:
IsEmpty ( FilterValues ( AllFruitColor::cColorList ; FruitColor::gColor ) )
Thanks for your answer. Yet, due to my newbieness in the filmekaer world I only understand half of what your saying, but maybe that's because of the weird example that I gave.
I'm posting the real database in this post and maybe things'll become a bit more clear.
The first table is the staff table
the second table is the staffshifts table
the layout connected to the second TO of the shifts is meant for my boss when people call in sick. He can specify a name and a day and the portal spits out people who don't have that name and are not working on that day so they can fill in for the person who owns that name and works on that day. As you can see, when you select Nick and Tuesday it spits out two "mikes" but i don't want those Mikes since mike already works on tuesday (it only omits the record with mike and tuesday, but not the records with mike and monday and mike and wednesday).
Maybe this explains it a bit better.
Yes, I do know what a TO is, but I'm just not FM enough to understand your solution :)
Just searching for a good starting point on which to start filling in more detail. Knowing that you know saves me the trouble of explaining what they are and how to create them. I don't have time to download your file now, but will check back later to see if you still need help and will look at your file then...
Thank you very much :)