7 Replies Latest reply on Feb 2, 2014 9:36 AM by Joseph_1

    Show Unique Values on found set, using constrain

    Joseph_1

      Title

      Show Unique Values on found set, using constrain

      Post

           Hi All

           I was wondering if there is a way to show on a found set only unique values and not also duplicates

           e.g:

           My Found set has, name and oerder are different fields

           Name: John Smith, Order: 23

           Name: Sam Smith, Order: 21

           Name: Nick Smith, Order: 24

           Name: John Smith, Order: 27

           Name: John Smith, Order: 29

           Name: Nick Smith, Order: 20

            

           After constraining I expect the found set only to be 3 records:

           Name: John Smith, Order: #

           Name: Sam Smith, Order: #

           Name: Nick Smith, Order: #

        • 1. Re: Show Unique Values on found set, using constrain
          philmodjunk

               Take "John Smith" in your example. Your data shows Order numbers of 23, 27 , 29

               Once you have just one row for "John Smith", what value should you show for Order #?

               What version of FileMaker are you using?

          • 2. Re: Show Unique Values on found set, using constrain
            Joseph_1

                 Hi Phil thanks for answering. 

                 The order number won't really matter I just want the found set to show unique names even is the person has many orders 

                  

                 thanks again 

            • 3. Re: Show Unique Values on found set, using constrain
              philmodjunk

                   The simplest is to have one table for customers and one for their orders. Then you can get your "unique list" automatically from the customers table.

                   But to get what you want from the data you show...

                   Take a layout based on this table and change the body layout part into a sub summary layout part. Select "when sorted by name" for this sub summary layout part.

                   Put your name field in this sub summary layout part.

                   Now sort your records by name and you'll get each name listed only once. (In Browse mode, sub summary parts are not visible if your records are not sorted by by the "when sorted by" (Break) field.)

                   Note: Customers with identical names will be grouped into a single entry due to sorting by name. Sorting by a customer ID # would eliminate that issue.

              • 4. Re: Show Unique Values on found set, using constrain
                Joseph_1

                     Thank you, 

                     But I think my question wasn't clear enough, I have a script that returns a found set of all the records above, I need to be able to constrain this found set to only unique people to later apply another script to this individuals.

                     No without the constraint my second script repeats several times for the individuals,

                     e.g:

                     After second script:

                Name: John Smith, 

                Check

                Check 

                Check

                     Name: Sam Smith

                     Check

                     Name: Nick Smith

                     check

                     check

                     And my idea is to have only one check per Name.

                     I hope I could be more clear.

                     Thanks

                      

                • 5. Re: Show Unique Values on found set, using constrain
                  philmodjunk

                       This really sounds like a situation where you need a related customers table of one record per customer. A looping script could sort and omit duplications though you still have a problem where different customers can have exactly the same name.

                  • 6. Re: Show Unique Values on found set, using constrain
                    philmodjunk

                         Instead of using a found set, you may be able to produce a list of distinct values using ExecuteSQL and the DISTINCT key word. You can likely adapt your second script to loop through the list of values produced by that method instead of a found set of records.

                         This requires FileMaker 12 or newer.

                         And with older versions, your looping 2nd script can skip duplicates to create only one check per name.

                    • 7. Re: Show Unique Values on found set, using constrain
                      Joseph_1

                           I am currently using FM12,

                           but could you elaborate more about the  "looping script could sort and omit duplications" you mentioned. How and which commands to use to omit duplicates?