5 Replies Latest reply on May 6, 2014 7:02 AM by philmcgeehan

    Search for Everything but a Letter

    philmcgeehan

      Title

      Search for Everything but a Letter

      Post

           I have a massive list of contacts, in a FileMaker Go layout for iPad, and, I have 28 buttons running along the footer to help the user find the contact they are looking for.

           Buttons [A] ... [Z] an [ALL] button and a [#] button.

           The A to Z buttons work very well and return all contacts that start with that letter.
           The All button works well, and returns all of the contacts,
           however,
           the # button, I want it to return all contacts starting with a number or any other punctuation character, such as brackets (parentheses), hyphens, dashes, et cetera.

           I can return contacts starting with numbers just fine, by performing a find with "#" as the criteria, but my question is, is there an operator that will return all characters, apart from letters; or, will I have to perform multiple searches with all the possible punctuation that a contact could start with. 

        • 1. Re: Search for Everything but a Letter
          philmodjunk

               No such operator, but instead of a ridiculous number of searches, consider setting up a calculation field that returns a 1 only if the first character is not text. You can then use the filter function in combination with the left function to filter out all upper and lower case letters in that calculation field. Then you can search for a 1 in that field.

          • 2. Re: Search for Everything but a Letter
            philmcgeehan

                 Thanks Phil,

                 I made a new calculation that returns a 1 if the contact starts with a letter.

                 If ( Upper ( Left (CompanyName; 1 ) ) =
                         Filter ( Upper ( Left ( CompanyName; 1 ) ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )
                         ; 1
                 )
                  
                 Not sure if that's the most elegant calculation, but it does the trick.
            • 3. Re: Search for Everything but a Letter
              philmodjunk

                   It makes nearly no difference but you don't need the first use of Upper. The = text comparison is not case sensitive. Filter, on the other hand, is case sensitive so you need the second use of Upper.

                   and since 1 is the same as True, you can leave out the If function:

                   Left (CompanyName; 1 )  = Filter ( Upper ( Left ( CompanyName; 1 ) ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )

                   But this does not change the results so none of these changes are in any way required.

              • 4. Re: Search for Everything but a Letter
                philmodjunk

                     I'm re-reading this again, (don't know why this popped up in Recent items), and you do need that Upper function if it's possible that the first letter is a lower case letter.

                • 5. Re: Search for Everything but a Letter
                  philmcgeehan

                       I edited the formatting of a post in this topic, while I was playing around to see if there was a way to display part of the post in a monospaced font like <code> tags </code> — I don't think there is.

                       Thanks for the help though, I got this working how I need now.