6 Replies Latest reply on Jan 30, 2012 3:58 PM by MickeyHoyle

    Portal filter omit multiple records with same id



      Portal filter omit multiple records with same id


      Let's say I have two tables

      one with fruit:

      id   fruit name

      1    apple

      2    orange

      3    pear


      One, related to the fruit table with the color of the fruits.

      id   fruit name   color

      1    apple          red

      1    apple          red

      1    apple          green

      2    orange        orange

      3    pear            green


      I now want to create a portal filter in a self joined table that omits fruits that have a certain color, which is chosen in a global search field. The portal filter is setup like this Fruitcolor::color ≠ Fruitcolor_2::gcolor

      If i enter green it will not show me the green pear, nor will it show me the green apple. What it will show me are the orange orange and two red apples. My problem is, that I don't want to see any apples at all since that one green one ruined my entire batch. Same goes for red, if I enter red in the searchfield, I don't want to see any green apples either since they'll probably be contaminated with the other apples redness. (sorry for the incredibly weird example, it was the easiest way for me ).

      How can I do this?

        • 1. Re: Portal filter omit multiple records with same id

          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 ) )


          • 2. Re: Portal filter omit multiple records with same id

            Hi PhilModJunk,

            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.


            • 3. Re: Portal filter omit multiple records with same id

              Do you know what a table occurrence is? If not, you may find this tutorial helpful: Tutorial: What are Table Occurrences?

              • 4. Re: Portal filter omit multiple records with same id

                Yes, I do know what a TO is, but I'm just not FM enough to understand your solution :)

                • 5. Re: Portal filter omit multiple records with same id

                  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...