1 2 Previous Next 19 Replies Latest reply on Mar 18, 2015 8:14 AM by Mike_Mitchell

    Find request not working


      I am trying to diagnose a problem with what I think is a simple find request and I am at my wits end.  I have a calculation field that takes total charges and subtracts payments and adjustments resulting in a number formatted to a currency.  From that, I am running a find request to find amounts in that field greater than $0.00.  No matter what I do, I am getting those that owe money AND those with a 0 balance.  I change the find request to >1 or greater than 100 and I get the same results.  What am I missing???

        • 1. Re: Find request not working

          I should also say this is a calculation that worked at one point and I cannot find anything that has changed.  Seriously confused.

          • 2. Re: Find request not working

            Additional note - I can sort based on that field.

            • 3. Re: Find request not working

              Couple of questions:


              1) Exactly what is the syntax of your Find request?


              2) Is this a stored or unstored calculation? (i.e., Is the field indexed?)

              • 4. Re: Find request not working



                Thanks for looking at this.  The calculation is unstored and the fields the calculation is based upon is unstored.  When you say syntax, I do know what you mean.  I am putting in a find request of >1 and getting results that include all of my guests that do not owe me money.  I manage guests coming to an event and balances are due.  What I am trying to do is pull up all the guests that still owe on their account and send only them an email.  If I cannot separate out the guests that have paid, I will have to pull each email manually.  I am not even working with different tables.  All of these calculations are within the same table.

                • 5. Re: Find request not working

                  Is the calculation result set as Number (lower part of the Specify Calculation window, dropdown menu below the calculation formula)?

                  • 6. Re: Find request not working

                    parnokr wrote:

                    I am not even working with different tables.  All of these calculations are within the same table.


                    You don't have a payments/transaction table?

                    parnokr wrote:

                    resulting in a number formatted to a currency


                    Do you mean you have this field on some layout(s) formatted as a currency, or that you're actually calculating the result's display as a currency?


                    If the latter is the case, then change the result type of the calculation to number (rather than text), and try your Find request again.


                    In general, calculate a numeric result as a number, and use the layout formatting tools to display that result as a currency.

                    • 7. Re: Find request not working

                      Yes, result is set to be a number

                      • 8. Re: Find request not working

                        That is what I have.  The calculation results in a number, and the display is formatted to currency.


                        You are correct about the payments table.  I am pulling totals from a couple of tables here (total of charges and total of payments)  The problem as I see it is I am getting the correct number results meaning $1000 in charges and 3 payments totaling $750 leaving a balance of $250.  That is correct, but when I run a find request on that data, in the balance field, if I put in >0 or pretty much anything else, I am getting the same results.


                        Been using Filemaker for 10 plus years and have been confused before, but have spent 4-5 hours on this now and cannot figure out the issue.  Should be a simple find request.

                        • 9. Re: Find request not working

                          With an issue like this, it's often the result of a corrupted index. It may also be coming from a dependency issue, since you have an unstored calculation that depends on another unstored calculation. In Find mode, those might not be evaluating correctly. So I would try a couple of things:


                          1) Rebuild the indexes on the source fields (the actual number fields) by turning indexing off, exiting the Manage Database dialog, then turning it back on.


                          2) Change the calculation so it goes directly to the source data, instead of creating a chain of unstored calculations. This would eliminate the dependency issue as a possible source.


                          Looking forward, doing searches on unstored calculations is a performance killer as the database grows. You might want to look at converting away from using them and towards using scripts to update the totals in regular number fields that can be indexed.





                          • 10. Re: Find request not working

                            Please try the following:


                            change your calculation field's formula from whatitis to ( whatitis ) * 1


                            see if this changes the find results.

                            • 11. Re: Find request not working

                              Don't know if this applies but I had a similar issue with a database running on FM Server 12. Find on calculated total worked as expected when working directly on the unhosted file, same find  request found no records when hosted on the server.


                              Rebooted the server, all works as expected.

                              • 12. Re: Find request not working

                                Thank you.  I am going to try that.  My db is on a remote 12 server and it will be reset overnight.  I am hoping that is the issue.  I cannot see how it can be a calculation issue since the calculations are all working properly, it is the find request that is not working.  When I called Datatrium about resetting the server, they indicated this is a known problem.

                                • 13. Re: Find request not working

                                  I like Mike's suggestion about the indexing. That could certainly be the culprit. The restarting of the server indicates a possible caching issue so I'll be curious to see if that makes a difference.


                                  Can you try Mike's suggestion first? If not, let us know if the restart of the server solves the problem.


                                  Can you let us know what Operating system both the client and server are on? I recently encountered a similar issue on Mavericks.


                                  Kind regards,



                                  • 14. Re: Find request not working

                                    you forget what the OP says about sorting: he is able to sort on that field. That excludes corruption, at least in my book.

                                    (The calc * 1 trick goes back to the 20th century, times when it was not an exception to use it.)

                                    1 2 Previous Next