6 Replies Latest reply on Feb 1, 2012 6:14 AM by PeterW45

    Script to use a find result as source for new search



      Script to use a find result as source for new search


      I am setting up a tennis database (http://forums.filemaker.com/posts/59e6bdf9d0). I realize this comes down to a very simple flat file of the form, 1A, 1B, 1C, 1D, 2A, 2C, 2D, 2E, etc. The numbers represent a matchID and the letters represent the playerID. I want to search for a player's records and find who has played with him. That means I search for A and return the records as his matchIDs. I want to use the search result for a new search based upon those matchIDs. This is a very simple type of script and I have seen variants of it, but when  I perform the script, it enters "$MatchID" into the MatchID field. 

      This what my script looks like:

      • Enter Find Mode[Pause]
      • Set Variable [$MatchID; Value:Tennis DB::MatchID]
      • Extend Found Set[Restore]



        • 1. Re: Script to use a find result as source for new search

          Wouldn't that one get one player A's opponents?  It would get the MatchID from the first found record and then find the two records for that match, player A and player B.  If player A had multiple matches it would not show any but the first.

          I would set this up with multiple tables and relate players through a match table, then use a portal to list matches a player was in and his opponents.

          • 2. Re: Script to use a find result as source for new search

            What version of FileMaker do you have? This method only works with FileMaker 11. (and make sure $MatchID is not in quotes.) And then $MatchID must be given a value while not in Find mode. (After you enter find mode, non global fields go blank and their data becomes inaccessible.) Your given script permits continuing the script without performing the initial find.

            Other scripted finds that use set field to move the value from $matchID as criteria into the appropriate field work in FileMaker 10 and 11 and make for a script where it is easier to see the criteria used.

            • 3. Re: Script to use a find result as source for new search

              @Mark, You are correct in my intent. I do want to list all of Player A's matches. I actually want to count to see who he has played with most frequently. In my database, I have a summary field which includes a count of PlayerID. Thus, if I can see all of Player A's matches, then I can list the number of times he as played with each player. That is my objective. I can do this manually by entering the matchIDs in an extended search, but that is tedious. I thought I should be able to do this in FileMaker with a script. Did you have something in mind?

              @Phil, I do have FileMaker 11. The script was exactly as posted above, no quotes. I tried moving the set variable before entering find, setting the set variable to a global with $$MatchID, I tried setting the value to 1 by entering 1 in the value box, put in "=" & Tennis DB::MatchID (as http://forums.filemaker.com/posts/91881504ae), and others. I just have not been able to get anything into the MatchID field. I often saw $MatchID appear with an error message. I also saw some just pass through the script without changing the find (since it is in extended search mode, I refind the prior results, and get to see the format of the search). I tried putting a MatchID number into the Extend Fount Set [##], but it must jump out before this as no find occurred. I can see by looking at the result of a manual find that the form of the extended find contains Tennis DB::MatchID[6], for example. Therefore I know that I am not passing my variable in as I am unable to change the values in the extended find. 

              • 4. Re: Script to use a find result as source for new search

                My biggest concern about your script as written is that it enters find mode and pauses. It does not actually perform a find. When you press enter or click continue, it then stays in find mode. In find mode Tennis DB::Match ID will be empty and no value will be assigned to the variable for use in the extend found set.

                What kind of find do you want to perform before you use the extend found set to add records to what you find in the first part of this process?

                • 5. Re: Script to use a find result as source for new search

                  I understand what you have said about entering the find mode, but it has not been restored and in the extend mode I can see the remains of my prior finds, namely Tennis DB::MatchID[6], Tennis DB::MatchID[8], etc. So I watched to see if I was able to add to the find command. The existing values told me the find was working. I was able to add values during the pause, so I extended the search manually. I was not able to introduce any of the MatchID numbers into the find command. 

                  I did find that writing a simple script to find a single value was sensitve to the language used. An extra space resulted in a failure to execute. 

                  My ultimate objective would be to create a report that would show the number of matches each player had with every other player. I can do this manually by doing an extended search for the matchID's for each player and then producing a summary table. A player search returns the matchIDs. If I could use the matchIDs to do a search for the players, then I could prepare a summary table for each player. 

                  • 6. Re: Script to use a find result as source for new search

                    Here is how to do it. See http://forums.filemaker.com/posts/0f6fc4326f (I gave it a thumb's up!)


                    Go to Layout [ “MainList” (MainList) ] # # First find the IDs Enter Find Mode [ ]

                    [ Pause ] Set Error Capture [ On ] Perform Find [ ] If [ not Get ( LastError ) ]

                    Go to Related Record [ From table: “SelfJoin”; Using layout: <Current Layout> ] [ Show only related records; Match found set ]

                    End If


                    This works eactly as I had hoped. Thanks to LaRetta. There is also a file containing the script posted in the link. I downloaded and modified it.