8 Replies Latest reply on Aug 28, 2013 9:59 AM by philmodjunk

    Want to confirm: is calculation field cant be used to relate tables?

    TaylorJansen

      Title

      Want to confirm: is calculation field cant be used to relate tables?

      Post

           In Table A I have a calculation field to calculate the status of the record, the result is a text field. The result maybe either Complete of Incomplete

           In Table B i have an auto-entered field which enter Incomplete to the field. 

           These to fields are linked so that a portal in a layout based on Table B would show records from Table A with status Incomplete.  But it is not working. If I change the relation to relate not a calculation field, the portal works.  is it becasue calculation field cant be used to relate tables?

            

        • 1. Re: Want to confirm: is calculation field cant be used to relate tables?
          raybaudi

               "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.

          • 2. Re: Want to confirm: is calculation field cant be used to relate tables?
            philmodjunk

                 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.

            • 3. Re: Want to confirm: is calculation field cant be used to relate tables?
              TaylorJansen
              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
              • 4. Re: Want to confirm: is calculation field cant be used to relate tables?
                philmodjunk

                     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.

                • 5. Re: Want to confirm: is calculation field cant be used to relate tables?
                  TaylorJansen

                       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. 

                  • 6. Re: Want to confirm: is calculation field cant be used to relate tables?
                    philmodjunk

                         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.

                    • 7. Re: Want to confirm: is calculation field cant be used to relate tables?
                      TaylorJansen

                           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

                      • 8. Re: Want to confirm: is calculation field cant be used to relate tables?
                        philmodjunk

                             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.....