1 2 Previous Next 27 Replies Latest reply on Aug 9, 2017 11:21 PM by FabriceNordmann

    How to find 3 of a kind

    eduardohomem

      My database has 1000 records. Each record has 8 number fields. My aim is to find all records with 3 equal numbers.

        • 1. Re: How to find 3 of a kind
          fmpdude

          This is an easy one with SQL:

           

          • 2. Re: How to find 3 of a kind
            eduardohomem

            Sorry, I dont know SQL. My FM version is 8.

            • 3. Re: How to find 3 of a kind
              TomHays

              For clarification please be very specific in your needs.

               

               

              Your table has 8 Number fields

              numA

              numB

              numC

              numD

              numE

              numF

              numG

               

              Record 1 has

              numA = 7.2

              numB = 6.5

              numC = 7.2

              numD = 3.8

              numE = 9.67

              numF = 7.2

              numG = -16

               

              Record 2 has

              numA = 0

              numB = 12

              numC = 31.22

              numD = 47

              numE = 16

              numF = 7.2

              numG = 9

               

              Record 3 has

              numA = 8

              numB = 8

              numC = 49

              numD = 8

              numE = 8

              numF = 7.2

              numG = 9

               

              You want to do a search and bring up Record 1 and Record 3 as a Found set containing these 2 records.

               

              Is this correct?

               

              -Tom

              • 4. Re: How to find 3 of a kind
                beverly

                ExecuteSQL() is FM12-current.

                Can you post some example data (the 8 fields) and highlight a record (or more) that match this criteria?

                Also, do you want JUST where any 3 of the 8 are the same? Or 3 or more of the 8 are the same?

                Beverly

                Sent from miPhone

                • 5. Re: How to find 3 of a kind
                  eduardohomem

                  Thank you. I want to bring up all records with 7.2 because in that example Record 1, 2 and 3 share 7.2. And also records 2 and 3, because both share 9 in common.

                  • 6. Re: How to find 3 of a kind
                    TomHays

                    You want to locate records that have the same value in the field numA on at least 3 different records.

                     

                    You want to do the same for each of the 8 fields.

                     

                    In my example records #2 and #3 share the value "9" in field numG, but since there are only two records and not three, I didn't expect that they would fit your criteria.

                     

                    If you do want to find two or more records with the same value in the same field, you can search for "!" in that field.  It is a special character meaning to find duplicates.  After searching, sort by that field to see the matches as neighboring records.

                     

                    I expect locating 3 or more duplicates and omitting simple 2-record duplicates will require adding additional calculation fields, related tables, and/or scripting.

                     

                    -Tom

                    • 7. Re: How to find 3 of a kind
                      eduardohomem

                      Tom,

                       

                      I want to locate ANY records that have the same value in 3 fields and bring all those records in a new found set. For instance: in a record, field A, D and G have numbers 1, 4 and 6. I want to locate ALL records with numbers 1, 4 and 6 in any of 8 fields.

                      Thank you so much for your help

                      • 8. Re: How to find 3 of a kind
                        TomHays

                        Ok.  I think we're getting somewhere.

                        All of my interpretations so far were misunderstandings.

                         

                        You have 8 number fields.  You want to treat them as a set of numbers.  You don't care where the values are in any of the 8 fields.

                        (Note that this data structure is usually represented as a set of related records in another table instead of distinct fields on a single record.)

                         

                        You want to locate any record that has in common with another record at least 3 number values.

                         

                         

                        That's a bit tricky.  But, as you have seen, defining the problem is a significant part of the work.

                         

                        -Tom

                        • 9. Re: How to find 3 of a kind
                          FabriceNordmann

                          I think I would create an additional calculation field and and search on it.

                           

                          GetAsBoolean ( position (

                          substitute ( sortValues (

                          list ( field1 ; field2 ; field3 ; field4 ; field5 ; field6 ; field7 ; field8 ) ; 2 ) ;

                            [ ¶ ; "" ] ;

                            [ "000" ; "BINGO" ] ;

                            [ "111" ; "BINGO" ] ;

                            [ "222" ; "BINGO" ] ;

                            [ "333" ; "BINGO" ] ;

                            [ "444" ; "BINGO" ] ;

                            [ "555" ; "BINGO" ] ;

                            [ "666" ; "BINGO" ] ;

                            [ "777" ; "BINGO" ] ;

                            [ "888" ; "BINGO" ] ;

                            [ "999" ; "BINGO" ]

                          ) ; "BINGO" ; 0 ; 1 ))

                          • 10. Re: How to find 3 of a kind
                            beverly

                            Is this a matrix sort of calc?

                             

                            Sent from miPhone

                            • 12. Re: How to find 3 of a kind
                              TomHays

                              Sometimes a solution can take advantage of a feature of the data.

                               

                              Are these number fields restricted to positive integers, i.e. counting numbers?

                              Are these numbers within a specific range?

                              Are all 8 number fields guaranteed to be filled in with a valid number, i.e. not empty?

                               

                              What are the technical options for the solution?

                              Can you add new fields to the table?

                              Can you add scripts?

                              Are you using FileMaker Pro Advanced which allows for custom functions?

                              Can you use a FileMaker plug-in?

                              Do you want the end result in a Found Set or do you want a list of unique record IDs?

                               

                              -Tom

                              • 13. Re: How to find 3 of a kind
                                eduardohomem

                                That's correct, Tom.

                                How could I do that?

                                • 14. Re: How to find 3 of a kind
                                  FabriceNordmann

                                  Sorry, I hadn't seen your sample data. This won't work with your numbers, and even less with FileMaker 8.

                                  1 2 Previous Next