Differenciate new and updated record within one single bot

[Aurelien]

Dear Community and AppSheet Champions 🤠

Among the suggested bots, we have the suggested event "A record is created or updated".

I would like to be able to differenciate the two situations, without having to create one bot for each single-event data change.

I thought about this, but that does not work:

Would you happen to have an idea?

[Suvrutt_Gurjar · Accepted solution]

Maybe not very economical  as it involves adding a column 🙂but the following works .

Please add a ChangeCounter type column , with Accumulate mode and initial value as 0 that changes on all column changes or you can set the columns you want to designate as "Update Changes"

Then your branch condition for add/update can be 

AND([_THISROW_BEFORE].[TESTCOUNTER]<>[_THISROW_AFTER].[TESTCOUNTER], [_THISROW_AFTER].[TESTCOUNTER]>1)

[TESTCOUNTER] is ChangeCounter type column here.

The Y branches to Update record Email and N branches to Add Record email

[JpChapron]

Here's my proposition :

IF(ISBLANK([_THISROW_BEFORE]), "Do whatever", "Do whatever if the row isnt blank)

Edit : OR, I'm using this one in a certain bot, working well :

AND(ISBLANK([_THISROW_BEFORE]),ISNOTBLANK([_THISROW_AFTER]))

[Suvrutt_Gurjar · Accepted solution]

Maybe not very economical  as it involves adding a column 🙂but the following works .

Please add a ChangeCounter type column , with Accumulate mode and initial value as 0 that changes on all column changes or you can set the columns you want to designate as "Update Changes"

Then your branch condition for add/update can be 

AND([_THISROW_BEFORE].[TESTCOUNTER]<>[_THISROW_AFTER].[TESTCOUNTER], [_THISROW_AFTER].[TESTCOUNTER]>1)

[TESTCOUNTER] is ChangeCounter type column here.

The Y branches to Update record Email and N branches to Add Record email

[JpChapron]

I was editing my post to add something like that haha ! nice addition, it is a solution I am using !

[Aurelien]

I love this idea ! I didn't have the idea of using another column.

My very deep idea was to find a way to manipulate data change events from within the branches in the process. This way, I had in mind to build a whole logical path from one single process: new, updated, updated for this column and status, deleted, and so on.

Your idea makes sense !!!!!

[Suvrutt_Gurjar]

Hi @Aurelien ,
Typically we use IN([Key], TableName[Key]) to know if a row is new or being updated. But in case of automation , the bot seems to kick in only after row is added. 

So I believe another detection of new or current row through additional ChangeCounter column is required.

[kvngo94]

Hi Suvrutt, 

can you advise another branch for delete change type ? I want to make 1 bot instead of 3 for 3 kinds of change type : add / delete / update. Thanks.

[Aurelien]

---

@JpChapron wrote:

AND(ISBLANK([_THISROW_BEFORE]),ISNOTBLANK([_THISROW_AFTER]))

---

This one intrigues me as there is no fundamental difference with my first try?

I have to leave the office for now, I'll give it a try by tomorrow. Thank you for your suggestion!

[JpChapron]

I honestly havent tried it, it just came as a thought, I think the second one is a better solution since, I am actually using it

[Suvrutt_Gurjar]

---

@JpChapron wrote:

I was editing my post to add something like that haha ! nice addition, it is a solution I am using !

---

Oh okay @JpChapron . Nice to know our thoughts are on similar lines.

Yes, I also tested the concept in a test app before responding, tested around 10 times by adding , editing records and then started responding in the post.

[dbaum]

FWIW, here's an expression for this purpose that I have saved in my notes. For row adds, it seems equivalent to the expression in @Aurelien's OP, but for now I can't confirm whether it's working for me--I don't remember where I used it nor whether I encountered problems.

IFS(ISBLANK([_THISROW_BEFORE].[Required Column]), "Add", ISBLANK([_THISROW_AFTER].[Required Column]), "Delete", true, "Update")

[Suvrutt_Gurjar · Accepted solution]

If I may add further, my testing leads me to believe that there is no concept of [_THISROW_BEFORE} or [_THISROW_AFTER] before a row is created or added the first time. This is logical because the row itself is non-existent before adding.

The help article's  and its title also confirms that the said values are for updated record.

Access column values before and after an update - AppSheet Help

Even the expression that worked in the test app, essentially  checks if the row is updated  ( A combination of [_THISROW_BEFORE]  , [_THISROW_AFTER]and  ChangeCounter column being >1)  and if the results is FALSE, then it considers  as the row is added and it works. The point to note is detection of add condition  ( A FALSE result of the expression) is indirect. If it is not updated, consider it added.

Hi @Aurelien ,

On second thought, I think even the following simpler expression will work in the branch condition. I have not tested it though.

[ChangeCounter] =1   

TRUE means record is added ( [ChangeCounter] updates to 1 on record add) and FALSE means ( It is greater than 1) and record is updated.

[Aurelien]

Really appreciate your help, everyone !

Thank you all @Suvrutt_Gurjar @dbaum @JpChapron 

[Steve]

Is this a new row?/Does this row already exist?

[SkrOYC]

Btw, if you have a [Created at] and [Modified at] you could do the following.

The obvious scenario for many would be to use a [Created at] with initial value of NOW() and a [Modified at] with an AppFormula of NOW(). My solution is a little bit different.

[Created at] with an initial of NOW() evaluates when the user opens the form or creates the record (if done via actions/automation). There is a ChangeTimestamp column type that evaluates when the user saves the record. So my setup is pointing the [Created at] initial value to the current row's [Modified at] (configured as ChangeTimestamp) and that makes them have the same value when the row was created and not edited in any way and having different values when edited, of course.

Hope it helps

[Fabian_Weller]

---

@Suvrutt_Gurjar wrote:

there is no concept of [_THISROW_BEFORE} or [_THISROW_AFTER] before a row is created or added the first time.

---

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

[_THISROW_BEFORE].[Column] is also working for an add. It seems to take the initial value of the column.