"is it becasue calculation field cant be used to relate tables?"
No, it is because most likely the result of your calculation field is unstored.
Other details may also affect why this isn't working for you. If Raybaudi's response doesn't resolve the issue, please describe what you have set up in more detail and include a description of how this is "not working" for you.
My problem is due to unstored. I changed the field to store the calculation. But I wonder if there are any impacts on changing the fieldtostore the calculation result
I recommend that all calculation fields be specified as "Stored" if it is possible to do so. Searches, Sorts and Layout updates are much faster when they reference data in a stored calculation field as opposed to unstored.
Where you pay a price for this is when doing batch updates of very large numbers of records such as via Replace Field Contents or Import Records where FileMaker then has to re-evalutate and update both the field and any associated indexes. So the exception to this is when you have tables with very large numbers of records and are encountering unacceptable delays doing those type operations.
I encounter another problem after the field changed to stored.
The calculation involves Get(CurrentDate) Function. I found the result doesnt update. Any way to solve this problem?
This a field to calculate the status of the record, the result would be Overdue, To Do, Void, Normal based on another date field in the same table by comparing with today's date.
I recommend that all calculation fields be specified as "Stored" if it is possible to do so.
Unfortunately, Get ( functions ) will not automatically update in stored calculations so it is not possible for your calculation to be stored.
You may find that instead of that relationship, you can use a script to perform a find. Such a find will still work when it specifies criteria in this field even though it is unstored.
To get a field that works in your relationship, I think, you'd need to set up a script that runs once a day to find records where today's date merits a status change and updates a text field accordingly.
So I think I will go the second way, doing a script once a day, as I have conditional value list based on this calculation field too.
But how can I perform a script once a day? In that script , is it to update the date field so that the calculation can update the status of the record? I have no idea. Please help
You can set up the status field as a simple text field. Then your script can use date criteria to perform a find to find those records for which the status must change. The script can then use Replace field contents to modify the value of the status field for all the found records in one batch update process.
To run a script once a day:
If you are hosting the file with FileMaker Server, use a server schedule to perform the script once a day. This is a nice option as you can set it up to run a few minutes after midnight when it is not likely to interfere with users accessing the file at the same time.
You can also use an OS based task schedulaer such as Windows Scheduled Tasks to open a special "Robot" FileMaker file. File Options in this robot file are set to automatically perform a script. That script uses the perform script step to perform the update status script in your main file. This can also be set up to run during a time when users are not accessing the database.
You can run the script manually once a day by clicking a button. You might do that at close of business for the day to update the status field for the next day....
A script can be set to run everytime the file is opened or everytime the file is closed. That script can check other values at that time to keep from running more than once a day and to keep from running at the wrong time. A script can detect that the current user is the host and thus if you are hosting with FileMaker Pro, it can kick in only when the file is first opened or last closed on the host machine. A script can set a value in a field in a table and check for that value to determine whether or not the script has already run today.....