Use DateTime like ChangeTimestamp

[Fabian_Weller]

I want to save the DateTime when a user edits a certain column.
Usually we would use the ChangeTimestamp column type for that.

But I want to use a DateTime column type. Because I need to change this value also via an action. And this is not possible with a ChangeTimestamp.

Does anyone know a way how to do that without a Bot?

[MultiTech · Accepted solution]

Okay... so inside the form, if you do a LOOKUP() - that's essentially the same thing as [_thisrow_before]

And at that point, there is no [_Thisrow_After]... yet; you've got a [_ThisRow]... like...[_ThisRow_NOW] type of thing with all the edits made inside the form.

So you can do:

Lookup([_thisrow], Table, Key, [Value]) <> [Value]

This will let you modify the [Value], while looking up the old one for your comparison.

Try that for your reset on edit. 

[MultiTech]

You can get extremely clever with "reset on edit" - to the point where, if you can find the right logic formula, you can remove many of the actions you would otherwise use to modify a column.

It's just a matter of finding a perfect mix of:

• The initial value formula
• The reset on edit formula
• and how these two play with each other

------------------------------------------------------------------------------------------------------------

You might try something like:

"Initial Value"
  NOW()

"Reset on edit"
  or(
    IsNotBlank([Column_1]), 
    IsNotBlank([Column_2]), 
    IsNotBlank([Column_3])
  )

You could even get away with some of the intelligence the change columns have for watching for certain values:

or(
  IsNotBlank([Column_1]), 
  [Column_2] = "Open", 
  and(
    [Column_2] = "Open", 
    IN([Column_3], LIST("Option 1", "Option 2"))
  )
)

----------------------------------------------------------------------------------------------------------------------------

The idea being:

• I've got an initial value formula, driving the value of this column
• I've got a reset on edit formula, driving the initial value

Between the two... you can make some really clever things. 😉

[MultiTech]

Here's a few examples, pulled from production apps deployed and in use right now, to consider:

• This one clears a column if the value inside it is no longer inside the available options in the validation formula

"Initial Value"
null

"Reset on edit"

and(
	IsNotBlank([_this]), 
	not(in([_this], 
  	if(IsNotBlank([Timesheet_Current_Termination]), 
      Intersect(
        [Timesheet_Location].[ALL_Remaining_Phases], 
        [Timesheet_Current_Termination].[CableConn_Remaining_Field_Reports]
      ), 
    if(IsNotBlank([Timesheet_Current_Core]), 
      Intersect(
        [Timesheet_Location].[ALL_Remaining_Phases], 
        [Timesheet_Current_Core].[Core_Remaining_Field_Reports]
      ), 
    if(IsNotBlank([Timesheet_Current_Cable_End]), 
      Intersect(
        [Timesheet_Location].[ALL_Remaining_Phases], 
        [Timesheet_Current_Cable_End].[CableEnd_Remaining_Field_Reports]
      ), 
    if(IsNotBlank([Timesheet_Current_Cable]), 
      Intersect(
        [Timesheet_Location].[ALL_Remaining_Phases], 
        [Timesheet_Current_Cable].[Cable_Remaining_Field_Reports]
      ), 
    [Timesheet_Location].[ALL_Remaining_Phases]
    ))))
  ))
)

• Here's another one like that, but much simpler

if(IsNotBlank([_this]), 
	not(in([_this], [Current_Substation].[Related Strings])), 
false)

---------------------------------------------------------------------------------------------------------------------------------------------------------------

• This one monitors if a specific setting is turned OFF for a user; the idea being, if they turn off test mode, I want this column to change itself to false along with their edit.
  • (Otherwise they'd need to go and change this themselves, or I'd need to use an action to change the value;  and if I simply used an app formula, then the user couldn't change this value if and when they needed to.  This way, I get the best of both worlds, without having to involve any data change actions.)

"Initial Value"
false

"Reset on edit"
[Test_Mode] = false

[Fabian_Weller]

Thank you very much @MultiTech for sharing these nice examples. I also love what we can do with Reset on Edit.

I just learned that we can see if a Reset on Edit happend:
[_THISROW_BEFORE].[Column_To_Watch] <> [_THISROW_AFTER].[Column_To_Watch]
With that you check if [Column_To_Watch] had a Reset on Edit.
How may this help? If you need the same Reset on Edit Expression in several columns, you need to put the expression only in 1 column. The other columns can watch the first column. So you don't need to maintain the expression in every single column.

But I need your help again, Mat. What I want to do is, I want to check if a user changes a value while in the form view.
[_THISROW_BEFORE] <> [_THISROW_AFTER] is working only on opening the form view. But I couldn't find a way to check if a user changes a value while in form view. The mechanism should work like ChangeTimestamp.

[MultiTech]

---

@Fabian_Weller wrote:

I just learned that we can see if a Reset on Edit happend

---

Where did you learn this?

[Fabian_Weller]

I just gave it a try and it worked 😀
I know that we can use this [_THISROW_BEFORE] <> [_THISROW_AFTER] for Bots. But sadly we cannot use it as the form's save event. Because AppSheet cannot know anymore what the value was before. If we could, this would solve my problem.
So I thought to just try it and Tataaaa! It works at least for the moment, we open a form view.

[Fabian_Weller]

I think @Koichi_Tsuji once had a solution with (I think) an extra column. Can you help me with this? I can't find your post.

[MultiTech · Accepted solution]

Okay... so inside the form, if you do a LOOKUP() - that's essentially the same thing as [_thisrow_before]

And at that point, there is no [_Thisrow_After]... yet; you've got a [_ThisRow]... like...[_ThisRow_NOW] type of thing with all the edits made inside the form.

So you can do:

Lookup([_thisrow], Table, Key, [Value]) <> [Value]

This will let you modify the [Value], while looking up the old one for your comparison.

Try that for your reset on edit. 

[Fabian_Weller]

This is very cool thank you @MultiTech 
It's almost perfect. Just in case a user changes the value and then change it back. The DateTime will update, even though it should not. 
As you see in this GIF, DateTime will update if any other value then the original is chosen. If I choose the original again, DateTime will not change again.

This GIF shows the behavior compared to ChangeTimestamp column type.
ChangeTimestamp just changes once and changes back if you chose the original value again.

[Suvrutt_Gurjar]

Hi  @Fabian_Weller and @MultiTech ,

If I may join the post thread.  A modest attempt at suggestion.

@Fabian_Weller ,

Based on understanding of your requirement, you may want to consider the below workaround, even though , it requires an additional column.

1) Add a column called [TrackEnum] of type ChangeTimeStamp.  In type details of this column add the change column as "Enum" which is the column you are changing in your test app,  Initial value NOW(). You can hide this column with Show_if

