Bot writing and clearing data

[1minManager]

I have a bot that calls an API to fill in 4 seperate columns on saving or updating a row.  But its behavour is mind boggling.  Bascailly it runs, updates all the 4 columns, then clears any of the columns that were blank before.  There is nothing in the bot or anywhere else in the app telling it to do this.

So this started off as a project with @MultiTech to put some AI into a new App were developing for ourselves.  Every time you create and save a row it would use an Appsheet Bot and the AI API to populate those 4 columns. 

Then I noticed that when users just updated a row it would sometimes not write things into the AI columns.  So I created a second scheduled bot to find rows with just the first of the four columns blank.  This sort of worked except no matter how many rows there where it would only ever update the last blank row.  

Presuming this was some issue with the way the bot was grouping the changes.  I changed it so that the bot trigged off of an action.  The action would simply write "update" into a column then clear it.  To call that action I created a slice and a table view of all affected columns.  You could then tick each column and run the action and therefore the bot seperately on each.  But this made things stranger still.  Heres my best desription of what happens:

Before bot runs, trigger column its blank and some of the 4 AI columns have data in.  All have at least one column with no AI data

______ A _ C _
______ _ _ C _
______ A _ _ _

 Bot is triggered

Update A _ C _
______ _ _ C _
______ A _ _ _

Row 1 updated

Update A B C D
______ _ _ C _
______ A _ _ _

Row 2 updated

Update A B C D
Update A B C D
______ A _ _ _

Row 3 updated

Update A B C D
Update A B C D
Update A B C D

Update column is cleared on all rows at the same time.  All updated columns are reset to what they where before.

______ A _ C _
______ _ _ C _
______ A _ _ _

 For the sake of clarity, this bot is not affected by:

• Other bots - all other bots on this table are currently disabled
• Reset on edit - the app doesn't use any
• Action on Save - the app doesn't use any

Workng on a hunch I actually go it to not clear the AI data, though why this works also makes no sense.  As I said earlier the trigger bot sets a column to "Update" then clears it.  If I don't make it clear that column, and just leave it at "Update" then it seems to work ok.

Does anyone have any clue or idea why its working this way?

[Steve]

What's your data source? AppSheet Database? Assume that's your problem.

Gonna need to see the bot, action, and slice configs. Any table Are updates allowed? expressions? Need to see those, too. Also security filters.

[1minManager]

No its just Google Sheets

Are updates allowed? 

Action

Slice

Bot

Each of the API calls has 2 processes.  One to call the API and the following one to write the output into a column.  All the writing part seems to work fine, so its not API or AI at fault.  Its why appsheet is clearing all the data.

[Steve]

I don't have experience with webhooks. Are they synchronous: guaranteed to complete before the next step is started?

Are all the steps using the same slice? Does the slice include the affected columns?

[1minManager]

Its calling a script so you can't choose it to be synchronous

[MultiTech · Accepted solution]

So you're saying that by the time the whole thing runs, the ai responses are cleared out?

Just a hunch:

• Try changing how you're clearing the automation trigger
• Instead of using the action to clear the value, instead clear it in automation
• Make the first step of the automation chain to clear the trigger

Maybe there is some complicated interplay between the 2-data edits, the fact that the 1st data edit calls the scripts (which change the values)... and maybe the 2nd edit (which comes from the perspective of BEFORE the script run) is overwriting with it's original row contents.

Could be wrong, I wouldn't think this level of nuance would get in the way; but it might? ¯\_(ツ)_/¯

[1minManager]

Added the [Report]="" as an action at the end of Bot worked fine.  So I think your right - the second part of the action doesn'r 'see' the data from the API

[MultiTech]

Make sure that you're clearing of the automation trigger is the first step in the automation, otherwise you're going to end up with the automation triggering multiple times because that trigger is still there. 

This is essential when you're working with manual triggers like this. It really stems from the nuance of how automation makes data changes to records; when an automation makes changes to a record it does it column by column one by one (not in a singular edit), which means if you've got any steps for an automation that change data... these changes will kick off the automation each time it makes a change during that chain run. All because that trigger is still there. 

Glad you got it worked out! I can't believe that that was the reason behind this. Kind of crazy.

It's a nuance thing that you'll only really encounter once you get into the weeds, hopefully I can prevent you from falling into that pothole.

[Steve]

Mindbogglingly stupid, that is.

[MultiTech]

🤣🤣🤣🤣