1 2 Previous Next 18 Replies Latest reply on Jun 4, 2017 7:19 AM by JamesBind

    Auto Enter Modification Fields

    user9259

      I run filemaker scripts on a server schedule to update some fields I display in lists for performance issues...

       

      So when the scripts run through the Modified TimeStamp, Date, Time, Account all get triggered. I would rather not overwrite the last modifications of users... Is there a way to tell the modified Auto Enters...Don't update when I am changing the values of certain fields?

       

      Thanks!

        • 1. Re: Auto Enter Modification Fields
          TomHays

          You need to use Auto-Enter Calculated Value instead of the Modification Timestamp directly.

           

          Create your own calculation that returns Get(CurrentTimestamp) and have that calculation dependent on changes to the fields on which you want it to trigger.

           

          For example you might use Evaluate(expression; [trigger fields]) and put all of your dependent fields in the second argument inside the brackets.

           

          Evaluate("Get(CurrentTimestamp)";

          [Name; Birthdate]

          )

           

          An alternative is to write something like

           

          Let([

          dummyVariable = Left(List(

          Name; Birthdate

          ); 0)

          ]

          Get(CurrentTimestamp)

          )

           

           

          -Tom

          • 2. Re: Auto Enter Modification Fields
            TomHays

            You could also solve the issue by changing your data structure to remove the fields that you change via script from your table and put them into a one-to-one related table.

             

            -Tom

            1 of 1 people found this helpful
            • 3. Re: Auto Enter Modification Fields
              user9259

              thank u sir!!!

              • 4. Re: Auto Enter Modification Fields
                user9259

                how about if NOT this field; Evaluate...In other words; instead of listing all the fields to evaluate; having it not evaluate when a named field is modified. How would u handle that? Thank You!

                • 5. Re: Auto Enter Modification Fields
                  JamesBind

                  I had the same problem, I used a different method. I had to update the ''Today'' function / calculation just after midnight so that the db will run faster the ''next day'' when it is used.

                   

                  All fields / records been updated so could not use a field dependent formula nor a one to one relationship

                   

                  It was long time ago but sill remember the concept, it was published in the FileMaker Pro Bible 4, (attached CD of the book)

                   

                  If you can't get a copy of that CD pls advise and I will recreate one for you and explain here : )

                  • 6. Re: Auto Enter Modification Fields
                    Jason Wood

                    user9259 wrote:

                     

                    how about if NOT this field; Evaluate...In other words; instead of listing all the fields to evaluate; having it not evaluate when a named field is modified. How would u handle that? Thank You!

                    An auto-enter calc recalculates when any field that is referenced in the calc is modified. As TomHays explained there are a variety of ways to include references to fields without them affecting the calculation result, but it won't work in reverse.

                    • 7. Re: Auto Enter Modification Fields
                      TomHays

                      FileMaker's triggering does not make it simple to make a field generate a new modification timestamp when any field except those you list are changed.

                       

                      But it is possible using Get(ModifiedFields) and FileMaker 13 or later.

                       

                      This Auto-Enter Calculation requires a second field that is always triggered.  In this example it is an Auto-Enter Modification Timestamp field named AnyModifiedTimestamp.

                       

                      The two fields FavoriteNumber and FavoriteColor are entered into the calculation as the fields that do not trigger an update to the Timestamp.  Changes to any other field in the record will generate a new value for TimeStamp.

                       

                      -Tom

                       

                       

                      /* Auto-Enter Calculation replaces existing value */

                      Let([

                      /* Make a list of the fields that you want to exclude from triggering this field update. */

                      exemptFields =

                         GetFieldName(FavoriteNumber) & "¶" &

                         GetFieldName(FavoriteColor);

                       

                      /* This requires an always-triggered field such as an Auto-Enter Modification Timestamp field */

                      triggerField = GetFieldName(AnyModifiedTimestamp);

                       

                      tabNameCC = GetValue(Substitute(triggerField; "::"; "¶");1) & "::";

                      exemptPlusTriggerField = List(exemptFields; triggerField; GetFieldName(Self));

                      exFieldsSimple = Substitute(exemptPlusTriggerField; tabNameCC; "");

                      allModFields = "¶" & Get(ModifiedFields) & "¶";

                       

                      /* Set up for up to 10 fields to exclude from triggering.  Add more if needed */

                      userTriggerModFields = Substitute(allModFields;

                         ["¶" & GetValue(exFieldsSimple;1) & "¶"; "¶"];

                         ["¶" & GetValue(exFieldsSimple;2) & "¶"; "¶"];

                         ["¶" & GetValue(exFieldsSimple;3) & "¶"; "¶"];

                         ["¶" & GetValue(exFieldsSimple;4) & "¶"; "¶"];

                         ["¶" & GetValue(exFieldsSimple;5) & "¶"; "¶"];

                         ["¶" & GetValue(exFieldsSimple;6) & "¶"; "¶"];

                         ["¶" & GetValue(exFieldsSimple;7) & "¶"; "¶"];

                         ["¶" & GetValue(exFieldsSimple;8) & "¶"; "¶"];

                         ["¶" & GetValue(exFieldsSimple;9) & "¶"; "¶"];

                         ["¶" & GetValue(exFieldsSimple;10) & "¶"; "¶"];

                         ["¶"; ""]

                      )

                      ];

                       

                       

                      /* Put the calculation you want to use for this field as the default value for the Case */

                      Case(

                      ValueCount(userTriggerModFields) = 0; Self;

                      Get(CurrentTimestamp)

                      )

                      )

                      1 of 1 people found this helpful
                      • 8. Re: Auto Enter Modification Fields
                        user9259

                        This a great solution! Because I always have many fields I want to trigger; but very few that I want to exclude!

                        However I cant make it work. Can u please look at my file and tell me what I'm doing wrong?

                        Thanks!

                        • 9. Re: Auto Enter Modification Fields
                          alquimby

                          Tom,

                           

                               I tried both of your sample auto enter calcs. The alternative one works but I still get a timestamp value in my text field when Name and Birthdate are empty using:

                           

                          Evaluate("Get(CurrentTimestamp)";

                          [Name; Birthdate]

                          )

                           

                               I tried several variations without success, but Evaluate usually baffles me.

                          • 10. Re: Auto Enter Modification Fields
                            TomHays

                            (1)

                            You modified the 10 lines:

                               ["¶" & GetValue(exFieldsSimple;1) & "¶"; "¶"];

                            line to have your own field names.  Don't the original 10.  Only add more lines just like the previous ones with a new number like:

                               ["¶" & GetValue(exFieldsSimple;11) & "¶"; "¶"];

                            if you have more than 10 excluded fields.

                            The exFieldsSimple variable is supposed to be a Let() variable and not one of your fields.

                             

                            In your case you to add a handful more.

                               ["¶" & GetValue(exFieldsSimple;11) & "¶"; "¶"];

                               ["¶" & GetValue(exFieldsSimple;12) & "¶"; "¶"];

                               ["¶" & GetValue(exFieldsSimple;13) & "¶"; "¶"];

                               ["¶" & GetValue(exFieldsSimple;14) & "¶"; "¶"];

                               ["¶" & GetValue(exFieldsSimple;15) & "¶"; "¶"];

                             

                            This is a poor man's approach to removing from one list all items in another list (e.g. ZapValues).

                            If you use such a custom function you could write a simple expression like:

                               userTriggerModFields = ZapValues(allModFields;exSimpleFields)

                            instead of using a limited number of premade Substitute arguments.

                             

                             

                            (2) You need to add more fields to be excluded.  Any field that changes in the record that you don't want to cause triggering of the timestamp needs to be added.  That includes Auto-Enter fields and calculations that will update when you modify trigger-exempt data entry fields.

                            Thus you need to add lines to exclude

                            z_RecordModificationDate

                            z_RecordModificationTime

                            z_RecordModificationAccount

                             

                             

                             

                             

                            -Tom

                            • 11. Re: Auto Enter Modification Fields
                              TomHays

                              Allen Quimby wrote:

                               

                              I still get a timestamp value in my text field when Name and Birthdate are empty using:

                               

                              Evaluate("Get(CurrentTimestamp)";

                              [Name; Birthdate]

                              )

                               

                              I find that this method will produce a current timestamp when the record is created (and Name, Birthdate are blank) but does not change thereafter unless you change the Name or Birthdate fields.

                               

                              Note that the field using this Auto-Enter calc is:

                              Auto-Enter via Calculated value (set to replace existing value - "Do not replace" box is unchecked)

                              It seems to behave the same regardless of the "Do not evaluate if all reference fields are empty" checkbox for the calculation.

                               

                              I tested this on FM Pro 14 Advanced under Mac OS X 10.9.5.

                               

                              -Tom

                              • 12. Re: Auto Enter Modification Fields
                                user9259

                                of I update to use the Let variable, but it is still updating the new_timestamp_modified field.

                                my exempt fields are:

                                text_exempt

                                text_exempt2

                                num_exempt

                                num_exempt2

                                 

                                I understand about the additional fields; I am just trying to get it to work on this one example.Thank You!

                                • 13. Re: Auto Enter Modification Fields
                                  TomHays

                                  user9259 wrote:

                                   

                                  of I update to use the Let variable, but it is still updating the new_timestamp_modified field.

                                  my exempt fields are:

                                  text_exempt

                                  text_exempt2

                                  num_exempt

                                  num_exempt2

                                   

                                  I understand about the additional fields; I am just trying to get it to work on this one example.

                                   

                                  I also am instructing you on how to get it to work with this one example.

                                   

                                  In addition to the data entry fields that you don't want to trigger when they change, there are additional fields that are changing because you set them up that way with Auto-Enter.  Those have to be added to the list in the calculation so that it knows to omit those from the list of fields that trigger a change.  I listed those fields in a previous post.

                                   

                                  You also need to keep the original 10 lines of the

                                     ["¶" & GetValue(exFieldsSimple;1) & "¶"; "¶"];

                                  sequence.

                                  I still recommend that you add more.

                                  At a minimum you need at least two more than the number of fields you have listed to exclude.

                                   

                                  -Tom

                                  • 14. Re: Auto Enter Modification Fields
                                    user9259

                                    I'm lost; I am calling in the Let 4 fields I want to be exempt; I am referencing those in sequence with the exFieldsSimple variable...

                                     

                                    Maybe if you could modify the example I provided then a dullard as myself can understand..

                                     

                                    Thanks!

                                    1 2 Previous Next