10 Replies Latest reply on May 11, 2016 12:53 PM by abarcelo

    depurating a list


      Hi all,

      I'm working in a database that has several groups of records that have the same ID.

      I need to build a short list in which only one of the fields that have the same ID appear.

      Is it posible without scripting?

      As I guess the answer is NO, then, Which is the best script to get it?

      Thanks in advance,


        • 1. Re: depurating a list

          there is a "wildcard" character that lets you find duplicates. this may or may not help, but you can try:


          p.s. this works in all versions of FMP

          • 2. Re: depurating a list

            Thanks Beverly for your kind and fast reply!

            I'm trying to understand it. May be there's a solution in that way.

            • 3. Re: depurating a list

              it will return all records that have a duplicate in whatever field you search. If you happen to have more than one ID that might be duplicated, you might sort by that field to "group" them.


              tell us what you'd like to do with the list and there may be alternatives.


              • 4. Re: depurating a list

                I followed the instructions. It's magical how it places "unique" or "duplicate" in the check duplicates field! I can't understand why it works, yet.

                But in the step: "Select Update serial number in Entry Options, and click Replace." I can't find where to do this. In the "Replace Field Contents" dialog box there is an option for this, but it is grayed, not usable.

                • 5. Re: depurating a list

                  If you're trying to fetch a list of values (such as in a variable) that are unique, you can use:


                       ExecuteSQL ( "SELECT DISTINCT field FROM table" ; "" ; "¶" )


                  in a calculation. (Although I would not do it in a calculation field, as performance may be very poor in the right circumstances.)


                  Another option would be, if you have the list already, to use a Custom Function to remove the duplicates.


                  Edit: Never mind. I misread the original post. You want the duplicates, not a distinct list. Sorry!

                  • 6. Re: depurating a list

                    are you trying to change the duplicates?


                    can you post a screen shot of your next step(s) after finding duplicates?


                    • 7. Re: depurating a list

                      The depuration is only for viewing only.

                      Perhaps I didn't explain well.

                      I have, let's say, 5 records with ID 01, 3 records with ID 02, 7 records with ID 03 and so on...

                      I want the depurated list to show only one of each group, so as to have a short list (I have more than 2000 records in about 400 groups, or different IDs.


                      I changed auto entry data in the "counter" field, in the database managing dialog. It works OK now.


                      Now it will be easy to write a script for the short list view.

                      Thanks a lot, Beverly!

                      Best regards,


                      • 8. Re: depurating a list

                        Mike has given you a way to get these "unique" values for the ID field. (I think 'depurated' was not quite right, as you aren't removing anything.)


                        Good that you can proceed from here!


                        • 9. Re: depurating a list

                          Thanks Mike.

                          I could solve it by Beverly's advices.

                          • 10. Re: depurating a list

                            I apologize for my English. It's a second language for me. I'm from Argentina and my mother tongue is spanish.

                            Anyway, you had enough imagination to figure out what I was trying to say!