6 Replies Latest reply on Jan 27, 2012 9:10 AM by davidanders

    Efficient Way For Finding Names



      Efficient Way For Finding Names


      I have three fields, called NameFirst, NameLast, and WasFound.

      I have a list of 12 names and if someone from the list is found, I want to set WasFound to 1.

      Given that people sometimes enter their names in revers order (Mary Smith, versus Smith Mary, in upper case, lower case, or mixed case, what is the most efficient way to see if I have any of these people in by table? All suggestions appreciated.

        • 1. Re: Efficient Way For Finding Names

          Hi David,

          Setting a field in a record with a 1 for 'wasFound' will not work in multi-user (when shared) because people will have different 'found sets.'

          It is usually better to take the information (entered into a global text field), let script perform the search and produce the found set immediately for the User.   Just to clarify the rules, how would User enter names they want to find?  Would it be:

          Joe R
          Smith, J


          We can use script to parse out each line but I need to be clear on how you envision the setup.

          • 2. Re: Efficient Way For Finding Names

            case won't be an issue as find criteria in FileMaker is not case sensitive.

            Add a calculation field, cFullname to your table defined as:

            NameFirst & " " & NameLast

            You can then enter your search criteria in cFullName and The Criteria "Mary Smith" will find the same records as "Smith Mary".

            Since you have a list of names, A script can loop through that list, either performing 12 finds or creating 12 requests for a single find to locate and "mark" the found records.

            What is the purpose of "wasFound" here? Is this a database that will have more than one user accessing it at a time? (The "wasFound" changes by one user can conflict with the changes made to this field by another user.) There may be a better approach you can use for this.

            • 3. Re: Efficient Way For Finding Names

              If you create a calculation ( result is text) with:  FirstName & " " & LastName

              ... then you can search this field.  Then your script will only need to search one field.  But each of your search value must be a different find request.

              • 4. Re: Efficient Way For Finding Names

                Oh yes, and if you put your list of names into a global field separated by returns, it might be possible to set up a relationship that matches to the correct records without performing any find at all--though the mixing of name orders First, last vs last, first would require some special handling in that case...

                • 5. Re: Efficient Way For Finding Names

                  I would not suggest creating a relationship and the required permutation script calculation just to get a match per name or partial name.  That uses a lot more resources and is no more efficient than a find.  That is what indexing is for.  :^)

                  • 6. Re: Efficient Way For Finding Names

                    If you create a calculation field for the first letter in the last name (Smith would be S, Vander Hooble would be V - not V and or H)

                    You can create 26 buttons that will return the Last Names that the first letter is A and B and etc.  Old Time Rolodex interface.