ChangeTimestamp columns not working and/or being cleared

[1minManager]

I have a quoting type app that uses a seperate pricelist sheet.  I am trying to set it up so that if an item on the pricelist table changes then the quote record does not update.  But conversely, if the user changes any one of 8 specific columns in the quote then the price DOES update.  

To make this work, I've added 5 ChangeTimestamp columns.  The first 4 ChangeTimestamp columns each monitor a seperate column.  The 5th ChangeTimestamp column monitors the above 4 plus another 4 columns.  Whats weird is i'm getting this:

On some quotes all 5 are updated and on others none are updated.  Which is weird because its all columns start off blank and its impossible to create a price with choosing an option in these 8 columns.  

I've tested it at my end by editing existing records and creating new records.  I cannot do this without triggering all these ChangeTimestamp columns.  The ChangeTimestamp columns are all setup indentical apart from the columns they monitor.  The only setting I've changed from the default is to no show them.

Another quirk is that I have a column which is set as a ChangeTimestamp column for the entire row.  I have one record I've been looking at and using the google sheets edit history.  The row was created at 09:16 and last edited at 09:36.  Yet I have columns where the edit history shows as 09:38.  These related to the user loggin a depsosit was taken against this job.  Yet somehow these where updated without changing the global ChangeTimestamp column as well.

Related to the above, those 5 ChangeTimestamp columns where all cleared at 09:38.  How can this be?  There isn't any action or bot that changes those 5 columns.  So how can adding data in an unrelated column clear all the timestamps in those 5 column and not update the timestamp in the global column?

[1minManager]

Bumping as I got no response before.  To summarise:

1 = I have a ChangeTimestmap column called [RelayTS] which watches [Interface Relay].  Everything is default and ResetOnEdit is turned on.

2 = Here is [Interface Relay].  A simple ENUM with a RequireIF function but NO IntialValue

3 = How than can I have multiple examples every day where 

• [Job Type]="Towbar Fitting"
• [Interface Relay]="yes" or [Interface Relay]="no"
• [RelayTS] is blank

Completely stumped with this one 😞 .  Any Appsheet JEDI's able to help? - @MultiTech @Steve @SkrOYC @Marc_Dillon @Koichi_Tsuji @Joseph_Seddik 

[Marc_Dillon]

I don't think using ResetOnEdit on any "Change..." column type is correct.

I basically never use any of the "Change..." column types. I just always come up with other ways to accomplish my goals. TBH, your setup looks like a mess, I'd suggest figuring out a different way to do it, like with specific Actions, or Bots to capture the appropriate change events, etc.

[1minManager]

I need the price calculation column to update if any of 8 columns are changed.  But not if anything else changes.  Including if the user changes the prices on the sperarate pricelist table. 

Are you thinking I should have a Save Form Action that does a [_THISROW_BEFORE] / [_THISROW_AFTER] check?

[Steve]

Change columns are triggered by Reset on edit?. If the column value changes for any reason at any time in a form view, even if it goes back to its original value, the Change column will record it.

[1minManager]

But thats the thing, it isn't.  I seem to have an App where an ENUM column starts empty, the user changes it, saves the record and the ChangeTimestamp column is blank.

I've tested it multiple times and I cannot get the ChangeTimestamp to remain blank.  It's causing real issues and the client is getting frustrated.

The only thing I can add to this is it seems that something is clearing the ChangeTimestamp column.  There is an action on saving the form.  But I've already tried removing that.  The only odd thing I can see about the rows with no timestamp info is that the creation data and last modifiction date for the row can sometimes be 20-30 apart.  Also I've checked all the bots and action and nothing is set to try and update the ChangeTimestamp columns.  The App doesn't use an API, spreadsheet formulas or Google Scripts.

[Steve]

Possible that another user is adding a row with the same key column value?

Or that an action is adding a row with the same key column value?

[1minManager]

I looked at that.  The key column is [JobID] and uses Unique.  There isn't any duplicates here.

There is some evidence of data being added not sequentially, see below.  But there dosen't seem to be any link between these rows and the ChangeTimestamp column being cleared 

[dbaum]

