3 Replies Latest reply on May 16, 2013 1:17 PM by philmodjunk

    Script to search another database for a term



      Script to search another database for a term


           I have two databases to manage submissions to an academic journal that I want to "connect." The first database has information about the article submitted ("Works" database). The second database has contact details about the scholar (basic "Contacts" template database). Both databases have entries for "Last Name."

           I want a button, associated with the "Last Name" field of the Works database, that will search for matching entries in the "Last Name" field of the Contacts database.

           Any idea on how to go about this? Or any suggestions on better ways to connect the two databases?

        • 1. Re: Script to search another database for a term

               By "database" I think you mean Table. Do you have your tables in two different files or the same file?

               If you defined a relationship between your two tables (this can be done even if the tables are in different files) like this:

               Works::LastName = Contacts::LastName

               Then your button in Works can use Go To Related Records to bring up all contacts with a matching last name. Or you can put a portal on your Works layout and it can list the same matching contacts in the portal.

               But since last names are not unqiue, the resulting list may list contacts that had nothing to do with that "work". If you linked works to contacts by an ID number, you'd avoid those problems.

               Here's a demo file that uses names to look up ID numbers in order to link records:

               FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
               Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7


          • 2. Re: Script to search another database for a term


                 Thank you for your help! That worked for going from Works to Contacts.

                 The next problem, however, is this: I need to be able to use the Contacts layout to look for related entries in Works. However, it is not as simple as I made it sound in my original post. Each entry in Works has three last names: the last name of the author, the last name of the first peer reviewer, and the last name of the second peer reviewer.

                 I want to see every work a given person has been associated with--whether authored or reviewed it.

                 So I have, for Works:

                 Author Last Name
                 Reviewer1 Last Name
                 Reviewer2 Last Name

                 But for Contacts:
                 Last Name

                 I tried doing what you suggested above, but this time saying that Contacts::LastName=Works::AuthorLastName AND that Contacts::LastName=Works::Reviewer1LastName, etc. but this yields nothing. Any advice on where to go from here?

            • 3. Re: Script to search another database for a term

                   You need the matching logic of your relationship to be OR, rather than AND.

                   There's a trick you can use to get that result.

                   Define this calculation field, cContactList, in works:

                   List ( Author Last Name ; Reviewer1 Last Name ; Reviewer2 Last Name )

                   Be sure to select "text" in the Result Type drop down.

                   Define your relationship as:

                   Works::cContactList = Contacts::LastName

                   That will result in matching works to contacts by Author Last Name OR Reviewer1 Last Name OR Reviewer2 Last Name.