Re: Automation error: row was not found

Bethel_Vasto · 09-01-2022 03:28 PM

Some background: some time ago I posted a question about a bot returning errors of 3 different types. I resorted to a workaround but never really fixed error no.2 (read here https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Bot-returning-errors-of-3-different-types/m-p/4... )
Now, in that post, I was talking about an automation that was supposed to change the value of an existing row. So the error made sense (row was not found).
Now I am stuck with the same error even though the automation is supposed to perform an "add a new row" type of action. Why would the error be "row not found"?
Here's what I am getting:
Error encountered in step with name [Wait until [Data] is yesterday-Update_Input]: Could not update row with key:09/01/2022 23:12:30 from table:Registro presenze_Schema, row was not found.

Automation is built as following:
-event is of type data change, adds only, to a table named "Registro Presenze".
-Process is wait for a period of time (the period a duration calculated between timestamp and midnight - btw, thanks @dbaum )

- check if a certain condition exist, if it does, add a new row to the table "Registro Presenze"

now here's what I am not sure about: I thought I needed an action of type "add a new row to this table" but instead found "add a new row to another table using values from this row".
I used that one because it's the closest to what I need, and it still lets me choose which table to add to, so I chose "Registro Presenze".
Now the error I'm getting, as mentioned above, doesn't really make sense because it looks like the automation is trying to update an existing row instead of adding a new one.
Thanks for taking the time to read this, any help would be greatly appreciated.

dbaum

Confirm that the column designated as the key for the Registro Presenze table either includes an Initial value expression or is included in your data change action to add a new row.

For other guidance, I suggest post screenshots of your automation's components.

Bethel_Vasto

Thanks for taking the time to read and reply!
Yes to both: the key column has an initial value expression and it's also included in the action.

Here are my screenshots:

Event

And here's the detailed action as shown in the behavior section

dbaum

@Bethel_Vasto wrote:
Error encountered in step with name [Wait until [Data] is yesterday-Update_Input]: Could not update row with key:09/01/2022 23:12:30 from table:Registro presenze_Schema, row was not found.

The error doesn't make sense to me since it references a problem editing data during the Wait step, where no data is even being edited. I especially don't know how to interpret "-Update_Input". Maybe try deleting the step and recreating it from scratch?

I had never tried using the Add a row... action to add a row to the same table where the action exists, but I just tried that and it seems to work fine.

Sorry I don't have much guidance to offer. I hope more expert troubleshooters can chime in and help you.

Suvrutt_Gurjar

@Bethel_Vasto wrote:
Automation is built as following:
-event is of type data change, adds only, to a table named "Registro Presenze".
-Process is wait for a period of time (the period a duration calculated between timestamp and midnight - btw, thanks @dbaum )
- check if a certain condition exist, if it does, add a new row to the table "Registro Presenze"

I think you may want to elaborate the above description. Since the event is triggered on "Adds only", it means the automation event will be triggered only when a new row is added to the table.

Then again the last step is to add a new row to the table using values from the add row action.

So there seem to be essentially "two add row steps" in the automation.

In general, it may be premature to mention but the the last step possibly could be "set the values of some columns in this row" if the row is added in the first step itself that triggers the event.

Bethel_Vasto

Thanks for replying!

When a user adds a row, I need the automation to wait for midnight and, if a certain condition is true, add a new row. I don't want the automation to edit the row that the user added. The user adding a row is just the trigger for the new row to be added at midnight.

Suvrutt_Gurjar

Thank you. Could you mention

1> If the rows are being added to the same table.

2. Is there any dependency between the first row added and the next row being added at the midnight. Is the "certain" condition dependent on the first row being added?

Bethel_Vasto

1. Yes

