Tracking Method for entries (Status Column)

DataShogun · 11-14-2023 11:37 AM

Currently been looking to implement a tracking method/audit fields on the Google App Sheet composed of these Columns below. However I've been having some issues setting up the columns bolded, any suggestion would be greatly appreciated:

Entry Status: Created, Modified, Deleted
Created By: returns USEREMAIL() on initial value
Created Time: returns NOW() on initial value
Last Modified By
Last Modified Time
Deleted By: I have currently set an action on 'Delete' to return USEREMAIL()
Deleted Time: I have currently set an action on 'Delete' to return NOW()

I'm currently having trouble establishing the bolded fields.

For 'Entry Status' I'm able on initial value to return "Created" and also setting "Deleted" when the custom action 'Delete' is clicked. However, I'm having trouble setting the "Modified" status: how would I switch this column to "Modified" whenever a user Edits the corresponding entry?
For 'Last Modified By' I would like to set it to USEREMAIL() whenever an entry is edited, however it doesn't seem like there is a column type for this. Is there any workarounds for this?
For 'Last Modified Time' I'm able to use the 'ChangeTimeStamp' column type, however it seems to also generate an time right on entry creation too... how would I modify this so it only generates a timestamp when it is modified the first time (not on creation)?

Thanks for your help!

Suvrutt_Gurjar

@DataShogun wrote:

For 'Last Modified By' I would like to set it to USEREMAIL() whenever an entry is edited, however it doesn't seem like there is a column type for this. Is there any workarounds for this?

For 'Last Modified Time' I'm able to use the 'ChangeTimeStamp' column type, however it seems to also generate an time right on entry creation too... how would I modify this so it only generates a timestamp when it is modified the first time (not on creation)?

You could try app formula setting of USEREMAIL() in an email type column for the [Last Modified By] column,

You could try app formula setting of NOW() in an datetime type column for the [Last Modified Time]

@DataShogun wrote:

For 'Entry Status' I'm able on initial value to return "Created" and also setting "Deleted" when the custom action 'Delete' is clicked. However, I'm having trouble setting the "Modified" status: how would I switch this column to "Modified" whenever a user Edits the corresponding entry?

In the initial value of the [Entry Status] column, please try an expression of

IF(IN([Key column], Table_name[Key column]), "Modified", "Created")

Also please enable "reset on edit" setting.

Here table name is the table whose entries you are racking. The above approach will work to change [Entry Status] column in form edit mode. If you are using actions to modify some other columns of the table, then you may need action to change this [Entry Status] column also.