I should also say this is a calculation that worked at one point and I cannot find anything that has changed. Seriously confused.
Additional note - I can sort based on that field.
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?)
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.
Is the calculation result set as Number (lower part of the Specify Calculation window, dropdown menu below the calculation formula)?
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?
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.
Yes, result is set to be a number
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.
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.
Please try the following:
change your calculation field's formula from whatitis to ( whatitis ) * 1
see if this changes the find results.
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.
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.
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.
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.)