2.The only "dependency" is that (I know it's not actually a dependency), having security filters in place and having turned off the "bypass security filters" option for this automation, the first row and the second row will have the same USEREMAIL() in one of the columns (initial value).

The condition does not have anything to do with any of the values of any of the columns in the first row.

This is the formula for the condition:

OR
(
AND(WEEKDAY(TODAY())=2,
ISBLANK([_THISROW].[Tirocinante].[Lunedì])
),
AND(WEEKDAY(TODAY())=3,
ISBLANK([_THISROW].[Tirocinante].[Martedì])
),
AND(WEEKDAY(TODAY())=4,
ISBLANK([_THISROW].[Tirocinante].[Mercoledì])
),
AND(WEEKDAY(TODAY())=5,
ISBLANK([_THISROW].[Tirocinante].[Giovedì])
),
AND(WEEKDAY(TODAY())=6,
ISBLANK([_THISROW].[Tirocinante].[Venerdì])
),
AND(WEEKDAY(TODAY())=7,
ISBLANK([_THISROW].[Tirocinante].[Sabato])
),
AND(WEEKDAY(TODAY())=1,
ISBLANK([_THISROW].[Tirocinante].[Domenica])
)
)

dbaum

You're in expert troubleshooting hands with @Suvrutt_Gurjar.

FYI regarding your condition expression: SWITCH() might be more straightforward.

Bethel_Vasto

I had a feeling there would be a more efficient way to write that formula, thanks, I need to learn how to use SWITCH().

Bethel_Vasto

@Suvrutt_Gurjar I was looking at the columns for the table in question and realized that the [Tirocinante] column has its USEREMAIL() formula as an App formula (instead of Initial value formula). Could that be an issue?

Suvrutt_Gurjar

Sorry for a bit late revert as I wanted to test on a similar setup before reverting.

@Bethel_Vasto for the second row add action, instead of using the action of type "Add a new row to a table using values from this table", you may want to try with a custom action that you can define in the automation pane itself. The action continues to be shown as a "Custom action" of type "add new rows" as shown in the screenshot below. This custom action shows only in the automation pane and does not reflect in the Behavior --> Actions pane.

I could add second row to the same table with the setup as shown in the screenshot below. It is similar to your automation setup except a condition step. The wait step is of 5 minutes. "First Add Trigger" event is triggered by the "Adds only" operation that adds the first row to the table.

You may also want to recheck your wait period expression that it returns the desired value as I felt it may need a relook.

Edit: Even though you have mentioned the condition is not dependent on the first row being added, you have [_THISROW]..... part in the condition. May we know why you have included [_THISROW] in that case in the condition expression? of course, having the [_THISROW] based expression should not be a problem but you may note that it will be based on the values in the first row being added.

Hope this helps.

Hi @dbaum : Please do not say you are in expert hands. Since this post was an interesting issue, just thought of participating. Please continue to participate in this thread, if you have suggestions. Your responses are also of the highest caliber. I follow all, especially your tips around use of GAS.

Bethel_Vasto

Thanks! I followed all the steps and I'll wait for midnight to see if this works! (I won't waste your time mentioning why I can't just test right now).
As for the [_THISROW], I added it because it needs to check a value for that specific weekday and that specific user in another table and, even though I have security filters in place, I was tinkering with everything so turning off the "bypass security filters " option might have checked the wrong values from the other table and therefore given me the wrong values for the condition. So I understand [_THISROW] might not be necessary but here's the whole story behind it:
1.a user adds a row.
2. at midnight, the automation checks to see if that user has a day off for that specific weekday (the days off are in a different table called "Anagrafica".
3. if the user has a day off, the automation adds a specific "day off record" for that day so the user doesn't have to.

I understand there is a "scheduled" type of event in the deployed version of the app that can do that everyday without me having to trigger the whole thing but I am very far behind in the developing process and I need my users to test this feature now.

I checked the formula for the wait step and it seems to be returning the right value, which is the time left between the [Timestamp] and midnight (also the monitoring shows that the errors are returned right after midnight so I am assuming that part is working just fine).

Can't begin to tell you how grateful I am for your help!

Suvrutt_Gurjar

Thank you for the update. Hope the automation bot runs as expected. Please update us on the result if it ran as expected.

You could possibly test it beforehand by introducing a small wait step of 5 minutes.

Bethel_Vasto

I am aware of the possibility of changing the wait step to five minutes but that means not having the condition met. The condition to be met is that the user has a day off for the day starting at midnight. If the user has added a row, it means it's not their day off. And the user adding a row is the trigger. And I have valid_ifs formulas to prevent users from adding two records on the same day.

Changing all of this is too much of a hassle 🙂 I'd rather wait

dbaum

While it may not be worth it, keep in mind that it's always possible to copy your app and data and then experiment using the copy.

Bethel_Vasto

Unfortunately, I got this error:
Error encountered in step with name [Wait until [Data] is yesterday-Update_Input]: Could not update row with key:09/05/2022 11:58:29 from table:Registro presenze_Schema, row was not found.
😞

dbaum

I still don't understand why get a "Could not update row" error for a step that just waits and doesn't update any row.

FWIW, my next suggestion is try troubleshooting based on @SkrOYC's good point that timestamp keys can be fragile. Maybe try modifying the expression that populates your key column to UNIQUEID()--even just temporarily for one cycle in order to see whether that resolves the issue.

SkrOYC

@Bethel_Vasto wrote:
realized that the [Tirocinante] column has its USEREMAIL() formula as an App formula (instead of Initial value formula). Could that be an issue?

If you need to use the user email for any meaningful way, sure it is.

Define App formulas and Initial values - AppSheet Help

@Bethel_Vasto wrote:
I understand there is a "scheduled" type of event in the deployed version of the app

Yes, and this is the one I'd use. Much easier to do IMHO

@Bethel_Vasto wrote:
key:09/01/2022 23:12:30

It's a bad idea to use this kind of keys, use a text field with UNIQUEID() instead

UNIQUEID() - AppSheet Help
What is a key? - AppSheet Help

@Bethel_Vasto wrote:
the key column has an initial value expression and it's also included in the action

I suggest to ignore any column that has an Initial Value expression configured unless you need to override that with other info, so your key column is an example of a column that you probably won't configure under this kind of actions

Bethel_Vasto

Thanks! Can you clarify why using timestamps columns as keys is a very bad idea?

SkrOYC

One of them is that two people can add a record at the same second.
Also keys are just unique identifiers, so a well made ID or UUID is the best tool for the job

Automation error: row was not found

Define App formulas and Initial values - AppSheet Help

UNIQUEID() - AppSheet HelpWhat is a key? - AppSheet Help

UNIQUEID() - AppSheet Help
What is a key? - AppSheet Help