9 Replies Latest reply on Feb 17, 2017 8:36 AM by philipHPG

    Updating a field

    garry107

      I have a field for "days remaining" for a deadline.

      I have the calculation entered as such "Certification Action date -  Get ( CurrentDate )" where Certification action date is the date entered which is the deadline. When I create a record that day the field "days remaining" works fine. But "days remaining"  shows the same value everyday, I need that updated everyday or whenever I open that record. Let me know how that can be achieved. Also is there any way where I can achieve the following-

      When the days "days remaining" field falls below 30 there will me an email sent to me to remind it?

       

      Thanks,

      garry

        • 1. Re: Updating a field
          philipHPG

          Check to make sure the calculation field is an unstored calculation field. If it is unstored it will re-evaluate when viewed. As stored, it only calculates when the record is originally created.

           

          As for your second question - if you are using FileMaker Server you could set up a scheduled script to check on a daily basis for records that meet your criteria (and e-mail you accordingly). Or, if you don't have FileMaker Server, you could set up a script that runs when the application is opened that checks for records that meet the criteria (and e-mail you according

          • 2. Re: Updating a field
            garry107

            How to check if the calculation field is stored or unstored?

            • 3. Re: Updating a field
              garry107

              Got it

              • 4. Re: Updating a field
                garry107

                I dont have filemaker server. What would be the script for the second question without filemaker server.

                • 5. Re: Updating a field
                  philipHPG

                  There are a lot of factors to consider depending on what you want to do with it.

                   

                  In pseudo-code it could be something like:

                   

                  Go To Layout [ Layout based on table occurrence that has Certification Action date ]

                  Enter Find Mode []

                  Set Field [ Table Occurrence Name::Days remaining ; "< 30" ]

                  Set Error Capture [On]

                  Perform Find []

                  Set Error Capture [Off]

                  If [ Get ( FoundCount ) > 0 ]

                     Send Mail [ Mail configuration here ]   **If you want, choose "Multiple emails (one for each record in found set)"

                  End If

                   

                  I would suggest you have a field to flag when the reminder was actually sent (otherwise you will get a reminder every day once the days remaining drops below 30), so you could create the ReminderSent field and add the following to the end of the script (just above the End If statement).

                   

                  Replace Field Contents [ No dialog ; Table Occurrence Name::ReminderSent ; Get ( CurrentDate ) ]

                   

                  and then add the following criteria to the Find operation (to make sure that multiple reminders aren't sent):

                   

                  Set Field [ Table Occurrence Name::ReminderSent ; "=" ]

                   

                  Hope that helps.

                  • 6. Re: Updating a field
                    garry107

                    I understand what you are trying to say.

                     

                    I wrote the script according to this

                     

                    Go to Layout ["Customers"]
                    Perform Find [Restore]
                    Send Mail [Send via E-mail Client; No dialog; To: Customers::Email; Subject: Invoices::Summary; Message: "Dear Customer,¶¶Thank you for your business."]

                     

                    My question is how do perform the script everyday?

                    • 7. Re: Updating a field
                      philipHPG

                      With FileMaker Server you can easily set up a scheduled script that will run on a daily basis. With FileMaker Pro you would need to set it up to be triggered by something you do on a daily basis - such as opening the database (OnFirstWindowOpen) or going to a specific layout (OnLayoutEnter). You would set up a script trigger that would launch that script, depending on your work processes.

                      • 8. Re: Updating a field
                        garry107

                        how about Install On timer?

                        • 9. Re: Updating a field
                          philipHPG

                          If your database is constantly open (almost never closes), then, yes, that could be a possibility. You could still create an OnFirstWindowOpen script trigger that initiates the timer so that when you take down the database for backups (you are making backups, right?), the timer is automatically restarted when the database is re-opened.

                           

                          You can only have one active timer in a window so if you use timers in other aspects of your database, that would also preclude this option.