Finding Records using an Unindexed Field Stopped Working. Why? Help!

[jrie818]

I have a script that finds all the Invoices that are billed and have an amount > 0, and prints a list of them.

Enter Find Mode

Set Field (Invoice:Status = "Billed")

Set Field (Invoice:Balance = "> 0")

Perform Find

It's been working since the beginning. And somehow it just stopped working. The Status is indexed but the Balance is not (Recalculate when needed). Could it be that we have so much records and it failed (4000+ invoices)? Each invoice is linked to a transrecord that consist of every line item on the invoice. So if 4,000 invoice averaged of 4 lines per invoice that's 16,000 records that it has to re-calculate everytime. Could it be the problem?

I didn't change any of the script, I just stopped working, there are no records found when I perform the script.

Please Help. I don't want to go back and change the fields to Indexed fields, because I don't know what will be the ramification after I do it. What could be the solution to this?

[mr_vodka]

Try doing a manual find in the Status field for "Billed". If you are getting unexpected results. Turn off indexing on that status field, save the changes to the field, and then go back in and reindex the field. See if that solves your issue.

[jrie818]

Yes, I tried doing a manual search on the Status field. The Status field search is ok. The one I'm having problem is the Balance field. If I search for anything, whether it be "> 0" or "=0", it doesn't work.

I tried to turn on indexing on the Balance field but FileMaker won't let me because there are other fields on another table that is linked to this field that are not indexed as well (recalculate when needed).

[jrie818]

You know what, it's working now. And I didn't change anything. It could be that there's so much traffic going on between the server and the clients that FileMaker refuses to calculate. There were no scheduled tasks running, even the backup. So it's weird that it did that. Anyway, it working now.