5 Replies Latest reply on Nov 29, 2009 1:54 AM by Sorbsbuster

    Batch Find



      Batch Find


      I am trying to figure out how to do a "batch find" on our database.  I have a text file of first and last names from a recent event.  Rather than doing a manual lookup of each name, I would like to use this text file to do a "batch query".  The query would display all the records matching the names in the text file.  (A bonus would be to just use the first initial of the first name in the text file with the last name so we could pick up names that are not exact matches such as Jackie Nelson vs Jacquelyn Nelson.)  I want to be able to do data entry on the records matching the names in the text file.


      The database in question is running Filemaker server 7 and the clients are Filemaker Pro 7.  The clients are hosted on Mac and Windows XP pro machines and Filemaker server is hosted on a Windows server machine.


      Our members table has first and last name fields. 


      I work as a volunteer tech for a non-profit and am not much beyond a beginner in Filemaker.  I have worked with relationships Filemaker and initially thought a Join operation could be used to find the records matching the first and last names.





        • 1. Re: Batch Find

          Do you mean that you have, say, a Members Database listing (say) 1,000 members, and you have a listing of (say) 200 attendees at a recent event, and you want to find those 200 members among the 1,000?


          If that is the case then you could have a table construction of the Members Table with all 1,000 members listed.  Duplicate the table (only for the sake of simplicity at this stage).  Call it 'Search Set'.  (When you establish an enduring purpose for it you could re-name it more appropriately, but this will show you the principle.)  Delete all the records from it and import all of the 200 names in the text file.


          Create a relationship between the Members Table and the Search Set Table.  If you have an ID which is unique to each member (good DB practice) then that is what you would use to link the two files.  By the nature of the 'Jackie'...'Jacqueline' qualifier I am guessing you don't.  In that case, start by linking 'LastName' to 'Lastname'.


          In the Members Table define a field as a calculation:


           InSearchSetYesNo = Case (Count (SearchSetRelationship :: LastName ) = 0 ; "No ; "Yes" )


          Then perform a Find in the Members Table for all records where that field = Yes.  They are the members who attended the function.

          If you create the field in the Search Set also and do a find for 'No' you will find any attendees who are not listed as members.


           I have glossed over the fact that you may have several different members named  'Smith'.  Prove that the technique works first of all, then modify the existing relationship to have multiple criteria.  Add that the FirstName must also match, so now 'John Smith' won't be shown as atending whenever it was 'Brian Smith'.


           Now, I have glossed over the fact that you may have several different spellings of 'Jackie', Jacquie', etc.  Now you're proving why you need to have a Unique Identifier...  If you are certain that (or would be satisfied with) finding a match of 'Smith' for definite, and then also 'J' after that, you could isolate the first letter of the FirstName, with a field FirstNameFirstLetter = Left (FirstName ; 1) and use that to match as the second criterion.


          Won't help you will 'William' and 'Bill', though...  Now you're proving why you need to have a.. etc.



          • 2. Re: Batch Find

            Hi Alan,

            Thanks for your reply.  I did what you suggested and it works great with the last name. And it also works great with the last name and the first name initial.  


            We do have a unique id for each member of our 'Members' database.  How does that help me with the Bill, William problem? 


            Anyway, thanks for your advice! 




            • 3. Re: Batch Find

              Because you'd just forget all the 'name' confusion and link directly using MemberID :: MemberID.


              Glad it worked for you!

              • 4. Re: Batch Find



                The problem with the attendee list (Search Set Table) is that it has no member id info.  Its just a list of names - first and last.  Some of the members of the attendee list are in our Members database and some are not.  So the Search Set Table unique id field would be blank or null - thus the necessity to use the names for the search. If you get a chance, let me know if I am missing something here.


                Thanks again!


                • 5. Re: Batch Find

                  No, you're not missing anything - you would need the ID in both lists.


                  Looking from the Attendees list back into the Members list (as discussed earlier) will show you all those people who attended but are NOT members.


                  You could have a portal on that screen which shows all members with the same LastName as the Attendee and what their FirstName is.  If we assume (always dangerous) that people always spell their Last Name consistently, then you could easily spot from the portal listing that the attendee 'Smith, Bill' matches possible members, 'Smith, John', Smith, Jim', 'Smith, William', Smith, Adam' and decide if that is the 'Smith, William' that attended.