4 Replies Latest reply on Feb 27, 2010 11:59 AM by wadef

    Inconsistent Find Results when using a scripted search on a related field

    wadef

      Title

      Inconsistent Find Results when using a scripted search on a related field

      Post

      I'm new to databases in general and Filemaker in particular and am experimenting with the demo version of FilemakerPro 10 on my Mac (OS X 10.6.2)

       

      I'm trying to set up a database to track my investment activities and portfolio.  I'm starting with 2 related tables: one for Activities (buy, sell, dividends, etc) and one for individual Securities (stocks, bonds, etc).  The activities table is populated with records of various actions including the stock name and symbol (from the related Securities table)

       

      I'm having difficulty trying to find all records related to a single stock symbol when I search via a script.

       

      I am able to successfully set up a search based on any field other than the ::symbol field.  E.G. I can find all records with price < nn, or quantity > mmm.  However, when I try to find all records with a symbol (text), I get inconsistent results.  (e.g. "no records match this set of find requests" when I set the search on one symbol, and the wrong records when I set the search up for a different symbol)

       

      The script I am using is 

       

      Go to Layout ["Activities Register" (Activities Register)]

      Enter Find Mode [ ]

      Set Field [Activites Register::Symbol_or_CUSIP; "BCE"]  (works OK on different field such as Price; >25)

      Perform Find [ ]

       

      I can do the search by going to Find Mode, entering the symbol in the corresponding field and clicking on the 'Perform Find' button, but I want to do further processing on the found data (e.g. find the last record of the set, and repeat the process for other symbols).  But for some reason, the 'Perform Find' script works differently from the button.

        • 1. Re: Inconsistent Find Results when using a scripted search on a related field
          philmodjunk

          A scripted find and a manual find should produce identical results if they are entering identical criteria.

           

          The fact that you are searching a related field may be a clue.

           

          If your manual search is being performed on your securities layout, then you'll get different results from your script example which is directly searching the activities table.

           

          When you do your find on the securities layout, you are telling filemaker "Find all securities records that are linked to at least one activivities record with "BCE" in the Symbol_or_CUSIP field. When you run your script, you are telling filemaker, "Find all the activities records with "BCE" in the Symbol_or_CUSIP field.

          • 2. Re: Inconsistent Find Results when using a scripted search on a related field
            wadef

            Thanks for the prompt response Phil.


            I expected that the results should be identical between manual and scripted finds.  Both my manual and scripted searches were performed on the same layout (the activities layout).  However, I can't seem to get the script to work when the search field is related to the securities layout (although it works on other fields that are not related to the securities table).


            I suspect I'm not setting something correctly in the Set Field script.  My manual search is done by simply clicking the Find button and typing the symbol (it's text) into the ::Symbol_or_CUSIP field.  It reliably returns all records from the activities layout that have the matching symbol name.  When I use the script in the above posting, I get different (incorrect) results.  I don't understand why the script would be any different than the manual find process, expect for the obvious conclusion that I've got the script wrong.


            Wade

            • 3. Re: Inconsistent Find Results when using a scripted search on a related field
              comment_1

               


              dwf wrote:

              My manual search is done by simply clicking the Find button and typing the symbol (it's text) into the ::Symbol_or_CUSIP field.


               

              If you are on a layout of Activities, and you type your criteria into a field prefixed with :: then you are searching a related field. Your script, OTOH, enters the criteria into a local field. I am not sure why there should be a difference in the search results, when the fields are supposed to match - but the process is NOT the same.

               


              • 4. Re: Inconsistent Find Results when using a scripted search on a related field
                wadef

                OK, I think I've got it!

                 

                I had the wrong context!  I.E. I specified in my search Set Field [Activites Register::Symbol_or_CUSIP; "BCE"] because I was searching the records in the Activities table, but I should have had it as Set Field [Securities List::Symbol_or_CUSIP; "BCE"] because that is where the original symbols are defined.

                 

                Thanks all.  Now my next tast is to make it interactive - let the user (me) select the symbol via a dialog box.