9 Replies Latest reply on Jan 12, 2015 9:02 AM by pmehta91

    Show Unique Records only In a found set script

    pmehta91

      Title

      Show Unique Records only In a found set script

      Post

      Hi 

      In a found set I have all the records that match the search I have set. However, each record layout has a portal within it. E.g. A company sells pens, but in the portal it lists pens twice as they are from different brands. 

      In the found set if a search for pens was made, it shows the company twice in the found set. I want the company to only appear once.  Please note the fields in the results page is from two different tables, e.g. the name is from one table (which is the uniqueid) and the email address / phone number are on another table. 

      I have tried to constrain the found set with a ! but still no luck. 

      Any suggestions?

        • 1. Re: Show Unique Records only In a found set script
          philmodjunk

          ! Finds duplicates, it can't be used to omit them unless you want all instances of that record that is a duplicate omitted.

          I can't quite picture why you have duplicate company records in your found set in the first place. Duplicate product TYPES (such as your pens example) makes sense, but not duplicate company records.

          And using a company name as your unique ID (which does not appear to actually be unique) in your portal's relationship is not a good idea. Company names are neither unique, nor unchanging. Tomorrow, one of these companies may choose to change their name--possibly do to a change in ownership and then all your matches by company name "break" if you update the company name in one table without exactly updating the same field to the same value in the other table. There are better ways to link the records that avoid this issue.

          In some cases, you can hide the duplicates if you sort by the field that has duplicate values and change your body layout part to a sub summary layout part "when sorted by" this same field. That may, in your case, introduce new problems to your display of this data.

          • 2. Re: Show Unique Records only In a found set script
            pmehta91

            I have duplicate companies in the results because I have a portal in the table "products" which is a portal. A company can have more than one type of "pen" product depending on the brand etc, hence why the company is appearing more than once. The search results table is from the "products" table because I have set a hidden text called "preferred", for example if a type of product is the preferred pen, a message in red saying "preferred" would appear. 

            Hence why I just want the the company to appear once on the results

            • 3. Re: Show Unique Records only In a found set script
              philmodjunk

              Can your search results list products from more than one company at a time?

              If so, you can sort your records by Company and place the company field inside a sub summary layout part "when sorted by" the same company field that you used to sort your records and group them by company.

              If you only show product records for one company at a time, you can place this field in the header of your report to show it only once.

              • 4. Re: Show Unique Records only In a found set script
                pmehta91

                Hi, yes it can list products of more than one company at a time. 

                However, I am not quite too sure what you mean by a sub summary layout? 

                Thanks for your help I appreciate it

                • 5. Re: Show Unique Records only In a found set script
                  philmodjunk

                  While in Layout Mode, select Part Setup from the Layout Menu. Use this dialog to create a new sub summary layout part and select your Company field as your "when sorted by" field. Make sure that your script that displays your find results includes a sort records step that sorts your records by this same field.

                  Sub summary layout parts are not visible when the found set's sort order does not include that part's "Sorted by" or "Break" field.

                  • 6. Re: Show Unique Records only In a found set script
                    pmehta91

                    Hi

                    Yes done this, but it still displays the company twice in the results page, e.g. same company appears twice when pens is searched for. 

                    • 7. Re: Show Unique Records only In a found set script
                      philmodjunk

                      Then you either have the company name in the wrong part of your layout or you have not set up the sub summary layout part and sorted correctly. The company name field should not be in the body, only in the sub summary layout part so that it appears only once for each group of product records and since each group of product records  represents one company, you should then see the company name only once.

                      • 8. Re: Show Unique Records only In a found set script
                        pmehta91

                        Would this work if the layout is a "List" view?

                        • 9. Re: Show Unique Records only In a found set script
                          pmehta91

                          Thanks for your help, managed to sort it atlast!