4 Replies Latest reply on Jul 30, 2013 2:36 PM by philmodjunk

    Related record modification by username

    AndrewFoo

      Title

      Related record modification by username

      Post

           I'm trying to get the username that modifies a related record. 

           I have been able to get the most recent modification and creation timestamp via these two calculations: 

      Max (Work History::Record Created) and Max (Work History::Record Modified) 

           I just need to find a way to get the username from the a set of related records that has the most recent modification and creation timestamp.

           The related reocrds are from a child table.

           Any help would be greatly appreciated

           Thanks!

        • 1. Re: Related record modification by username
          philmodjunk

               Careful. In FileMaker you have UserNames and AccountNames. They are not the same thing. Either can be auto-entered into a text field--which I assume is how this data is present in Work History. There are several ways to get the value of such a text field for the related work history record with the most recent creation timestamp or the most recent modification timestamp.

               Option 1:

               For the most recent creation timestamped record, Last ( Work History::UserName ) will return the appropriate value if the relationship to Work History is not sorted.

               Option 2:

               For records with the most recent modification timestamp you can refer to Work History via a relationship that sorts the records by the timestamp field. If you choose an ascending order, the the Last ( ) function will work to return the work History record with the most recent modification time stamp. If you choose a descending order, then you can simply refer to the field from the related table and this will reference the desired value.

               Since you want both Most recently created and most recently modified, you would need two different table occurrences for Work History so that you can specify different sort orders for each.

               Option 3:

               Execute SQL can also be used to extract these values and this would not require adding more table occurrences to your relationships graph.

          • 2. Re: Related record modification by username
            AndrewFoo

                 Would an SQL query like this work?

                 ExecuteSQL ("select Work History::Record Created By where max(Work History::Record Created)"  ; "" ; "")

            • 3. Re: Related record modification by username
              AndrewFoo

                   Since I'm using log-in's I'm assuming the correct term is account name NOT user name. Is this right?

              • 4. Re: Related record modification by username
                philmodjunk

                     Account names are specified in Manage | Security and each is linked to a specific password. User names are specified in Edit | Preferences and are specific to a given computer user account.

                     I don't think max(Work History::Record Created) is a valid WHERE clause. I was thinking more in these terms:

                     GetValue( ExecuteSQL ( "SELECT a.\"Record Created By\" From \"Work History\" a ORDER BY a.\"Record Created\" DESC" ; "" ; ¶ ) ; 1 )

                     Note: ExecuteSQL is very uninformative when you get even one little detail wrong. I recommend using SeedCode's SQL Explorer to help build the correct SQL query.