6 Replies Latest reply on Nov 1, 2014 4:32 PM by FredH

    Show last modification (cross tables)

    FredH

      Title

      Show last modification (cross tables)

      Post

      Hi Experts,

      I have 2 tables (Customers & Invoices) which are linked.  For each table, I have created an automatic (timestamp) field named _ModifDate. 

      I would now like to show the latest Modification Date (customer & invoices combined for the specific customer) on the customer form.  I would also like to format the modifdate as a date rather than as a timestamp knowing that I show the ModifDate in a regular text field.

      Any idea how I can do this?
      Thanks in advance!
      Kind regards,
      _Fred_

        • 1. Re: Show last modification (cross tables)
          philmodjunk

          If you only want to show the date, why not use a date field that auto-enters the modification date in the first place?

          In the customer table, you could define this calculation that returns the most recent modification date from your timestamp fields (do not use text fields here)

          Max ( GetAsDate ( _ModifDate ) ; GetAsDate ( Last ( Invoices::_ModifDate ) ) )

          Select Date as your return type

          • 2. Re: Show last modification (cross tables)
            FredH

            Hi PhilModJunk,

            Thanks for the suggestion but it does not seem to take into account when changes are made in the Invoices table.  Am I correct that I should configure this not as a calculation but as a date field using auto-enter calculation?

            In fact, I am using Timestamp in the individual tables and Date for the max because for the moment, I just need to know the latets modification on day level but I might need it on a timestamp level later.  Therefore, I prefer to already have the timestamps stored as from now.

            Kind regards,

            _Fred_

            • 3. Re: Show last modification (cross tables)
              philmodjunk

              This should not be a date field with an auto-entered calculation as changes in the related table cannot trigger the needed update. This should be a field of type calculation.

              • 4. Re: Show last modification (cross tables)
                FredH

                Hi PhilModJunk,

                I have tried to use a calculation field the result stays the same.  This is what I have as formula for TableA::_ModifDate_Latest:
                Max ( GetAsTimestamp (_ModifDate_TableA ) ; GetAsTimestamp ( Last ( TableB::_ModifDate_TableB ) ) )

                When I make a change in Table A, _ModifDate_Latest = _ModifDate_TableA but if afterwards I make a change in Table B afterwards, _ModifDate_TableB gets updated but _ModifDate_Latest remains the same (=_ModifDate_TableA).

                Any idea what I am missing here?

                TIA!

                _Fred_

                 


                 

                 

                • 5. Re: Show last modification (cross tables)
                  philmodjunk

                  Ok, that last function is the issue. The relationship for your related table needs to specify that the related records be sorted by the modification timestamp field in ascending order so that the most recently modified record will be the last related record.

                  You could also specify a descending sort order and remove the last function from the calculation.

                  To specify a sorted relationship, double click the relationship line to open the Edit Relationship dialog and then click the Sort button for the related table's side of the relationship.

                  • 6. Re: Show last modification (cross tables)
                    FredH

                    Worked like a charm!
                    Thanks PhilModJunk