A single Bot to trigger on creation OR change

[1minManager]

I often find that I want a bot to trigger on both newly created records and/pr when a specific column changes.  The former can be done by choosing "Adds" or "Adds and Updates".  The later by using [_THISROW_BEFORE].[ColumnName] <> [_THISROW_AFTER].[ColumnName].

The issue I'm wondering about is can I combine both of these into one bot?  Since I'm sure in the past I've used "Adds and Updates" with [_THISROW_BEFORE].[ColumnName] <> [_THISROW_AFTER].[ColumnName] but it didnt trigger on Adds because as far as the bot is concerned [ColumnName] was created but it didn't change.

[Suvrutt_Gurjar · Accepted solution]

I would try below,

Enable "Adds and Updates"

In the bot condition , I would try

IF ( IN ([Key Column], Table_Name[Key Column]),

[_THISROW_BEFORE].[ColumnName] <> [_THISROW_AFTER].[ColumnName],

TRUE)

This will mean if the row exists in the table , the expression will go through the 

[_THISROW_BEFORE].[ColumnName] <> [_THISROW_AFTER].[ColumnName]

part.

If the row is being newly added ,the expression will simply evaluate to TRUE , effectively ignoring [_THISROW_BEFORE]..... [_THISROW_AFTER] part.

[1minManager]

Awesome @Suvrutt_Gurjar I forgot that you can use Select() type functions to read what is already in the table and not just this row

[Suvrutt_Gurjar]

You are welcome Simon. We all learn from each other.

Your responses in the community are insightful. I regularly follow them and also responses of many other colleagues. 

This community is wonderful.

Best wishes.

[WillowMobileSys]

@Suvrutt_Gurjar suggested expression is the only way I know to do this.  I have never understood why AppSheet doesn't just surface the row edit state.  They clearly know if a row is NEW or an UPDATE in both Forms and Bots.  I highly doubt they re-query to check row edit state - I suspect there is a variable there used by the system...just surface it to us App Creators.  

Anyway, in absence of such a variable, I would recommend to keep the Bots separate for efficiency unless you have some compelling reason to combine them.  

If the Bots are separate, the ADD Bot can quickly dismiss the UPDATES and the UPDATE Bot can quickly dismiss the ADDs.  In other words, in 3 operations the system knows if it needs to execute the ADD Bot, execute the UPDATE Bot or neither.

With a combined Bot, it will need to potentially inspect each row of the Table to determine if the Bot should run or not - on EVERY row edit.  As the Table grows, so does the length of time to determine if the Bot should run.  

[Suvrutt_Gurjar]

Hi @WillowMobileSys ,

Thank you for your valuable inputs. Those are thoughful and detailed as you always do.

Your point on each row evaluation is indeed worth considering. I agree that a row level evaluation can be expensive and should be avoided.

In this regard, if I may mention one point that I have a feeling that  all inclusive lists such as Table_Name[Column Name] or Slice_Name[Column Name] seem to execute much faster as compared to list functions that take arguments such as SELECT() or FILTER() . 

Even expression assistant does not flag an all inclusive list with a standard warning that " this expression could impact the performance."  For any SELECT() or similar functions, the assistant gives that warning. I think this absence of warning for all inclusive lists would be intentional.

At this moment, I do not have any calculation base to support my statement. Today entire my day and tomorrow,  I am away from my work desk and responding from mobile while in transit in travel. But when I am back at my work desk,  I will carry some tests and revert with my readings. I will of course carry the tests on VCs on large tables.

[WillowMobileSys]

---

@Suvrutt_Gurjar wrote:

Table_Name[Column Name] or Slice_Name[Column Name] seem to execute much faster as compared to list functions that take arguments such as SELECT() or FILTER() .

---

I believe what you are referring to is Indexing.  The question is if AppSheet employs Indexing on the app side of things.  More importantly, does AppSheet take advantage of Indexing in the IN() function.  I don't know the answer.

[Suvrutt_Gurjar]

Thank you. I had list compilation time in mind - the list used in IN() function as that is a multirow expression. . Maybe I misunderstood you. I am now trying to understand your below referred guidance.

---

@WillowMobileSys wrote:

With a combined Bot, it will need to potentially inspect each row of the Table to determine if the Bot should run or not - on EVERY row edit.  As the Table grows, so does the length of time to determine if the Bot should run.  

---

Could you guide which part of the suggested expression will start consuming more time with increased table size?

The IN() function , or the next [_THISROW_BEFORE] ...[_THISROW_AFTER] based comparison or both?  

[AleksiAlkio]

If the ColumnName is a mandatory field, then [_THISROW_BEFORE].[ColumnName] <> [_THISROW_AFTER].[ColumnName]  is enough for both cases.

[Suvrutt_Gurjar]

That is a a much simpler and elegant solution @AleksiAlkio 

[WillowMobileSys]

---

@AleksiAlkio wrote:

If the ColumnName is a mandatory field, then [_THISROW_BEFORE].[ColumnName] <> [_THISROW_AFTER].[ColumnName]  is enough for both cases.

---

Very good point!  And if it's not...what is your recommendation then?  Just curious on your thoughts as THE ultimate AppSheet guru!!  🙂 

[AleksiAlkio]

I believe Suvrutt's formula is then the simplest one. Or something like..

OR(
NOT(IN([Key Column], Table_Name[Key Column])),
[_THISROW_BEFORE].[ColumnName] <> [_THISROW_AFTER].[ColumnName]
)

[AleksiAlkio]

Though then the option "Blank value comparison mode" needs to have "Consistent"