Display last modified date and useremail

[paula]

Hi all,

I'm using google sheets as my data source for my app. I learned there's Edit History to see all who made edit/s and the dates altogether. I think that's for Admin to see. For the end user, how can I make that in Appsheet so I could display it? I'm done with last modified date. I need the last modifier.

Thank you!

[1minManager]

You can't pull in the edit history from google sheets into appsheet.  The simplest way to see the last edit date for a record would be to have a DateTime column of where the AppFormula is Now()

Simon, 1minManager.com

[paula]

Thank you but I'm done with that, I need last modified by.

[mateoc15]

I believe I have an answer AND a follow up question.

The best way I've found to do this is two columns.  ModifiedBy and ModifiedTimestamp.  Timestamp's initial value is NOW(), the ModifiedBy is set to USEREMAIL() or user name or whatever you like, and you have to set update behavior for both Reset on Edit? to true.  This forces the initial value to recalculate to current user and time.

[mateoc15]

My follow up question... I have a ModifiedBy which is a USEREMAIL() of type Email.  It's being updated as expected per my description above.  However, I also have a ModifiedByDisplayName, which tries to find an employee's name to display rather than an email, and just the email if it can't be found.  The expression is below.

ifs(
   isblank(lookup([Modified By], "Employees", "Modified By", "Display Name")), [Modified By],
   true, lookup([Modified By], "Employees", "Modified By", "Display Name")
)

The display value is NOT being updated with the employee's name, despite being in the employee table.  Any ideas?  Maybe my ifs() condition is off, or the use of isblank()?  I have done a save and validate data, as well as manually refreshing the data in the app preview window.  Nothing.

[frankarv]

Try:

If(
Isblank(Lookup(Useremail(),"Employees", "Modified By", "Display Name")) = True,

[Modified By],

Lookup(Useremail(),"Employees", "Modified By", "Display Name")

)

Also if this is something you would like to happen when someone "Quick Edits" the row, you will need Automation, but if this is an edit directly in the form and the user needs to "save" then it'll be easier if you make an action and insert it in the "save" behavior of the form and use the same formula in the action, just set it to: Data: set the values of some columns in this row

Edit:

Also if you want to use only one column for this you can concatenate the date on your formula and just set the column type as text

if you wish to keep using the formula you have you can try with adding this on your lookup:

Lookup([_THISROW].[Modified by]......

[mateoc15]

This is what I ended up with.  This reply was helpful and it's what I ultimately implemented.  AND I shot myself in the foot with a bad lookup.

If(
   Isblank(Lookup(Useremail(),"Employees", "Modified By", "Display Name")) = True,
   [Modified By],
   Lookup(Useremail(),"Employees", "Modified By", "Display Name")
)

SHOULD BE

If(
   Isblank(Lookup(Useremail(),"Employees", "EMAIL", "Display Name")) = True,
   [Modified By],
   Lookup(Useremail(),"Employees", "EMAIL", "Display Name")
)

It was actually just looking up the name of who updated the EMPLOYEE record, not using the actual email for the employee!

[SkrOYC]

So you already have a user table and don't mind showing the email if the user was not in the dataset, so.... why don't you save some performance and go the Enum/Ref route? Just curious

[mateoc15]

Good question, with no good answer! I'm still pretty new to Appsheet so if performance becomes an issue I'll look at it. Would you expect the difference to be significant? Anything else to consider? 

[SkrOYC]

---

@paula wrote:

last modified by.

---

Same with USEREMAIL()

[SkrOYC]

---

@mateoc15 wrote:

Any ideas? 

---

User table with Email as Key and Name as Label and change the modified by column to be Enum basetype Ref to the User table