Searching an unstored calculation field (quickly)
I have a requirement to search an unstored calculation field but, with around 7000 records to trawl through each time, this process isn't especially quick. I appreciate that this isn't a good solution but I am struggling to think of an alternative.
Specifically I need to be able to search the status of client quotes (a calculation field) which changes based on calculations and manual user intervention. The status shows the progress of the quote, whether the client requires a visit, if the quote is passed the due date, when the quote requires follow-up and whether it is lost or accepted amongst other things.
I wish to be able show a list of only those quotes which require action (such as those needing a visit to be arranged, those which are outstanding a quote and those which need following up). However I wish to omit those which don't currently require action (such as those which have a visit arranged or have been sent a quote or where the quote is lost etc).
Setting the status is not a problem but it is based upon other calculations such as whether the current date is after the visit date but before the due date (meaning a quote is required) or if the status is manually set to being lost etc.
FileMaker won't let me index the field (no surprise) as it is a calculation field relying on other calculations fields. Also I don't really want to copy the calculated data out to a 'static' field as the information may change on an hourly basis and this would mean users are relying on 'outdated' information.
Some of the status updates could be set via a script but this wouldn't allow for calculations based on the current date or calculations based on other calculation fields.
As users need access to this information on a regular basis I need to find a solution without the massive search!
Any suggestions would be greatly appreciated!