4 Replies Latest reply on Nov 16, 2011 12:31 PM by JohnCarpmael

    Trying to exclude duplicate records in a portal in FM 11

    JohnCarpmael

      Title

      Trying to exclude duplicate records in a portal in FM 11

      Post

      I hope someone can help me with this. I have a solution that contains a portal which for various good reasons shows a number of duplicates. So, for example it might have:

      White

      White

      Gray

      Yellow

      Blue

      Blue

      And I want it to show:

      White

      Gray

      Yellow

      Blue

      I am hoping to do this using the filtering option. The duplicates do not need to be deleted, just not displayed. Any thoughts on how I can do this ?

      Many thanks in advance !

        • 1. Re: Trying to exclude duplicate records in a portal in FM 11
          philmodjunk

          You'll need to use one method or another to "mark" the records with duplicate values by putting a value in a "mark" field. then you have a value a portal filter can exclude.

          Here's one method:

          Give each record in this portal a unqiue serial number in a serial number field.

          Open Manage | Database | Relationships

          Select the table occurrence for your portal and use the duplicate button (two green plus signs) to make a duplicate of this table occurrence.

          Use it to define this relationship:

          PortalTable::ColorField = PortalTable 2::ColorField

          Now define a calculation field, cMinID, in this table as: Min ( PortalTable 2::serialNumberField )

          Now this portal filter will drop out duplicates:

          IsEmpty ( PortalTable::cMinID ) or PortalTable::cMinID = PortalTable::SerialNumberField

          This should display all records that do not have a duplicate color ( IsEmpty function) or which are the record of a set of duplicates with the smallest serial number (Current serial number matches minimum value with same color).

          • 2. Re: Trying to exclude duplicate records in a portal in FM 11
            JohnCarpmael

            Thanks for the really swift answer PhilModJunk !

            But I'm afraid that I didn't give you enough information. The records on the PortalTable already have unique serial numbers and the colours are not duplicated in that table. They appear multiple times in the portal because the Portal TO is 4th in line from the originating TO. So the portal looks like this:

            11 White

            11 White

            14 Gray

            16 Yellow

            20 Blue

            20 Blue

            And I want it to show:

            11 White

            14 Gray

            16 Yellow

            20 Blue

            Your elegant answer doesn't seem to help in this case. I'm hoping you may have another answer now that I have been a little clearer !

            Again many thanks.

            • 3. Re: Trying to exclude duplicate records in a portal in FM 11
              philmodjunk

              Please post a screen shot of the Relationship Graph or use text to document all the relationships.

              • 4. Re: Trying to exclude duplicate records in a portal in FM 11
                JohnCarpmael

                Apologies, PhilModJunk ! It works beautifully !

                Many thanks.