6 Replies Latest reply on Feb 6, 2016 7:05 PM by JeffJ

    Mark record with "absolute" latest modification date and account name

    JeffJ

      I am nearly finished with an involved invoicing project and I would like to make it possible for users to know who last modified a record and when. This is very straight forward with fields that record the creation date and account name and the modified date and account name. What is a problem is related records. I currently have two related "line item" type tables that also have created and modified fields with auto enter functions. Even when a change in the related records results in a new calculation in the main table it is not registered as a modification in the main table. To resolve this I have a system in the main table that will display an "absolute" latest modification time by using Max() functions to calculate the latest date amongst the main and two related tables. Two issues remain. When a record in a related table is deleted it is not registered as a current modification but the Max() functions may recalculate to the last date that a line item was added or the main table modified. Secondly, the account name deleting, adding, or modifying a "line item" is not recorded as the last account name to make a change. How can I record in the main table the latest date of any change in any of the involved tables as being the modification date of that invoice, and what account name made such changes? Thanks

        • 1. Re: Mark record with "absolute" latest modification date and account name
          jamiebah

          Have you looked at the Evaluate () function?

           

          In the help system, navigate as you see below or search for Evaluate

          Home > Reference > Functions reference > Logical functions > Evaluate

           

          You will find the following as the last example:

          The dependent parameter can also be useful in other cases. For example,

          Evaluate(“Get(CurrentTimeStamp)”; [FieldB; FieldC])

          will store a timestamp in the calculation field whenever FieldB or FieldC changes.

          • 2. Re: Mark record with "absolute" latest modification date and account name
            beverly

            I always forget about this function and using field dependencies!

                 https://www.filemaker.com/help/14/fmp/en/html/func_ref1.32.183.html

             

            beverly

            • 3. Re: Mark record with "absolute" latest modification date and account name
              user19752

              Evaluate() can't use with "line item" fields, since

              "changes in other fields of the same record"

              and, if you use fields from related record in the function, the result become "unstored".

               

              You may need script trigger (onRecordCommit) for doing it.

              • 4. Re: Mark record with "absolute" latest modification date and account name
                JeffJ

                All great information. After thinking about it today I am going to try the Evaluate() function in combination with the "modified by" fields in the main and related tables. Further, using a Sum() function in the main table to keep a count of the number of related records in any related table can trigger the Evaluate() when a related record has been added or deleted. I will try that tonight and post the results if successful. Thanks

                • 5. Re: Mark record with "absolute" latest modification date and account name
                  JeffJ

                  It appears to work but it does take a bit of doing. If anyone is interested I will outline the components and technique for one main table and one related table. I actually have two related tables but the concept is the same. This function checks to see if the main record has been modified, a related record has been modified, or a related record has been added or deleted.

                   

                  1. In the related table I have a date field, "Date Modified", that Auto-Enters a modification date.

                  2. In the main table I have a date field, "Date Invoice Modified", that Auto-Enters a modification date

                  3. In the main table I have a calculation field, "Date Line Item Modified" that calculates the Max() (latest) "Date Modified" of all related records

                   

                  4. In the related table I have a number field, "One", that Auto-Enters the digit 1

                  5. In the main table I have a field, "Line Item Count", that calculates the Sum() of the "One" fields in the related table

                  6. Finally, in the main table I have two fields, "Absolute Date Modified" and "Absolute Modified By", that use the Evaluate() function to display data when any of the previous fields change as below:

                   

                  Evaluate ( "Get ( CurrentDate )";[ Date Invoice Modified; Date Line Item Modified; Line Item Count])

                  Evaluate ( "Get ( AccountName )";[ Date Invoice Modified; Date Line Item Modified; Line Item Count])

                  These calculations return date and text data respectively.

                   

                  Thanks for the heads up on the Evaluate() function

                  • 6. Re: Mark record with "absolute" latest modification date and account name
                    JeffJ

                    Well it seems that my logic was flawed in my last post. It was late and I did not fully test the system I outlined there. It appears that the Evaluate() function will ALWAYS see a calculation field as having changed so it perpetually updates. I am going back to the drawing board to attempt to configure a function that checks to see if the main record has been modified, a related record has been modified, or a related record has been added or deleted. A script trigger run on "OnRecordCommit" may work if it is based on the main table record and/or the related table record but what about the case of simply deleting a related record? Any ideas on any of this anyone? Thanks again