BOT Error: failed with exception Index was outside the bounds of the array.

[junyutan]

I am trying to delete rows in Table A, when a row in Table B is deleted, with a Bot Trigger.
I'm pretty sure I've been able to do this with no issues before.

Bot Trigger:
When a row in Table B is deleted,
delete rows in Table A

Reference List -
I've tried all 3 options
LIST([Table_B_ID])
Select(Table A[ID], [ID]=[_THISROW].[Table_B_ID])
FILTER(Table A,[ID]=[_THISROW].[Table_B_ID])

Delete Rows

Is there something I'm missing?

Thanks.

[WillowMobileSys]

You cannot delete rows that are part of the Referenced Rows list.  It interferes with the row list processing.

You have a couple other options:

1)  Add a [Delete?] column and use this current Bot to update that column.  Set it to trigger other Bots.  Then create a second Bot for that row type, triggered when [Delete?] = TRUE that simply deletes that single row.

2)  In many scenarios, App Creators are deleting related child rows due to a Parent being deleted.  There is something I refer to as "tightly coupled" Parent/Child relationship - which is simply turning on the "Is part of" flag in the Child table under the Parent column.  Doing this creates a relationship such that when ANY Parent row is deleted, all its child rows are also automatically deleted.  is this your use case?

I hope this helps!

[junyutan]

Hi I'm not sure I understand what you mean by
"You cannot delete rows that are part of the Referenced Rows list."

Also, if I create an action in Table B,
that does the same thing (without deleting the Table B row), it works.
The same action works without the Bot and without the Table B row being deleted.

1) I could try this solution, but I would like to understand 'not being able to delete 'delete rows that are part of the Referenced Rows list'

2) The database relations are a bit complicated, so I wouldn't say Table A is a child table of Table B. 

When a new Row in Table B is created, I push a new row to Table A. So when a Row in Table B is deleted, I want to delete that row in Table A.

Table A is referenced in Table B, but Table B is not referenced in Table A

[Steve]

You MUST delete the children BEFORE the parent.

[junyutan]

I think I get what you mean, thanks

[junyutan · Accepted solution]

I guess I've actually done this on a different app before...

My Solution without any additional Columns.
- Create a 'Fake' Delete Button that is a group action.

Sub actions inside group action:
- Delete Related Rows (Action on a set of rows)

- Delete This Row (Delete Action)

Hide the system 'Delete' button. Show the fake delete button.

This works well enough for my use case.

[Steve]

That's a good approach.

[WillowMobileSys]

Hold on!  The "fake" button approach is no different than what you created in the Bot.  When you use the button, the rows ARE deleted without error? 

This used to NOT work years ago with the same error you were seeing in the Bot - though maybe it was the Bot implementation I was creating. It's a bit fuzzy and I have never tried it again.  So...maybe they adjusted the action implementation but they didn't carry it over to the Bots "custom action" implementation?

I'm willing to bet if you adjusted the Bot to use the action Group you created, it would then work.

[junyutan]

The problem with the bot is the row in Table B is deleted BEFORE the
referenced rows in Table A, because the bot trigger is 'on Table B row
deleted'.

With the fake delete button, it deleted the rows in Table A FIRST and then
deletes itself (row in Table B).

So, this action can't be used in the bot, because the bot will only trigger
after row in Table B is deleted.

[MultiTech]

---

@junyutan wrote:

Create a 'Fake' Delete Button that is a group action.

Sub actions inside group action:
- Delete Related Rows (Action on a set of rows)

- Delete This Row (Delete Action)

Hide the system 'Delete' button. Show the fake delete button.

---

Just keep in mind that all these deletes are then done client-side - on the device - which means the user has to wait for each edit to sync before they can move on; and if you've got a lot of child records to delete, this can become burdensome as the user now has to wait forever for things to upload to the cloud.

The benefit of the automation route, is that it's one edit - deleting the parent - that the user has to wait for.  Technically this will take a little longer than a typical edit or add, because there's an automation running, but it's MUCH faster than client-side edits.

[junyutan]

That's a good point.

For this case there will always only be one child record, so the delay is not too bad.

I will keep in mind the method of setting a delete column and triggering two bots for the child record delete.

[Steve]

Using Is a part of?, the deletes are instantaneous.