5 Replies Latest reply on Dec 3, 2009 7:01 AM by tas

    Searching calc field on server



      Searching calc field on server

      Description of the issue

      I have a calculation field that determines the distance between the zip code of an entry in our Candidate database and another zip of the user's choosing. This zip is entered in a global field. The calculation works perfectly, but searching doesn't. The first search on the calc field works fine. I am usually searching for someone within a certain distance (

        • 1. Re: Searching calc field on server



          Thank you for your post.


          Remember... global fields are local for each session.  Therefore, any entries you make into a global field will be different than someone else who is using the same file.  This could cause a lot of the slowness.  Instead, you may want to issue a Replace into a text field.  This would force the calculation across all records, and the search would be quicker.


          In any case, I am happy to see your file.  I have sent you a private message (top of this screen - right side - X Messages) with instructions where to send the file.



          FileMaker, Inc. 

          • 2. Re: Searching calc field on server



            Thank you for sending in your database file.  I was able to understand what you are trying to accomplish.


            Any calculation field that involves a global field will be unstored.  On a local machine, this isn't a problem, but when put on a server, where each user has a local global variable, the results are unexpected.  For your case, since you are using FileMaker Pro 10, you can take advantage of script triggers.  This is what I did:


            1. In the Candidates table, I created "g_distance" - a global Number field.  This field will be used to enter the distance you want to search for.  In the example you sent me, you were searching for "<15".  In this field, just enter 15.


            2. Create the following script, "Distance Trigger":


            # check to make sure the value isn't negative 

            If [ IsEmpty (Candidates:: g_distance) or Candidates:: g_distance < 0 ]

               Exit Script []

            End If


            # make sure all records are available and point to the first record

            Show All Records

            Go to Record/Request/Page [ First ]


            # initialize a flag/variable to keep track of the last record

            Set Variable [ $exit ; "0" ]


            # enter loop



               # check to see if record is outside the distance.  If so, omit the record

               If [ Candidates:: g_distance < Candidates:: c_Distance ]

                  Omit Record


                  # check to see if the flag/variable was set for the last record.  If so, exit loop

                  Exit Loop If [ $exit = "1" ]


                  # check to see if the current record is the last record.  If so, set the flag/variable

                  If [ Get ( FoundCount ) = Get ( RecordNumber ) ]

                     Set Variable [ $exit ; Value: "1" ]

                  End If



                  # else, record is within the requested distance, so go to the next record

                  Go to Record/Request/Page [ Next; Exit after last ]

               End If


            End Loop


            # check to see if zero records are within the required distance

            If [ Get ( FoundCount ) = 0 ]

               Show Custom Dialog [ "No Records Found" ]

               Show All Records 

            End If 


            # the pointer is at the last record, so go to the first record

            Go to Record/Request/Page [First]





            3. In Layout mode, click once on the g_distance field, pull down the Format menu and select "Set Script Triggers..."


            4. Put a check mark next to "OnObjectSave", and select the script "Distance Trigger".  Click OK.


            Go to Browse Mode and enter a Zip along with a distance.


            Let me know if you need clarification for any of the above steps.



            FileMaker, Inc. 

            • 3. Re: Searching calc field on server
                 Thanks for the recommendation. I will use this work around. Do you know if this issue is something that Filemaker is working on fixing or should I do a similar work around for all similar calculation fields in the future?
              • 4. Re: Searching calc field on server



                I have no information about the status of this issue.  In the mean time, if you are searching on any field that is a calculation based on global fields, then use a similar script as above.



                FileMaker, Inc. 

                • 5. Re: Searching calc field on server

                  I was wondering if the problem I have is related to the one in this thread or I should start a new thread.  This is my problem:


                  I have two related tables: Items(ID,...) and Transactions(ItemID, Date, Quantity).  I want to compute the total quantity of transactions of an item before a specific date. So I add a global in Items (gEndDate) and add another relationship Transactions_OnDate where ID=ItemID AND gEndDate >=Date

                  I then add a calculated field in Items Qty_OnDate = Sum(Transactions_OnDate::Quantity)

                  Everything works perfectly.  I see correct records in both portals.  I am also able to search in the calculated field Qty_OnDate.

                  BUT when I move the database to server, portals and records display correct amounts but when I search for Qty_OnDate >0 after changing global gEndDate I get the wrong results: I get results of previous search, as if the Qty_OnDate has not updated even though I see correct results. If I make any changes to a record, then Qty_OnDate is updated properly and that record appears correctly after find. 


                  Is this manifestation of the same problem? Can you suggest a workaround for this case?