Filemaker 12 and MacOS 10.8.2 apparent find issue
Operating system version
Description of the issue
Having problems performing finds on unstored calculation fields involving related tables.
We have a client with a rental database. When they attempt to find a rent order that both belongs to a specific lease AND has an amount due of more than zero, the system cannot perform a successful find. We are able to see that the data we are searching for does actually exist by looking at the tables themselves.
When the same lookup is performed either by script (the normal method) or manually on a Mac system still running 10.6.8, everything works as expected.
The script and procedure were also working perfectly up until sometime on 1/7/2013, at which point it failed and the client contacted us.
Steps to reproduce the problem
Tried to run the script on multiple 10.8.2 workstations and on the client's 10.6.8 workstation. Also attempted to perform a normal Find Mode find in Table view with the same find criteria as the script.
The find is to locate all Rental Orders (monetary orders for payment) that have a remaining balance greater than zero. Leases are in a related table, linked by Lease ID to the Orders table, which has most of the data. The actual monetary amount for each lease is in a different table, related to the Orders table by Order ID. Balance Due is the calculation that compares the total amount owed via Details to the amount paid in the Payments table. The find is:
Find: Leases::Lease ID = Orders::Lease ID
Omit: Orders::Balance Due =0
All orders for a specified Lease ID should be shown that do NOT have a zero balance.
All orders are shown, regardless of balance. It seemingly ignores the Omit. Changing the Omit to a Find: Orders::Balance Due >0 fails entirely with a No Records Found error.
Performing a normal Find Mode find in Table view for Lease ID and ANY information in any of the calculated fields fails to find any records. Performing a Find for just the Lease ID shows us all the data is existent in the database as expected. Searching for ONLY the calculated information also fails.
Exact text of any error message(s) that appear
No Records Found (standard find error message).
These are a client/server setup. The server is just a standalone copy of FileMaker Pro 12v03 on a 10.6.8 workstation (not used for client access). The clients are Macs running 10.8.2 and FM Pro 12v03.