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.
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.
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
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
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