Using the "last modified by" field as a reference field?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I'm trying to create a user table as discussed in this other post:  However, it seems that I am unable to set the reference field to [Last Modified By] when creating a relationship between my users table and the table I'm referencing.  I'm not sure why, as both fields are "user" fields.  I realize the post I'm referencing is old, so maybe they've changed the functionality.  Is there another way to approach this problem perhaps?  I've searched around and couldn't find anything on this topic, but maybe it's something dumb that I'm overlooking.
Photo of ThatOneGuyOverThere04

Posted 2 years ago

  • 0
  • 1
I'm not sure why it is not being offered, but try making a new Formula User field called [Last Modified by mirror] with a formula of [Last Modified by].  I'm quite sure that will be offered as a choice.
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
If your "User" table has the "user" as the key field...

Make the relationship as normal, then once it have been made, go to the field properties of the [Related Resource]

Change the field type form user reference to "Formula - User"
Then in the formula field enter:
[Last Modified By]

That will automatically connect the records to the user table based on the last modifying user.
Mark, your solution worked perfectly.  I had tried your idea previously, but I set the field to a Formula Text field like an idiot.  After changing the type to Formula User it is now working properly.

Matthew, I tested what you are suggesting, but I am unable to change the field type of the [Related Resource] to formula - user without breaking the previously established relationship and having it output the last modification to my user table itself.  I also cannot change the [last modified by] field's type in the related table.  I am interested in your solution as it seems a bit cleaner. I'm going to have to connect a lot of different tables to this one user table to monitor records/performance of users using summary fields, so not having to create mirror fields would be preferable.  Would you be willing to elaborate a bit on your explanation of your solution?  I assume I'm probably editing the wrong field or misunderstood what you said.
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
So there are a lot of different ways to get UX reporting for how they are using the app, and modifiying/ creating records.

So I guess the question I have for you is, What are you looking to report on?  What types of reports do you need?  What details are you looking to track?

Is it individual field edits you are monitoring, or just complete record change monitoring?

The reason I ask, is because there are some clean ways and some messy ways to do this, and I want to help you go down the clean path.  And the path that gives you the data you need.
Basically I need to track the last modified field to see how many records are being edited by that person in a day (obviously this wouldn't track if the last modified was already this user, but I would also use the date to exclude this).  The way that I have the data separated basically I'll be tracking those fields for different lengths of time.  1 day, weekly, monthly, etc.  It's going to be used as a performance monitor as they're supposed to be editing a certain amount of records per day.  The actual data that they're editing is unimportant, it's just how many records they're editing for now.  Essentially it would be complete record change monitoring on a per user basis.  Obviously the way that I'll track each one will be set up a little differently depending on their role/interaction, I just want all of their performance data in one place and captured in an automated way.
A thought is to use an Action to create a log entry in a new table. The log detail can be very limited, basically who made the change and when.  The "when" is actually [date created], of the log record so you don't even need a field for that.  You would just need a user field for the Action to write to.

The you have an easy log of records modified in detail and can analyze them to your hearts content and graph you brains out.  That is what I recommend, now knowing your goal.
Wow.  I cannot believe I didn't think of that.  Thanks again for the help guys!
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
I'm on board with Mark here to use actions and a child table.

Just make sure that the condition on the action only triggers when the field [Last Modified By] changes.  

Or some type of logic do you don't get multiple child records for the same day. 

i.e. if they edit one field, and go back again because they missed something.  It would skew your reporting.

So something to catch if it is (1) a new day and (2) who the old user was.
The way I have it set up they should only be editing that record once in a day (each individual user anyway, as the edit process moves along other users will also edit).  

I'll definitely setup the trigger the way you suggested only when the last modified field changes so we're only recording one "event" record for that person for a particular record.  I think that should also help keep them from purposefully padding their numbers.  This method should provide me with lots of other opportunities as well, such as error tracking and things like that.  Absolutely fantastic.