1 2 Previous Next 17 Replies Latest reply on Jan 25, 2016 1:17 PM by beverly

    Search Two Fields at Once (Easy Points)

    osensnolf

      I have two fields: First and Last (for their name).

       

      A small percentage of my data has the first name and last name as the same data.  For instance, Fist=Chris, Last=Chris.

       

      How can I search for and identify all records that have the same First Name and Last Name (excluding blanks).

       

      Thank you in advance for your time.

        • 1. Re: Search Two Fields at Once (Easy Points)
          beverly

          This is also an OR search and needs the New Record/Request:

           

          Enter Find mode

          Set Field ( First ; "Chris" )

          New Request

          Set Field ( Last ; "Chris" )

          Perform Find

           

          beverly

          • 2. Re: Search Two Fields at Once (Easy Points)
            osensnolf

            Hi Beverly

             

            I am not at the computer to try that, but my thought was that it would only find any records

            with the first name Chris and any records with the last name Chris.

             

             

            I want it to only find records where BOTH the first and last name are Chris within the same record.

             

             

            Will this do that?

            • 3. Re: Search Two Fields at Once (Easy Points)
              schamblee

              Sorry.  I responded  incorrectly at first. I got it backwards.

               

              Items on same record is 'and'.   New Record Request makes it 'or'

              Remove the New Record Request.

               

              How to conduct multiple find requests in FileMaker Pro | FileMaker

               

              Specify Find Requests and Edit Find Request dialog boxes

              • 4. Re: Search Two Fields at Once (Easy Points)
                MaxEh

                My reading of your question is that you want to find records where someone has inadvertently put 'the same' name into both fields in 1 record. That's an AND search. So you would set your search criteria as:

                 

                Enter Find mode

                Set Field ( First ; "Chris" )

                Set Field ( Last ; "Chris" )

                Perform Find

                 

                or enter Find mode and type in "Chris" in the First name field and click into the Last name field and type "Chris" again if you are not abstracting it with scripting.

                 

                This should find records in which "Chris" is both in the First name field and Last name field.

                If First is set to "Chris" and Last is set to "Max" only records that have that combination will be found.

                Keep in mind that someone could have the same first and last names although I can't think of any right of the top of my head!

                • 5. Re: Search Two Fields at Once (Easy Points)
                  osensnolf

                  I think/know I asked the question incorrectly... I'll rephrase it.

                   

                  How can I find records that have the same First and Last name within the same record without knowing the name?  If I am looking for records that have the Firstname as Chris and the Lastname as Chris, that is easy to do.

                   

                  But what about records I do not know of.  Is there a way for me to run a script or something that says "Show me all records that have the a First name that also equals the Last name within the same record."

                   

                  The results will find "Chris Chris", "John John", "Mike Mike".

                   

                  I hope that helps and that you do not give up.  I apologize for asking incorrectly.

                   

                  Thanks

                  • 6. Re: Search Two Fields at Once (Easy Points)
                    MaxEh

                    Ahh. My approach would be to create a calculation field SameName. The calculation would be something like"

                     

                    If (Yourtable::First = Yourtable::Last; 1)

                     

                    Then find all records where SameName = 1.  You can delete the Same Name calculation field once you are done. I think an Execute SQL might work here as well.  Something I need to learn!

                    • 7. Re: Search Two Fields at Once (Easy Points)
                      osensnolf

                      Hmm.  This is where it hurts me not being the developer.  I may be able to fumble my way trough it but not sure..

                      • 8. Re: Search Two Fields at Once (Easy Points)
                        wintertj

                        Depending on how you want to handle these cases, you may want to set up a field on a table, perhaps a global, and export all such instances into a CSV list, then you could export that single field into a flat csv file, then import it back into a table to deal with the records however you intend to deal with them. Identifying the cases where first and last name are equal may be a lot easier in SQL than a native FileMaker search due to its cumbersome use of OR logic (i.e.: seperate requests as Beverly demonstrated). Try something like this as a field calc or in data viewer and cut and paste the results even:

                         

                        ExecuteSQL ("

                        select *

                          from table

                        where First = Last

                        " ;"," ;"¶" )

                        • 9. Re: Search Two Fields at Once (Easy Points)
                          beverly

                          That's an AND search.

                          No new request needed. Put the value in both fields.

                           

                          -- sent from myPhone --

                          Beverly Voth

                          --

                          • 10. Re: Search Two Fields at Once (Easy Points)
                            osensnolf

                            I pasted the value below in the Find field for both First and Last and as Beverly mentioned, I did both fields at the same time and did not add a new request.

                             

                            No results (although I know there are because I made it.)

                             

                            ExecuteSQL ("

                            select *

                              from table

                            where First = Last

                            " ;"," ;"¶" )


                            Is the above a Filemaker command or only for SQL?

                            • 11. Re: Search Two Fields at Once (Easy Points)
                              wintertj

                              ExecuteSQL is a FileMaker calculation, but it uses the SQL language which is used in most relational databases. You'll not paste it in a field, rather, it will be the calculated value of a field itself. For it to work, you'll need to replace the word "table" in my example with the exact name of your table (and it will need to be an SQL compliant table name too, so you may need to change the table name to not have spaces, etc). Also, the First and Last need to be the exact names of the fields on your table, and will need to be SQL compliant names as well. If you are using FileMaker Pro Advanced, use the data viewer to work your query till it produces results. If you are not using Advanced, which lacks data viewer, it'll be a little tricker to visualize it as you'll need to set a field, calc it, then view the results.

                              • 12. Re: Search Two Fields at Once (Easy Points)
                                osensnolf

                                Thanks Tony for your help on this.  I'll pass this along to my developer and he should be able to provide additional help with using your command.

                                • 13. Re: Search Two Fields at Once (Easy Points)
                                  beverly

                                  If you have someone else doing the developing and are trying to prevent the users from entering the same in both fields, you can make a validation (by calculation), on say the last name, so that the user is given a dialog to let them know. And/or create a script trigger on the fields to check that these two fields are not equal. Preventing the error may help in the long run (after you clean up the mistakes once).

                                   

                                  beverly

                                  • 14. Re: Search Two Fields at Once (Easy Points)
                                    osensnolf

                                    Someone else is doing the development work but this issue is only a problem with existing data.

                                    1 2 Previous Next