5 Replies Latest reply on Jan 26, 2015 7:33 PM by philmodjunk

    Looking for the unique

    amtrakpdx

      Title

      Looking for the unique

      Post

      Dear Wizards,

      I have a table with FldA and FldB.  In a script, I perform a find on FldA for one value.  While the number of records is now less, FldB has a great deal of duplicated values.  What is the next process I use to create a final found set that has only the unique (non-duplicated) values in FldB?
      Thanks

        • 1. Re: Looking for the unique
          philmodjunk

          And what happens after that?

          If you just need a report with one row for each unique value in Field B there's a simple way to do that by setting up a sub summary layout part "when sorted by B" and removing the body layout. This method requires sorting your records by Field B before you get the desired results.

          • 2. Re: Looking for the unique
            amtrakpdx

            If I do a find on FldA based on 'Orange' the result of FldB is going be different than if I do the FldA find based on 'Apple'.
            Orange in FldA may yield BBBBBDDEEEEEEGGG in FldB records.  My desired final table would be four records with FldA with all 'Orange' and FldB being B,D,E,G.
            Apple in FldA may yield AABBBBBCCCCGGGFFFF in FldB records.  My desired final table would be five records with FldA with all 'Apple' and FldB being A,B,C,G,F.
            My end goal was to have a TO with only the unique records of FldB but based on the results of the FldA found set.
            I'm not that well versed in sub-summaries but it sounds like that might be a solution; I just need to learn how to script the variables into the sub-summary parameters.
            Thank you for any further guidance and/or clarification.

            • 3. Re: Looking for the unique
              philmodjunk

              I don't quite see why you would need a table occurrence for this.

              BTW, there are ways to filter out duplicates in portals and ways to use a calculation field that lists just the unique values of B all in a single filed as well as ways to import the data into a new table dropping out the duplicates while doing so....

              • 4. Re: Looking for the unique
                amtrakpdx

                I like what you're suggesting regarding filtering out duplicates in a portal.  Can you point me to a discussion that may help?

                Thanks,
                Sam

                 

                • 5. Re: Looking for the unique
                  philmodjunk

                  Don't have thread reference for that one.

                  Option 1:

                  Set up a table with only unique records (can be initially set up with an import records operation that filters out duplicates during import). Use this table as a "filter" in between the layout's table and your table of data with duplicated records. You use a global field (or set of global fields) defined in this filter table and set by a script performed by the OnRecordLoad trigger to "reach through" the filtering table to just those that you want to see in your report.

                  Example:

                  LayoutTable-----X-----<TableOfUniques-------<TableofDuplicates
                  Layouttable::anyFIeld X TableOfUniques::anyField
                  TableOfUniques::gDate > TableOfDuplicates::DateField AND
                  TableOfUniques::Name = TableOfDuplicates::Name

                  Your portal points to TableOfUniques, but calculation fields can reference data in TableOfDuplicates for all records dated the same or later than the date specified in gDate. There are a number of different options where gDate is just one example.

                  OR

                  You can keep an existing relationship in place, but use a self join relationship in combination with a portal filter to omit duplicates. Say it's the text in a field called "Name" that is duplicated:

                  LayoutTable----<PortalTable-----<PortalTable|SameName

                  There must be a field, __pkPortalTableID defined to auto-enter a serial number so that each record in the table has a unique ID

                  PortalTable::Name = PortalTable|SameName

                  Sort this relationship by __pkPortalTableID in ascending order.

                  Set up this portal filter expression:

                  PortalTable::__pkPortalTableID = PortalTable|SameName::__pkPortalTableID

                  This filter will evaluate as True only for the oldest record for a given value in Name and thus the duplicates are dropped out.