My understanding—and, I believe, observed behavior—of Change column types is that they don’t require the Reset on Edit property but rather inherently update when a relevant change is saved. I wonder whether selecting Reset on Edit is actually the problem — eg, reverting the column to its initial blank value.  

[1minManager]

Not sure if thats the right answer as the ChangeTimestamp sometimes works 😕

[1minManager]

Quick update.  I turned ResetOnEdit off on all those columns and since then there has been no blank ChangeTimestamps.  

Not flagging this as fixed just yet.  But hopefully this is it!

[1minManager]

Some more info to hopefully help.  Here is the latest data from this morning.

Note its filtered to where [Job Type]="Towbar Fitting" since thats the only time [Interface Relay] is visable.

[JobID]=UniqueID() and key column

[JobCreation]=has Now() as its initialValue to record the DateTime the row was created.  Now idea how we have Rows 1567, 1568, 1569, 1570 & 1573 all with identical creation times

[JobMod]=ChangeTimestamp colulmn which looks at all the collumns

[Quote Number]=Simply [_RowNumber]+40000.  Although not shown here, I have seen this out of sequence with the row numbers

[Interface Relay]=A Yes/No ENUM column with [Job Type]="Towbar Fitting" as both the Show_If and Required_IF

[RelayTS]=ChangeTimestamp column which watches only [Interface Relay].  Some of these cells had a date which was then cleared.  Others say "" was replaced with"

I have no idea whats causing this 😞

[Steve]

I'm afraid I have no insight. You'll probably have to get a developer involved, which means engaging Support.

https://www.appsheet.com/Support/Contact

[1minManager]

Yeah... about that.  The last few things I emailed them they basically do nothing then 4 weeks later ask if its sorted so they can close the ticket.  Really gone down hill fast since Google bought Appsheet 😞

[1minManager]

So a quick update on this.  Firstly I've diagnosed the initial issue of how they were able to create a new row without trigggering changetimestamps. I'd set them up a copy jobs action button using a "Copy row and edit the copy" action.  This was simply copying the changetimestamps from the copied job. So one mystery solved I guess.

But now I'm back on with using ChangeTimestamps to work out if the user changes some particular columns, and if they do then recalculate a price column using a ResetOnEdit formula of [ChangeTimestampColumn]=Now()

But of course, this doesn't work as required...😡  Let me give a simplified example of why

1. Row starts with [A]=1 and [Price]=£100
2. Change to [A]=2, timestamp column updates, [Price]=£200
3. Change to [A]=3, timestamp column updates, [Price]=£300
4. Change to [A]=4, timestamp column updates, [Price]=£400
5. Change back to [A]=1, timestamp column reverts back to the same DateTime it showed in 1 above and [Price]=£400

So from a user perspective the app seems to show both [Price]=£100 and [Price]=£400 for [A]=1

Then I had a brainwave, can we use a ChangeCounter column to pickup on this change back to the origional figure of [A]=1.  Nope.

1. Row starts with [A]=1, [ChangeCounter] is blank
2. Change to [A]=2, [ChangeCounter]=1
3. Change to [A]=3, [ChangeCounter]=2
4. Change to [A]=4, [ChangeCounter]=3
5. Change back to [A]=1, [ChangeCounter] is blank
6. Change to [A]=2, [ChangeCounter]=4
7. Change back to [A]=1, [ChangeCounter] is blank
8. Change to [A]=3, [ChangeCounter]=5

Is it just me that thinks this is illogical?  If I wanted the above behaviour then I could easily just use _ThisRow_BEFORE and _ThisRow_AFTER

So @Steve this "If the column value changes for any reason at any time in a form view, even if it goes back to its original value, the Change column will record it." doesn't appear to be correct

[Steve]

---

@1minManager wrote:

But now I'm back on with using ChangeTimestamps to work out if the user changes some particular columns, and if they do then recalculate a price column using a ResetOnEdit formula of [ChangeTimestampColumn]=Now()

---

This won't work. Reset on edit? is only considered when the user enters the form view; once in the view, it is not considered again.

[1minManager]

Again, not what I've seen.  The user can update a record as much as they want and, except for setting it to its origional value, the timestamp will update and ResetOnEdit sees this change and reruns the initial value formula