4 Replies Latest reply on Feb 2, 2011 8:39 AM by LarryM1

    Extremely basic FIND question



      Extremely basic FIND question


      I've recently switched from Access and moved over to FileMaker, but I'm having an unbelieveable problem with the FIND procedure. I'm trying to find all records where the person's name begins with a letter from A-M. I started with the most logical thing I could think of, namely <N in the find field, but it simply doesn't work. It selects virtually every record in the database, and there seems to be no pattern whatever to the results. Maybe it's finding every case when the letter "N" appears anywhere in the field???

      I've tried other variatons on the theme - with quotes, without quotes, with an asterisk after the N. Nothing works. This seems to be one of the most basic possible things to do with any database, yet I can't do it and it's driving me nuts. I have searched high and low in the help file and pored through two different editions of the Missing Manual for FileMaker. Everything leads me to believe that a simple <N search would find everything that begins with A-M, but it doesn't. (And the field I'm searching is nothing special - a plain vanilla text field.)

      What in the world am I doing wrong? Thanks in advance for any help!

        • 1. Re: Extremely basic FIND question

          I think you're being a wee bit hard on Filemaker.  I can't answer what it is actually finding (but I'll be intrigued to find out) but Filemaker's searches are by default 'anything that starts with an 'N' , so if you type N in the Name Field it will find Noh, Nobo, Nevin, etc., but I think putting the operator '<' in front of the search criterion will make it look for a complete data value less than N.

          An easy way to do this would be to create a calculation field NameLetterFirst = Left ( NameField ; 1 ) and then search on it.  It will indeed find all the A-N people the way you describe.

          • 2. Re: Extremely basic FIND question

            Do you have more than one word in this text field such as "Zebra Apple Grape" all in one field?

            In filemaker, the above example would be found by  a find performed with <N as the criteria because the word Apple begins with a letter < N.

            I've been experimenting with alternatives, but so far, the only methods that work would be to search a calculation field that either uses LeftWords to extract only the first word or uses Substitute or Filter to remove the spaces so that the text becomes all one word.

            • 3. Re: Extremely basic FIND question

              Thanks, Sorbsbuster! I finally did manage to select A-M in a slightly different way. I created a new calculation field that put an "x" in the field if the name started with A-M, then I selected all the records with "x".

              In building that formula <N worked liked I thought it would. But I still can't understand why I can't get the same result simply by going to the FIND form and entering <N in the Name field. Utterly baffling...

              • 4. Re: Extremely basic FIND question

                PhilModjunk, I do indeed have multiple words in the field - e.g. Jones, Henry. Thanks for your explanation that it's performing the search on each word - but that strikes me as very non-intuitive. I'll use the LeftWords function next time I try this, but that sure seems like a complicated workaround for a very basic function.

                Anyway, thanks for the quick responses!