2 Replies Latest reply on Feb 16, 2015 12:28 PM by Robbie_1

    Filtering Portal Records with Duplicate Values

    Robbie_1

      Title

      Filtering Portal Records with Duplicate Values

      Post

      Hi, 

      I am trying to set up a portal that filters out records that display duplicate values in a specific set of fields, displaying only one record per unique set of fields. Here's my best shot at an example of what I need to do...

      Table 1 contains master record and portal

      Table 2 contains records 1, 2, and 3

      Records 1, 2, and 3 contain fields 1, 2, and 3. 

      If Field 1, Field 2, and Field 3 are identical, I would like for the portal in Table 1 to only display Field 1, Field 2, and Field 3 once (In other words, only displaying Record 1, Record 2, or Record 3).

      In this case it does not matter if the actual record displayed in the portal is Record 1, Record 2, or Record 3. 

      Thank you in advance for the help. 

      Robbie

        • 1. Re: Filtering Portal Records with Duplicate Values
          philmodjunk

          It can actually be easier to help you with real names for your tables and fields. My answer assumes that you do not have returns in any of these three fields.

          Three options come to mind:

          Set up a table that only permits one unique combination of values from these three fields. It's possible to set up an auto-enter calculation in a 4th field that combines the values of the other three with a unique values validation. To set up your table from your existing data, you'd import your records from Table 2 into this Table 3 and the validation rule will omit duplicates. Moving forward, everytime you add or update a record in Table 2, a script could update Table 3 to match. You can then use a portal to Table 3. This option doesn't make sense just to set up a portal that omits duplicates, but you may find that this table 3 is useful for other purposes.

          OR

          Set up a second occurrence of Table 2 in a self join relationship matching records by the value of all three fields:

          Table1----<Table2>----<Table2 2

          Table2::field1 = Table2 2::Field1 AND
          Table2::field2 = table2 2::Field2 AND
          Table2::Field3 = table 2 2::Field3

          If table2 has a primarykey field (or other field that uniquely identifies each record in the table such as an auto-entered serial number field), you can then set up a portal filter on your portal that looks like this:

          Table2::PrimaryKeyField = Table2 2::PrimaryKeyField

          OR

          If you do not need to edit values in the portal, only display data from each record, you might set up a single large calculation field in place of your portal and use an ExecuteSQL() function to produce the desired rows and columns of data with the DISTINCT keyword used to drop out duplicates.

          • 2. Re: Filtering Portal Records with Duplicate Values
            Robbie_1

            That worked, thank you very much!