3 Replies Latest reply on May 14, 2014 4:14 PM by philmodjunk

    Single "Modified By" field and "Modified At" field for multiple tables in a tabbed element

    EricKent

      Title

      Single "Modified By" field and "Modified At" field for multiple tables in a tabbed element

      Post

           I have a layout that has a tabbed element in the center.  On the first tab you enter records in a table for the person making a request, and on the second tab you enter the request details into a second table in lines of a portal.

           I want to create a single field to display outside of the tabbed element that will indicate the last AccountName that modified information in either of the tables, and a single field that will give the timestamp that the modification took place.

           I tried beginning with creating individual fields for each table and just displaying them individually, but the fields for the table in the second tab will not populate.  

           How could I: 1) make the fields for the second table populate; or 2) make a single field for the AccountName and a single field for the timestamp that will adjust regardless of which table was modified?

        • 1. Re: Single "Modified By" field and "Modified At" field for multiple tables in a tabbed element
          philmodjunk

               1) A text (for the account name) and a timestamp (for the modification timestamp) should populate correctly when defined in the portal's table, but only if that specific record is modified. If you have 3 records in the portal and edit a field in row 1, these fields should update to show the specified modification data, but only in row 1 of the portal.

               With some careful relationship design and or ExecuteSQL, a calculation field could return this data as specified for 2) but only after 1) is correctly working as it would be set up to extract the data from the records with the most recent modification timestamp.

          • 2. Re: Single "Modified By" field and "Modified At" field for multiple tables in a tabbed element
            EricKent

                 The fields are set up as you describe and do show the correct data when in the portal rows.  However, I need to display this data to the user outside of the portal using <<Table:Modified By>> and <<Table::Modified At>> (or using some other manner of displaying the data), but when outside of the portal rows this information does not populate.

                 Am I doing something incorrectly here, or will relationships have to be set up to accomplish this?  (I am hoping it does not require relationships because I still do not really understand the relationships or when/how to create them or work with them)

            • 3. Re: Single "Modified By" field and "Modified At" field for multiple tables in a tabbed element
              philmodjunk

                   If you move the field from the portal row to a location outside of the portal row, this field will still show data, but it will be data from the first related record in the portal's table. If this is an unfiltered and unsorted portal, It will show the same data as that recorded in the first portal row.

                   Thus, you can't just drag the fields out of the portal row and expect them to work the way you appear to want them to work.

                   If you want to see the timestamp and account number for the most recent modification of a record in those portal records that are linked to the current layout record, you'll need to do one of the following:

                     
              1.           Put the fields inside a one row portal. Sort the portal records by Modification TimeStamp in descending order and this one row portal will always show the account name and timestamp from the most recently modified related record from the portal.
              2.      
              3.           Keep the fields outside of any portal but go into manage | Database and specify this same sort order for the relationship. You can also create a new occurrence of the portal's table, specify a sort order in its relationship to the layout's table and then add fields from this new relationship in order to keep th existing relationshp to the portal's table unmodified.
              4.      
              5.           In FileMaker 12 or newer ExecuteSQL can be used in a calculation field to show this information.