The standard solution to this problem is creation of a text field 'xyz_stored' and a script which transfers the actual state of 'xyz' (unstored) into the stored variant which can be searched quickly. The layouts show only the stored field and a button 'update' so users are motivated to click this button when browsing data and so keep the data in the stored field up to date.
Disadvantage is that you possibly search outdated information. This could be minimized with a script running every night updating all records.
Thank you for these suggestions Jens.
I think I understand the principle. This would work well for older records where the status would change infrequently or not at all but would not be so good for newer records where the status of different records changes regularly throughout the day.
I presume the script to update the status would take just as long as searching all the records and omiting the ones I don't want (otherwise I could add the 'update' into my existing script to search and omit).
Is there any way to only search or only update records where the status has changed from last time? Thinking 'out loud' I suppose that would still need to compare an indexed version of the status with the un-indexed version which will take just as long as the 'date modified' won't change where the status is based on a calculation.
I will certainly try out your suggestion and see how it goes but I can see that not having live status updates could prove frustrating for users.
Are there any other options to consider?
The script to update every record will indeed take quite long. This is why I suggested it to be run over night.
You have a chance to apply this technique if you find the point where you have to update single records. The update of a single record (click on button 'update status of this record') is quite fast and the critical question is wether there is any chance that this might fit your needs.
Hmm, that would be a possible alternative.
I suppose I could offer both 'Update All' and 'Update Record' options to users.
It would still be nice to find a solution which doesn't require user interaction.
Incidently, once I have created a script, how do I set it to run automatically?
> Incidently, once I have created a script, how do I set it to run automatically?
FileMaker Server 9 and 10 are able to run FM scripts according to a time schedule.
Thanks for the advice - I think I have found a work around based on your suggestions. I have also added a simple flag to show if the live status is different from the indexed status to encourage manual updating.
Just have to wait and see how users respond to it.
I have FM Server 8 - is there any other way for me to automate scripts?