ChangeTimestamp not working when using a BOT

Harriswe · 06-14-2024 05:23 AM

Has anyone experienced a column set to ChangeTimestamp failing to be populated in the table when using an Automation BOT?

I have a BOT that logs events in the Log table when sending a Service Request to a Tradesperson. However, the LastUpdatedOn column is not being populated and remains NULL, even though it is set as a ChangeTimestamp.

AppSheet defintion

Additionally, other default behaviours for calculated initial values, such as AddedBy and LastUpdateBy, are not updating correctly. When run from the editor as userABC, the developer email details are recorded instead.

After a lengthy online chat with support, they suggested changing the initial formula for LastUpdatedOn to TimeNow(). 🤔

Does anyone have any ideas or solutions for this issue?

Suvrutt_Gurjar

@Harriswe wrote:

After a lengthy online chat with support, they suggested changing the initial formula for LastUpdatedOn to TimeNow().

Instead of TimeNow() , you may want to try NOW() and instead of ChangeTimestamp type , please try just a Datetime type column if you are always looking to update it from bot.

Harriswe

Thank you @Suvrutt_Gurjar 🙏 for the suggestion. Changing the ChangeTimestamp type to DateTime has resolved this one issue. However, how practical is this approach going forward, given that the default is to use ChangeTimestamp?

Does this mean that whenever we use a Bot to update a table, we first need to change all ChangeTimestamp columns to DateTime for them to work properly with the Bot? 🧐

While this might work as a short-term workaround, it's not a long-term solution. It also doesn't explain why running the process as UserABC in the editor results in the developer's email address being used for the AddedBy and LastUpdatedBy columns.

What is the suggested workaround for this issue?

Suvrutt_Gurjar

@Harriswe wrote:

Does this mean that whenever we use a Bot to update a table, we first need to change all ChangeTimestamp columns to DateTime for them to work properly with the Bot? 🧐

No not at all. I believe we need to understand the use of ChangeTimeStamp column. Wheneever any column changes in the record, the changetimestamp column changes automatically. One cannot explicitly set a changetimestamp column through app formula or through bot.

One can set what columns in the same record the Changetimestamp column should listen to for changes. In the following example, the changetimestamp column will change (capture the timestamp of change) when any column in the Orders table record changes.

The following ChangeTimestamp column will automatically change only when [Order Status] column changes to any other value from a previous value.

The following ChangeTimestamp column will automatically change only when [Order Status] column changes to "Open" from any other value..

In summary, the changetimestamp column listens to changes in other columns of the record and captures the timestamp when any of those defined columns in the settings change.

If in your bot some other columns are changing that the Changetimestamp column has been set to listen to it will automatically change.

In your current case , it sounds that you want to capture a change time stamp when any column in a record changes. This can be done by "Datetime" type column through a bot or more easily by a changetimestamp column without a bot. It will automatically change when a record changes.

Hope I could explain.

@Harriswe wrote:

While this might work as a short-term workaround, it's not a long-term solution.

You may want to elaborate why you feel so.

@Harriswe wrote:

It also doesn't explain why running the process as UserABC in the editor results in the developer's email address being used for the AddedBy and LastUpdatedBy columns.

Please take a look at the following thread that is similar to your requirement.

Solved: Curious cases of "Preview App as" - Google Cloud Community

Harriswe

Hi @Suvrutt_Gurjar I am not attempting to update the ChangeTimestamp field within the BOT. Instead, I am relying on its default behaviour to automatically update, but this isn't happening.

The issue is that the ChangeTimestamp column is not being updated at all when a new record is created or amended.

Not a long-term solution
because when building the app you tend to favour the automated function provided through use of type: ChangeTimestamp. Now you are suggesting NOT to use this 'type', which means remembering to change the type back to Datetime if the developer anticipates updating this table using a BOT.

I disagree that this is a solution; it is merely a workaround. The root problem is that columns of type ChangeTimestamp are not updating correctly when the table's ADD or AMEND actions trigger a follow-on action as part of the Automation.

Suvrutt_Gurjar

@Harriswe wrote:

Hi @Suvrutt_Gurjar I am not attempting to update the ChangeTimestamp field within the BOT. Instead, I am relying on its default behaviour to automatically update, but this isn't happening.

Okay , got it. Somehow I thought you are trying to get the ChangeTimestamp to change through bot based on the following description in your post. The description somehow sounded to state that the column is changed through bot. Sorry about my interpretation.

@Harriswe wrote:

Has anyone experienced a column set to ChangeTimestamp failing to be populated in the table when using an Automation BOT?

@Harriswe wrote:

Hi @Suvrutt_Gurjar I am not attempting to update the ChangeTimestamp field within the BOT. Instead, I am relying on its default behaviour to automatically update, but this isn't happening.

The Changetimestamp column is perfectly changing when I change a column through bot that the Changetimestamp column is listening to. It is changing automatically.

You may want to elaborate your bot configuration.

Harriswe

Hi @Suvrutt_Gurjar thank you for your rely. The Bot is not amending any of these audit columns. It is relying on the default behaviour you described earlier.

Unfortunately, the default behaviour of ChnageTimestamp it is not working in the app. As the name implies, the Log table records changes and events to the client's data. Therefore, it is crucial that the audit columns are maintained correctly when creating a new record or updating an event such as a calendar entry.

Specifically, these columns are:
- AddedOn
- LastUpdatedOn

This necessitates reliance on the ChangeTimestamp 'type'. I have since raised a support request to help identify the cause.

Suvrutt_Gurjar

Thank you for the update.

Request you to update this post thread as and when you find a solution, so that it can be useful to anyone revisiting this post thread.

Harriswe

@Suvrutt_Gurjar , the Google support team has successfully recreated the issue and is currently working on a resolution.

Suvrutt_Gurjar

Great. Thank you for the update.