2) Now your ChangeTimeStamp column of DateTime type can have following settings

Initial value :  [TrackEnum]

Reset on Edit: OR ([_THISROW_BEFORE].[Enum]<>[_THISROW_AFTER].[Enum] , [_THISROW_BEFORE].[Enum]=[_THISROW_AFTER].[Enum])

You can also set an action on this column to change it.

Edit: A change in reset on edit expression suggested to take care of a condition. Change highlighted in bold.

[Fabian_Weller · Accepted solution]

---

@MultiTech wrote:

Lookup([_thisrow], Table, Key, [Value]) <> [Value]

---

Thanks to @jyothis_m I just found out that instead of this expression we can also use
[_THISROW_BEFORE].[Value]<>[Value]
to check if a user changes a value while in form view.

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Difference-between-THISROW-THIS-and-THIS-in-Form...

[jyothis_m]

Also please check if the behaviour is same in the new desktop ux. I found some different behaviour in desktop ux. 

[MultiTech]

I'd b

---

@Suvrutt_Gurjar wrote:

Reset on Edit: [_THISROW_BEFORE].[Enum]<>[_THISROW_AFTER].[Enum]

---

I'd be careful using this [_thisrow_before] stuff outside of automation....

• In most client-side instances, this doesn't exist in context - so it will bring strange results

---------------------------------------------------------------------------------------------------------

This page: https://support.google.com/appsheet/answer/11547057?hl=en

Is in the automation documentation...

• Nowhere in that page, or anywhere that I've come across in the documentation, does it say you can use that outside of automation

[Suvrutt_Gurjar]

Thank you @MultiTech for your insights.  There are following posts that indicate  [_thisrow_before] and [_thisrow_after] can be used outside automation as well.

1.  Release note : mentions [_thisrow_before] can be used in actions.

Release notes: 7/14/20 - Google Cloud Community

The following community posts also suggest use of _THISROW_BEFORE in app formulas.

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_R... - Page 7 - Google Cloud Community

Solved: Documention on [_THISROW_BEFORE] and [_THISROW_AFT... - Google Cloud Community

@Steve : May we request your guidance. 

@lizlynch : May we request your insights and accordingly correct the documentation by emphatically mentioning if  [_thisrow_before] and [_thisrow_after] can be used outside automation as well.

[MultiTech]

I remember that announcement... I also remember it NOT working when I needed it to - so I've been avoiding using it in production apps.

Indeed, just the other day I tried that and it didn't work in my scenario. 😞 

But I'm sure it probably would work in others

----------------------------------------------------------------------------------------------------------------------------

This is the story of my life:

• Documentation says it works
• Developers say it works
• Everyone else says it works....
• Yet when I try it... it doesn't

#I'mJustACrazyPersonIGuess 😜

[lizlynch]

@Suvrutt_Gurjar - Thank you for flagging me about this! @Steve confirmed that this feature is no longer specific to automation. So I've moved the topic out of the Automate section. 🙂 Thanks for helping improve the documentation, as always!

Liz

[Suvrutt_Gurjar]

Thank you very much @Steve  and @lizlynch 

[Steve]

[_THISROW_BEFORE] and [_THISROW_AFTER] are available outside Automation, but only in data-change contexts, when there are distinct before and after values. They are available in column constraints, but not in format rules, for instance. The documentation was never updated to reflect the enhancement.

[Steve]

---

@MultiTech wrote:

• Here's another one like that, but much simpler

if(IsNotBlank([_this]), 
	not(in([_this], [Current_Substation].[Related Strings])), 
false)

---

Equivalent:

And(
  IsNotBlank([_this]), 
  not(in([_this], [Current_Substation].[Related Strings]))
)

 Sufficient:

not(in([_this], [Current_Substation].[Related Strings]))

[MultiTech]

Essentially..... yes. But there's actually a nuance in my formula, which I'm actually taking advantage of in my case.

• Since I've wrapped the whole thing in IF(IsNotBlank(.... with the fallback being false... the processing load for this scenario is much lighter than checking the IN() statement all the time
  • Essentially I'm saying: If there's nothing there, don't even BOTHER trying

[Suvrutt_Gurjar]

Thank you @Steve  for this clarification.  Your clarification and documentation updates will help app creators in safely using it outside automation.