13 Replies Latest reply on Mar 11, 2017 2:14 PM by Eldberg

    Get calculated fields to update


      I have a database which contains data about a cemetery. When someone is buried in a grave, the next of kin (usually) becomes the "owner" of that grave for 25 years. When the ownership expires, I need to contact the owner to ask if he/she wants to prolong ownership, or abandon the grave. To display a warning about this, there is a calculated field which looks like this:


      If (yearfield < Year (Get ( CurrentDate )); "EXPIRED"; "")


      The problem is that it does not automatically make this calculation when I open the file. It only recalculates when I change something in the record. I know there is a File option to trigger a script when a window is opened, but I can't write a script that will do this. Recods do not display the warning even if the ownership year has passed.


      I tried to use "update field" but this gives me an error message: there is no field which gets linked data from "expired". I'm stumped.



        • 1. Re: Get calculated fields to update

          In order for your calculation field to update, it would have to be an unstored calculation field. This could, however, make for slow finds and sorts on this field.


          On the other hand, a script could find all records where a text field does not store "expired" but has yearfield < year ( get ( currentDate ) ). It could then update the text field to be "expired" for those found records. Such a script would only need run once a year to update your records.

          • 2. Re: Get calculated fields to update

            Thanks Philmodjunk.


            Considering that, as you correctly note, this only has to be done once per year, it might even be easier to search for the combination of expiration year < current year and replace all those with "expired". Manually, not with a script.


            But what I'd really like is a script that is run automatically every time the file is opened, telling FileMaker to redo the calculation for the field which either contains nothing, or the word "expired". It would be useful to know how to do this, because I may want to use the mechanism in other cases.


            How do I make the calculation field "unstored"?



            • 3. Re: Get calculated fields to update

              Running the script every time the file is opened is possible, but pointless. The calculated value will only change on the first of each new year. I've pointed that out because you might not have realized how seldom this value will actually change.


              If the field is of type calculation, you find it in Manage | Database | Fields and double click it to open the calculation dialog. (If you get a dialog with tabs such as "auto-enter" and "validate", it's not a field of type calculation and you need to change the type.) In the calculation dialog, there's a storage options button that you can click to open yet another dialog where you select an option named "do not store..."

              • 4. Re: Get calculated fields to update

                Running a script that loops through records will also cause record locking. If any records are being edited when the script runs, it will prevent them from being updated. Go with an unstirred calculation like Phil recommends.

                • 5. Re: Get calculated fields to update

                  Any script that does this type of mass update--whether by looping script or Replace Field contents should be run when it is impossible for another user to lock any of the records. Often, this can be done by a script that runs late at night when no other users are accessing the file, but that depends on who can access the file and when.

                  • 6. Re: Get calculated fields to update

                    Probably a rookie comment/question but if your contract for purchase is 25 years and would assume 25 years from date of purchase, wouldn't it be more accurate to use the actual DD/MM/YYYY so you could begin contact a set amount of time prior to expiration?

                    I have a similar solution that uses a simple OnFirstWindowOpen script trigger to set the current date to recalculate a status field (calculation field) so I can contact clients at 30/60/90 days. My solution only contains a few thousand records but seems to recalculate the file very quickly.

                    • 7. Re: Get calculated fields to update

                      The field I am trying to update is a bit of a luxury. It's supposed to bring up "EXPIRED" in red letters for all records where the ownership has expired. If I simply wanted to know the status for an individual record, I could just look at the expiry year which is in a separate field. If I wanted a list of expired records, I could just do a search in that field.


                      I realize that working through the entire database every time it's opened would use a lot of CPU power and take time. I am looking for a way to trigger the recalculation automatically. whether tis happens every time I open the file, or every time I open a record, or even semi-automatically by clicking a button to run a script which would only be needed once per year.


                      Grave ownerships are by whole years; a full date format would be redundant.

                      • 8. Re: Get calculated fields to update

                        And if someone acquires ownership on December 31st, their first "full year" is 24 hours?


                        This is why I have pointed out that your calculation can only produce a new value once every January.

                        • 9. Re: Get calculated fields to update

                          Yes, if you acquire the ownership one minute to midnight on december 31, the first year will last one minute. That is exactly how it works.


                          And I am completely aware of that the recalculation is only needed once per year.


                          At the present time, all those ownerships which expired on january 1st, 2017 are still not marked as "expired" because the recalculation did not happen.

                          • 10. Re: Get calculated fields to update

                            I've already pointed out what you can do. Running the script every time you open the file when it will only be able to change the values once a year is pretty silly.


                            In File Options, the OnFirstWindow trigger can be selected to run a script. If you are hosting the file from server, a schedule can run it once a day--early in the morning after midnight. Such a script can add a record to a "log" table set up for that purpose. A field in that record can auto-enter today's date when created. Your script, whether run once a day or each time the file is opened, and check the date in the most recently created record and either exit without doing anything or iit can add a record to the log and do the needed update.


                            But I'd try just using an unstored calculation first and only go to this option if I get delays that I don't like.

                            • 11. Re: Get calculated fields to update

                              Would a simple text box on the layout with a 'Hide object when" calculation of (Year (Get(CurrentDate))) - (Yourtable:ExpiryYear) ≤0 work?


                              Not sure how this would impact efficiency, but as it is just evaluating one calc, on each alyout enter/record load, it should be fine.

                              • 12. Re: Get calculated fields to update

                                You couldn't use it in a find or sort on it.

                                • 13. Re: Get calculated fields to update

                                  OK finally:

                                  Just wanted to recapitulate how this was ultimately solved. Philmodjunk and bcameron together guided me to this. Thanks guys!



                                  I created a new date field with global storage, to contain today’s date. I called it ”today”.



                                  Then a script:

                                  1. Erase [table::today]

                                  2. Assign field [table::today; Get (CurrentDate)]

                                  3. Execute record/searchpost



                                  I then set this script to be triggered every time the file is opened.


                                  So every time I open the file, the ”today” field will be changed to contain today’s date, in every record.


                                  To know if an ownership has expired, I need to compare today’s year with the contents of the numeric field ”year_of_expiration”. So the field called ”expired” has this calculation:



                                  If (Year (today) ≥ year_of_expiration; ”EXPIRED; ””)



                                  Problem solved.