6 Replies Latest reply on Jan 4, 2017 8:48 AM by erolst

    Update a field based on another field's timestamp

    Stu412

      Happy new year everybody....

       

      I'm working on a very basic ticketing system just for myself and want the system to automatically change the status of a ticket from 'Cooling Down' to 'Closed' exactly one day after I've set it to cool down.

       

      I've set a script trigger which runs when I change the status to 'Cooling Down' to set a timestamp field+86400 (dClosingDate) seconds which is one day in the future.

       

      The issue I'm having now is how to get FM to automatically change the Status field from 'Cooling Down' to 'Closed' when the timestamp field = Get(CurrentTimestamp), ie, sometime tomorrow.

       

      I've tried an auto enter by calculation on the text field for status with the following formula, but after waiting a few seconds...nothing happened!:

       

      If ( dClosingDate = Get(CurrentTimestamp) ; "Closed" )

       

      What am I missing here please?

       

      Thanks

        • 1. Re: Update a field based on another field's timestamp
          philmodjunk

          Stored calculation fields and auto-enter calculations both only re-evaluate if:

          a) a field referenced in the calculation is modified and

          b) that field is from the same record as the field itself

           

          Your calculation wither auto-entered or stored calculation does not have such a field. In order for it to show the correct value as time passes, you'd need to make it an unstored calculation.

          • 2. Re: Update a field based on another field's timestamp
            erolst

            You need to state

             

            If ( dClosingDate <= Get(CurrentTimestamp) ; "Closed" )

             

            otherwise the predicate would be true only for 1 second (the smallest unit in a timestamp).

             

            Also, make sure the calculation field is set to “Do not store …”, or it will evaluate once, and after that only when/if you change the closing date.

            • 3. Re: Update a field based on another field's timestamp
              Stu412

              Thanks for that so far, having a moment here...

               

              Because I'm trying to update a text field, obviously there's nothing in there about it being unstored as you'd normally get in a calculation field.

               

              All the status details I have for the tickets are stored in simple text fields.

               

              So does this need to change to a calculation field in order for the update calc to work as described?

               

              Thanks

              • 4. Re: Update a field based on another field's timestamp
                erolst

                The simple passing of a time threshold as defined in a calculation will not trigger an action; FM only has triggers in the presentation layer, not the data layer.

                 

                The advantage of a field with an auto-enter calc is that you can have your calc and edit it (the field), too. If the latter part is unnecessary, make it a straight calculation field (and don't forget to set the result type to Text).

                • 5. Re: Update a field based on another field's timestamp
                  Stu412

                  Ok, that makes sense...

                   

                  As a part of the solution I have a couple of end of day scripts which will eventually run.

                   

                  Presumably this process can be done a lot easier via a script, given I'm not too worried about absolute live accurate data?

                  • 6. Re: Update a field based on another field's timestamp
                    erolst

                    Stu412 wrote:

                     

                    Presumably this process can be done a lot easier via a script, given I'm not too worried about absolute live accurate data?

                    Not necessarily "easier" as per the logic, but you have better control over the "trigger": it's when you launch the script (directly or as part of closing the file). And you have the status as stored data, which could be useful.